Chart source data

[TMHill]

Hi

I am a pretty proficient user of VB/VBA, but am struggling on what
should be a fairly straightforward issue (to my mind, anyway).

Is it possible to retrieve the data range (as a range or as a string, I
can parse it quite happily myself) from a chart?

What I want is a function which would be something like:

sourceRange$ = GetSourceRange(myChart)


I feel sure its in there somewhere, but I hardly ever work with charts
and I can't find it!

Any advice would be great - thanks very much

Tom

 

[John Green]

Tom,

The only way do get the ranges referred to in a chart, AFAIK, is to parse them out of the SERIES function.

Sub GetRangesFromChart()
Dim Ser As Series
Dim stSeriesFunction As String
Dim iFirstComma As Integer, iSecondComma As Integer, iThirdComma As Integer
Dim stValueRange As String, stXValueRange As String
Dim rgValueRange As Range, rgXValueRange As Range

On Error GoTo Oops

'Get the SERIES function from the first series in the chart
Set Ser = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
stSeriesFunction = Ser.Formula
'Locate the commas
iFirstComma = InStr(1, stSeriesFunction, ",")
iSecondComma = InStr(iFirstComma + 1, stSeriesFunction, ",")
iThirdComma = InStr(iSecondComma + 1, stSeriesFunction, ",")
'Extract the range references as strings
stXValueRange = Mid(stSeriesFunction, iFirstComma + 1, iSecondComma - iFirstComma - 1)
stValueRange = Mid(stSeriesFunction, iSecondComma + 1, iThirdComma - iSecondComma - 1)
'Convert the strings to range objects
Set rgXValueRange = Range(stXValueRange)
Set rgValueRange = Range(stValueRange)
'Colour the ranges
rgXValueRange.Interior.ColorIndex = 3
rgValueRange.Interior.ColorIndex = 4
Exit Sub
Oops:
MsgBox "Sorry, an error has ocurred" & vbCr & _
"This chart might not contain range references"
End Sub