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