Get the Source Data of the ActiveChart

[JohnEOasis]

Does anyone know how to get the range of the Source Data of a chart in Excel using VBA?

 

[SkipVought]

Hi,

According to John Walkenback in "Excel Charts," there are MANY different SERIES formula formats. There can be missing arguments arrays, non-contiguous ranges and, for instance, the Bubble Chart has an additional argument not found in other chart types. John, "worked on this problem for several years." You can buy the book to see how he solved the problem.

I do lots of stuff with VBA and charts and have never found the need to "discover" the source data on the fly.

WHAT is the issue you are trying to solve, rather than framing the issue with HOW to accomplish the task?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[JohnEOasis]

I created a dynamic chart and need to know the range of that chart when a button is clicked so I can reset the chart to that same range later on.

 

[SkipVought]

Why not use a Named Range. I use this technique extensively. Quite simple to point the series to a different range or source.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[JohnEOasis]

The Chart can change either by a single row or many rows and columns at once. It depends on how the user moves around the chart.

 

[SkipVought]

So? That can all be accomplished with native Excel functionality and possibly some VBA for hiding columns & rows.

Please explain how you want your chart to function, as "the user moves around the chart."

Do you want stuff to happen when the user CLICKS on portions of the chart? If so, check out the GetChartElement Method.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[JohnEOasis]

Thank you for your replies,
The user used buttons/sliders/combo boxes to select the data for the chart. That is how they move around the chart.

I have found a solution. I set a universal range variable to a default then update it when something changes that default.