Manipulating Chart Source Data with VBA

[ossuary]

Hello,

I have a chart that I am trying to set up with dynamic data. I have the chart created, but the problem is that I will have a variable number of categories based on the data that is loaded, so I need to use VBA to change the range for the source data and labels on the fly (otherwise, I will end up with 20-50 categories that are all zeroes, putting a bunch of gobbledegook at the top of the chart).

What commands to I need to enter into VBA to get this to work?

I have put a formula in 2 cells to determine based on the current data what the source data values range should be, and what the category labels range should be.

The cell that holds the value range is Graphics!L6, and the cell that holds the labels range is Graphics!L7.

The current values that these cells hold are:

L6:

=Graphics!$J6:$J9

L7:

=Graphics!$K6:$K9

Oh, and the name of the chart is "Chart 1"

What do I need to put into VBA to get the chart to change the data source values and category labels to the ranges I have listed in those 2 cells? (and for that matter, have I listed the ranges correctly in those 2 cells so that they are usable?)

If possible, I would prefer to do this without having to use code to select the chart, because I want the updates to the chart to be invisible to the user.

Any help you can give me would be greatly appreciated.

 

[Jon Peltier]

You probably don't need VBA. Try using some dynamic names:

http://peltiertech.com/Excel/Charts/Dynamics.html

 

[ossuary]

I've tried using some of the stuff discussed on that page, but it doesn't really solve the problem I'm having.

Based on the data that is loaded into this report, some (or a lot) of the potential source range actually ends up being blank, so my legend key is huge (around 30 blank lines in some cases), and I have a whole stack of data labels all piled on each other at the top of the pie chart, all reading 0%, and it's very ugly.

I need to be able to use VBA to change the data source and label ranges so that every time a new set of data is loaded into the file, I can re-adjust how large my data source range is.

Any ideas on the correct syntax / process to do this in VBA?

 

[Jon Peltier]

Off the top of my head I don't have this ready, but you could use formulas in a couple adjacent columns to extract the values, in decreasing order, and the corresponding labels. If the value is zero, the label column is kept clean by returning "" from the formula. Use a number format of 0%;;; for the data labels and the values column, so labels with zero are not displayed (they are still in the chart, but they show only "").

Hmm, not to disappoint, but no VBA required.

 

[ossuary]

I already have the columns showing up blank, but since the data source is manually entered, it is still using those blank columns / rows.

I couldn't get the offset formulas on your page to work - I think because I am actually using a pie chart and all of your examples are for charts that use two adjacent rows of data.

Can you tell me what the offset formula would be to name the range if the range is all in a single column instead of spanning two columns? If I can get that to work, I can probably make due without the VBA for now (though I would still like to know the VBA commands to do this so I can use it for additional purposes).

 

[Jon Peltier]

The pie chart uses two ranges, doesn't it? One for labels and one for values. But what you need to do is filter the data to get all the zeros at the end. John Mansfield has posted a couple ways to do this on his blog:

http://www.cellmatrix.net/index.php/pie_chart_exclude_zero_values/
http://www.cellmatrix.net/index.php/bar_chart_exclude_zero_values/

Just because one says pie and the other says bar, both ways work for any chart as they show different ways to manipulate the source data.

 

[ossuary]

Jon,

I appreciate you trying to help, but the suggestions you are giving really just don't work with the data I have to work with.

I'm really just looking for the correct VBA syntax to adjust the data source range and the label range of my chart on the fly. The non-coding options just don't seem to work with my data as it stands (and the raw data I have to work with is not mine, so I can't change how it outputs).

 

[Jon Peltier]

I've been trying to tell you that VBA is going to be more involved and more difficult than using worksheet formulas. There is no magic about VBA. VBA can hardly do anything that you can't do without it.

I posted links to those two examples to try to help you see how to work with whatever raw data there is to produce cleaner data ranges. I can't imagine why your data is not amenable to this kind of approach.

If you're going to be typing ranges into a cell, you might as well type the ranges into the Source Data > Series dialog for your chart, or select the chart and drag around the colored highlights around its source data ranges.

If you're using formulas to compute these range addresses, you may as well use names (i.e., named formulas) to define the ranges and use these names as the chart's source data.

 

[ossuary]

Jon,

I did try to use the examples you provided me with, but I can't get them to work with the data I have (and I don't know exactly why they won't work, because I don't really understand the explanations for what the example formulas are doing). I am not entering any manual data, the cells that contain the ranges I want to use for the source data and labels update based on the information that is loaded into the report.

But there are specific reasons that I want to use VBA instead of formulas and named ranges to do the actual changes to the chart:

1) VBA can be on-demand, formulas are "always on." I don't want the chart to change its parameters every single time something from the data changes, I only want the chart to change when I tell it to through a specific macro, based on certain criteria.

2) By learning and understanding how to do this one specific function in VBA, it will help me to understand how to accomplish other, similar tasks using VBA that I am not asking about at this particular moment, but that I also want to know how to do. Using a formula that I vaguely understand will only help me once, in this specific application... but seeing the correct syntax for the code to manipulate the data in this way will help me make more complex and useful manipulations in the future.

Again, I DO appreciate your help, and I have tried your methods, but I cannot get them to work properly (I wish I could figure out why), and formulas will not help me as much as the VBA will, even though the VBA is more complex - more complex also means more diverse, if you can harness it. I'm not trying to be unappreciative, I just want what I want.

 

[Jon Peltier]

Alright. You could still make the solution not VBA-dependent and not "instant" but based on user input, as in

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=246

but if you insist, you could use something like the following air code:

Dim rXVals as Range
Dim rYVals as Range

On Error Resume Next
Set rXVals = Range(ActiveSheet.Range("L7").Value)
Set rYVals = Range(ActiveSheet.Range("L6").Value)

If Err.Number = 0 Then
  With ActiveSheet.ChartObjects("Chart 1").Chart
    .SeriesCollection(1).Values = rYVals
    .SeriesCollection(1).XValues = rXVals
  End With
End If

On Error Goto 0

You should eliminate the = in the cells containing the address, or remove it in the VBA.