Determining the Ranges Used in a Chart

The behavior that is beneficial when converting a chart to use arrays is a problem when you need to pro-grammatically determine the ranges that a chart is based on. If the Values and XValues properties returned the strings or range objects that you used to define them, the task would be easy.

The only property that contains information on the ranges is the Formula property that returns the formula containing the SERIES function as a string. The formula would be like the following:

=SERIES("Mangoes", Sales!$B$3:$D$3, Sales!$B$5:$D$5, 1)

The XValues are defined by the second parameter and the Values by the third parameter. You need to locate the commas and extract the text between them as shown in the following code, designed to work with a chart embedded in the active sheet:

Sub GetRangesFromChart() Dim seSeries As Series Dim sSeriesFunction As String

Dim iFirstComma As Integer, iSecondComma As Integer, iThirdComma As Integer Dim sValueRange As String, sXValueRange As String Dim rngValueRange As Range, rngXValueRange As Range

On Error GoTo Oops

'Get the SERIES function from the first series in the chart

Set seSeries = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)

sSeriesFunction = seSeries.Formula

'Locate the commas iFirstComma = InStr(1, sSeriesFunction, ",") iSecondComma = InStr(iFirstComma + 1, sSeriesFunction, ",") iThirdComma = InStr(iSecondComma + 1, sSeriesFunction, ",")

'Extract the range references as strings sXValueRange = Mid(sSeriesFunction, iFirstComma + 1, _

iSecondComma - iFirstComma - 1) sValueRange = Mid(sSeriesFunction, iSecondComma + 1, _ iThirdComma - iSecondComma - 1)

'Convert the strings to range objects Set rngXValueRange = Range(sXValueRange) Set rngValueRange = Range(sValueRange)

'Color the ranges rngXValueRange.Interior.ColorIndex = 3 rngValueRange.Interior.ColorIndex = 4

Exit Sub

Oops:

MsgBox "Sorry, an error has occurred" & vbCr & _

"This chart might not contain range references"

End Sub sSeriesFunction is assigned the formula of the series, which contains the SERIES function as a string. The positions of the first, second, and third commas are found using the inStr function. The Mid function is used to extract the range references as strings, and they are converted to Range objects using the Range property.

The conversion of the strings to Range objects works even when the range references are not on the same sheet or in the same workbook as the embedded chart, as long as the source data is in an open workbook.

You could then proceed to manipulate the Range objects. You can change cell values in the ranges, for example, or extend or contract the ranges, once you have programmatic control over them. For illustration purposes, the code changes the color of the ranges in the worksheet by changing the Colorlndex property.

0 0

Post a comment