Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart source data
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart source data
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 -- John Green - Excel MVP Sydney Australia "TMHill" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Source Data for the Chart | Excel Discussion (Misc queries) | |||
pie chart & column chart w/same source data | Charts and Charting in Excel | |||
Use detailed data in one worksheet to create summary data as chart source | Charts and Charting in Excel | |||
How do you link chart source data when you copy the chart? | Charts and Charting in Excel | |||
Chart data source | Charts and Charting in Excel |