View Full Version : Finding data range reference for a series in chart using vba
LucasLondon
03-13-2010, 06:13 AM
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
03-13-2010, 07:14 AM
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
03-13-2010, 07:43 PM
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 (http://www.j-walk.com/ss/excel/tips/tip83.htm) page for a link to it.
mdmackillop
03-14-2010, 03:43 AM
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
03-14-2010, 03:32 PM
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
03-14-2010, 03:54 PM
To use the other code you need to match data types so MyChart must be dimmed as Chart
Sub test()
Dim mychart As Chart
Set mychart = ActiveSheet.ChartObjects(1).Chart
Set DataRange = GetChartRange(mychart, 1, "values")
MsgBox DataRange.Address
End Sub
yuriy
09-15-2015, 03:47 PM
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
09-16-2015, 07:09 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.