Finding data range reference for a series in chart using vba

[LucasLondon]

Hello,

Is it possible to use VBA to identify the range reference for a series on a chart?

For example I have a chart object called "Unemployment & Inflation Rate " and it has a SeriesCollection(1) in it. I want to find out what range series collection one is referencing. So if its K4:K100, I want to show this say in a msg box.

So I guess macro would read something like this:

 
sub Test 
ActiveSheet.ChartObjects("Unemployment & Inflation Rate ").select
ActiveChart.SeriesCollection(1).Select
 
'code here to read of the data range the series is referencing 
'e.g reference = xxxxxxxxxxx
 
msgbox reference
End Sub

Could this be done?

Thanks,

Lucas

[mdmackillop]

I notice that clicking on a series shows the range in the Formula Box. Don't see a way yet to get it in a message box
How about

 
MsgBox ActiveChart.SeriesCollection(1).Name

[p45cal]

John Walkenbach solves this missing excel function in 2 ways.
1. He parses the series function. Here's his code:

Function GetChartRange(cht As Chart, series As Integer, _
ValOrX As String) As Range
'cht: A Chart object
'series: Integer representing the Series
'ValOrX: String, either "values" or "xvalues"
Dim Sf As String
Dim CommaCnt As Integer
Dim Commas() As Integer
Dim ListSep As String * 1
Dim Temp As String
Dim i as Integer
Set GetChartRange = Nothing
On Error Resume Next
'Get the SERIES formula
Sf = cht.SeriesCollection(series).Formula
  'Check for noncontiguous ranges by counting commas
'Also, store the character position of the commas
CommaCnt = 0
ListSep = Application.International(xlListSeparator)
For i = 1 To Len(Sf)
If Mid(Sf, i, 1) = ListSep Then
CommaCnt = CommaCnt + 1
ReDim Preserve Commas(CommaCnt)
Commas(CommaCnt) = i
End If
Next i
If CommaCnt > 3 Then Exit Function
  'XValues or Values?
Select Case UCase(ValOrX)
Case "XVALUES"
'Text between 1st and 2nd commas in SERIES Formula
Temp = Mid(Sf, Commas(1) + 1, Commas(2) - _
Commas(1) - 1)
Set GetChartRange = Range(Temp)
Case "VALUES"
  'Text between the 2nd and 3rd commas in SERIES Formula
Temp = Mid(Sf, Commas(2) + 1, Commas(3) - _
Commas(2) - 1)
Set GetChartRange = Range(Temp)
End Select
End Function

and how you might use it:

 Set MyChart = ActiveSheet.ChartObjects(1).Chart
Set DataRange = GetChartRange(MyChart, 1, "values")
MsgBox DataRange.Address

This is copied from his book without permission, so here's hoping that he'll see this as free advertising for his work rather than breaking copyright.

2. He uses a class module:
see the bottom of this page for a link to it.

[mdmackillop]

How did I miss the Formulas property?
If I hadn't, I'd have suggested

 
Sub ListSeries()
Dim txt as string, i as Long
For i = 1 To ActiveChart.SeriesCollection.Count
txt = ActiveChart.SeriesCollection(i).Formula
msg = msg & "Series " & i & " - " & Split(txt, ",")(2) & vbCr
Next
MsgBox msg
End Sub

although I think JW's code will catch some things that mine misses!

[LucasLondon]

Thanks gents for your help.

Mdmackillop - you solution works for me and it's also easy for me to understand and play with!

P45Cal - Just to let you know, I also tested out your solution but got a compile error saying "By Ref Argument Type Mismatch".

Lucas vbmenu_register("postmenu_207821", true);

[mdmackillop]

To use the other code you need to match data types so MyChart must be dimmed as Chart
[VBA]
Sub test()
Dim mychart As Chart
Set mychart = ActiveSheet.ChartObjects(1).Chart
Set DataRange = GetChartRange(mychart, 1, "values")
MsgBox DataRange.Address
End Sub
[/VBA]

[yuriy]

I know this post is old, but just used it with slight modification (John Walkenbach's code mentioned above):
Small addition to loop through several charts. My charts had external links, so print.debugged the full formula to catch the full link, including the external source name.

Sub Chartlist()
Dim wks As Worksheet
Dim lngS As Long, lngC As Long, lngX As Long, chrs As ChartObject

For lngS = 1 To ActiveWorkbook.Sheets.Count
With ActiveWorkbook.Sheets(lngS)
For lngC = 1 To .ChartObjects.Count

lngX = lngX + 1
Set chrs = .ChartObjects(lngC)
chrs.Activate

test (lngC)

Set chrs = Nothing
Next lngC
End With
Next lngS

End Sub

Sub test(nbr As Long)
Dim mychart As Chart
Set mychart = ActiveSheet.ChartObjects(nbr).Chart
Set DataRange = GetChartRange(mychart, 1, "values")

Debug.Print mychart.name & ": " & ActiveSheet.name & "-" & DataRange.Address
End Sub

Function GetChartRange(cht As Chart, series As Integer, _
ValOrX As String) As Range

'cht: A Chart object
'series: Integer representing the Series
'ValOrX: String, either "values" or "xvalues"
Dim Sf As String, CommaCnt As Integer, Commas() As Integer, ListSep As String * 1, Temp As String, i As Integer

Set GetChartRange = Nothing

On Error Resume Next
'Get the SERIES formula
Sf = cht.SeriesCollection(series).Formula
Debug.Print Sf
'Check for noncontiguous ranges by counting commas
'Also, store the character position of the commas
CommaCnt = 0
ListSep = Application.International(xlListSeparator)
For i = 1 To Len(Sf)
If Mid(Sf, i, 1) = ListSep Then
CommaCnt = CommaCnt + 1
ReDim Preserve Commas(CommaCnt)
Commas(CommaCnt) = i
End If
Next i

If CommaCnt > 3 Then Exit Function
'XValues or Values?
Select Case UCase(ValOrX)
Case "XVALUES"
'Text between 1st and 2nd commas in SERIES Formula
Temp = Mid(Sf, Commas(1) + 1, Commas(2) - _
Commas(1) - 1)
Set GetChartRange = Range(Temp)
Case "VALUES"
'Text between the 2nd and 3rd commas in SERIES Formula
Temp = Mid(Sf, Commas(2) + 1, Commas(3) - _
Commas(2) - 1)
Set GetChartRange = Range(Temp)
End Select

End Function

[p45cal]

Why don't you suggest it as an Article for this site? http://www.vbaexpress.com/forum/content.php?124-excel
Or perhaps an entry in the Knowledge base? http://www.vbaexpress.com/kb/submitcode.php