Wednesday, December 17, 2014

Excel VBA: Get source data GetSourceData

After searching a lot for the solution of this problem, I want to introduce it. For charts, MS Excel does not has a native function to get the source range address for series collection, but we can -with a trick-  get some useful information about the source range using string processing.

The following two pictures show the layout of series collection formula. I noticed that sometimes I get the first layout and sometimes I get the other one (I don't know exactly when I get each one). The difference between them is just the quotation marks of sheet name. The series collection formula has four data:

(1) Data series name: represented by address or string 
(2) Independent variable (x-axis) values: represented by address or defined name
(3) Dependent variable values (y-axis) represented by address or defined name
(4) Index of data series (read only value)

The first layout (with quotation marks):
The second layout (without quotation marks):
The following code demonstrates how to get source sheet name for the second layout (Excel 2007)

Sub GetSeriesCollectionData()

' Get the formula of the first data series
SeriesCollectionFormula = Charts("Chart1").SeriesCollection(1).Formula

' Some string processing...
StartPos = InStr(SeriesCollectionFormula, "(")     'Index of first "(" found in the string
EndPos = InStr(SeriesCollectionFormula, "!")    'Index of first "!" found in the string

' Extracting the name of source worksheet using Mid function
SourceWorkSheet = Mid(SeriesCollectionFormula, StartPos + 1, EndPos - StartPos - 1)

'Display the source work sheet in a message
MsgBox ("The name of source work sheet of this chart is : " + SourceWorkSheet)

End Sub