Chart Data from VBA not Cells

Question

I want to know if it is possible to set the data to a chart from VBA code without having to set it to a bunch of cells.

For the purpose, it can plot anything.. even random numbers. But I don't want to use any cells to paste the values and then do the chart get these values.

[My application has a Sheet with a lot of letters "M" (maintenance), "O" (opperating) and "A" (available), for everyday of the year, from a bunch of equipments. I do some vba filtering on the equipments to get some that I want to plot and want to do some calculations based on the "M", "O" and "A" quantities.]

Yup, certainly possible. To get an idea of how, simply plot a random little chart. Turn on the Macro recorder, then right click the chart to Select Data, and manually type in values separated by commas and see the new chart. Then stop the macro recorder. You'll get a template code ready. (Am using an ipad so can't post code in Answers :) hope it's useful) — hnk
– hnk, Commented Jul 2, 2014 at 13:23

Rory · Accepted Answer · 2014-07-02 13:48:09Z

5

Here's a simple example:

Sub AddChart()
    Dim cht     As Chart
    Dim ser     As Series

    Set cht = Charts.Add
    cht.ChartType = xlColumnClustered
    Set ser = cht.SeriesCollection.NewSeries
    ser.XValues = Array(1, 3, 5, 7, 9)
    ser.Values = Array(2.4, 3.2, 5.7, 12.67)
End Sub

You must note that the SERIES formula for a chart in some versions (2003 and prior, I think) is limited to 1024 characters. In 2010, it seems to be limited to 8192 characters. That means that if you use literal values (rather than a range) for the data, each character is included in the formula - so the more decimal places you specify and the more data points, the more likely the code will fail.

edited Jul 2, 2014 at 13:48

answered Jul 2, 2014 at 13:25

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Chart Data from VBA not Cells

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related