Find source WS from Chart

[Guest]

I have a case where I want to branch code when a user clicks on a Toolbar
button when a chart is the active sheet. I want to find out where the source
data from the chart is coming from. My initial thought was to use .XValues,
and branch my code based on the location of the source data.

When I have the Chart Active, and go to the Chart Menu and select Source
Data, I get a dialog where I can clearly see the chart knows exactly where
the data is coming from. If I turn on recording, and manipulate that window,
nothing gets recorded.

However, in VBA, when I grab a series, and look as .Values or .XValues, it
returns a variant array. So far, the only way I have been able to determine
the worksheet the .XValues came from is to get Series.Formula, and actually
parse the name out of the string.

Is there a better way of determining where the source data from a chart (or
series really) is coming from?

Thanks, Bob

 

[Bill Renaud]

<<Is there a better way of determining where the source data from a chart
(or series really) is coming from?>>

In general, I don't think so. Remember that a chart can have data from
multiple worksheets, even if it is not a combination or mixed data chart
with a secondary axis.

 

[Guest]

Bill,

Thanks for the response. I know that in general the data sets can come from
anywhere, but in this particular application I know all the source data is
coming from one worksheet. I was hoping something like
..Series(1).XValues.RefersToRange.Parent or something like that would just
give me the Worksheet object I was looking for.

Bob

 

[Peter T]

Indeed only way is to parse the formula, eg

Sub test()
Dim s As String
s = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Formula
s = Split(s, ",")(2)
s = Left$(s, InStr(2, s, "!") - 1)
s = Replace(s, "'", "")
MsgBox s
'Debug.Print s
End Sub

Regards,
Peter T

 

[Guest]

Peter,

I'm not familiar with the split command, I'll have to look into it.

Bob

P.S. Here is what I did:

Public Sub ToolBarButtonCode()
'This code is being run because of an .OnAction property of a toolbar
button.
Dim wbActive As Workbook, wsTest As Worksheet, chtTest As Chart
Dim strChartFormula As String, lngStart As Long, lngLength As Long

On Error Resume Next
Set wbActive = ActiveWorkbook
Set wsTest = ActiveSheet
Set chtTest = ActiveSheet
On Error GoTo 0

If wbActive Is Nothing Or (wsTest Is Nothing And chtTest Is Nothing) Then
MsgBox "Action not available"
Exit Sub
End If

If Not chtTest Is Nothing Then
strChartFormula = chtTest.SeriesCollection(1).Formula
lngStart = 1 + InStr(1, strChartFormula, "'")
lngLength = InStr(1, strChartFormula, "!") - lngStart - 1

On Error Resume Next
Set wsTest = wbActive.Worksheets(Mid(strChartFormula, lngStart,
lngLength))
On Error GoTo 0

End If

If wsTest Is Nothing Then
MsgBox "Chart data not in active workbook"
Exit Sub
End If

'At this point, wsTest should hold the worksheet object of the source
data.
MsgBox wsTest.Name
End Sub

 

[Bill Renaud]

Locating the worksheet name can be very problematic. In your code:

lngStart = 1 + InStr(1, strChartFormula, "'")
lngLength = InStr(1, strChartFormula, "!") - lngStart - 1

....you obtain the length of the string to extract (lngLength) by starting
at character 1 again. This could cause an error, if the worksheet name does
not have any spaces in it, and therefore, there will be no single quotes in
the string. You might try:

lngStart = 1 + InStr(1, strChartFormula, "(")
lngLength = InStr(lngStart, strChartFormula, "!") - 1

.... to insure that the length of the string to extract at least starts at
the position of lngStart, otherwise lngLength could end up being a negative
number. This assumes that you are using the label argument in the SERIES
formula. If not, then you might have to search for a comma to find the
beginning of the X data. After extracting the worksheet name, if single
quotes surround the extracted string, delete them.

For worksheets that have no spaces in the name, the SERIES formula will
appear as:

=SERIES(SheetName!$B$1,SheetName!$A$2:$A$100,SheetName!$B$2:$B$100,1)

Note that there are no single quotes in the above example. If the following
example, if the series has no label, then the first argument will be
missing:

=SERIES(,SheetName!$A$2:$A$100,SheetName!$B$2:$B$100,1)

This also assumes that you are not using named ranges in the formulas to
create a dynamic chart. If so, then the above series might appear as:

=SERIES(,'WorkbookName.xls'!XNamedRange,'WorkbookName.xls'!YNamedRange,1)

...where the string inside the single quotes would be the WORKBOOK (file)
name, not the worksheet name. You would then have to fetch XNamedRange from
the list of names, then parse it's 'RefersTo' property to get the worksheet
name.

Hope this helps (HTH)!

 

[Jon Peltier]

Rather than spend many hours writing and rewriting code to parse the series
formula, use this class module that John Walkenbach has developed:

http://www.j-walk.com/ss/excel/tips/tip83.htm

- Jon

 

[Bill Renaud]

Great tip Jon! Thanks.