getting the values from the activechart using excel vba

0
votes

I have some data, which I have charted as a scatter plot. And I would like to iterate over the points on the chart, and programmatically using VBA determine their

  1. cluster
  2. budget
  3. actual
  4. varaince values

enter image description here

I know I can get (4) variance using the following code, but I am not sure how to get values 1: cluster, 2: budget, 3: actual

Sub t3()
    Dim chart As chart
    Dim series As series
    Dim values As Long
    Dim p As Double

    Set chart = ActiveChart

    Set series = chart.SeriesCollection(1)

    p = series.Points.Count

    For i = 1 To p
        values = series.values(i)
        Debug.Print " ", i, values,
        Debug.Print ""
    Next i
End Sub

Using this information for 1, 2, 3, 4, I will check the cluster, and color the point accordingly A: blue, B, Red, C Green

Then I need to put a legend on the left of the chart with the 3 cluster values and their colors: A: blue, B, Red, C Green

chart

enter image description here

What I want

I chart, colored according to cluster: A, B, or C, where each of A, B, C only appear once in the legend. However the cluster is appearing for each point instead :(

enter image description here

Getting the other values

  1. I'd like to do calculations on each point on the scatter chart, however I am not sure how to get access to the other values (budget and actual), as I am only showing cluster and variance on my chart now.

  2. I simply offset from the start position and stored the values in variables, but I feel there must be a better way of accessing other data in a worksheet to use in a chart? Instead of removing it from the data (when you Select Data >> for the chart, can you hide it and then use it in calculations?

enter image description here

1
excelvbaloopscharts
Could you show your chart since it's hard to imagine how you setup your chart? If you just used different series for each columns you could just select a different series and iterate trough it: chart.SeriesCollection(2), chart.SeriesCollection(3) and so on... – Mitja Bezenšek
how do you add the missing columns (budget and actual) as series on the chart and then hide them (not show). If I could do this, yes I would agree you could use the one iterator (i) for all the values in the table. But I am not sure how to add the missing columns to the chart and hide any aspect of them from showing anywhere on the chart. – yoshiserry
If they are not in the chart you cannot access them. It is possible to hide the series on a chart by setting the line and fill visibility to msoFalse. If you do that you will probably need to set the axis maximum and minimum values. TBH in your situation accessing the data from the spreadsheet is probably a better way to go. – Mitja Bezenšek
Hi Mitja, thank you. I'm interested in why you think it's a better option to just access from spreadsheet? vs make the series, and make them false? – yoshiserry
Not sure how much points you have but performance would be much better if accessing through the spreadsheet. Also you would have problems with axis since it would stretch for the hidden series (imagine positive budget and actual and negative variance - the chart would still have a lot of space occupied by the positive even though all the displayed data would be negative). You would have to set the axis to fixed which is then not so good if your data is dynamic - you would have to change the fixed axis on every data change. – Mitja Bezenšek

1 Answers

0
votes

To get the chart only to show each cluster ID once, use this approach. I've added three columns to the data range, one headed by each cluster ID. Select E2:G7, enter this formula

=IF(E$1=$A2,$D2,NA())

and press Ctrl+Enter to fill the selected range with the formula.

Select the shaded range (select one region, then hold CTRL while selecting another) and insert a scatter chart.

new data range plus chart

Each cluster has a unique legend entry and a distinct format in the chart.

For your other calculations, use formulas in the worksheet.

I don't know why you're calculating your life with VBA when what you need is built right into Excel.