Aug 09, 2016

In Excel a chart is normally based on a table of data. Therefore, to chart a mathematical equation all we have to do is produce a table of two columns in which the first column contains the values of the independent variable x and the second column contains the outcome of the equation for each x. This method usually produces a large table whose only use is to be there for the chart. Of course this is not always favourable.

An alternative is to create a blank chart based on a small blank table and then set the Formula property of the chart to the values of the equation. The small table can then be deleted without any effect on the chart. As an example look at the following code.

Charts.Add

ActiveChart.SetSourceData Range(“A1:B1”), PlotBy:=xlColumns</p>

This will create a blank chart based on the range A1:B1. This means that the category is taken from the value in A1 and the series data is taken from B1, both of which may be empty, but nevertheless it now knows that there is one series of data in the chart.

Then, if we write this line, we are now changing the series data;

ActiveChart.SeriesCollection(1).Formula = “=SERIES(””Sales””,
{“”Q1””,””Q2””,””Q3””,””Q4””},{10,20,10,30},1)”

This line is where the magic happens. It sets the Category values to Q1, Q2, Q3 and Q4 and sets the Series data to 10, 20, 10 and 30. Notice that the data are contained inside curly brackets “{“ in an Array format. Moreover Category values are text values whereas series values are numbers. “Sales” is the name of the Series. At this point the chart is NOT based on the initial table anymore and that table can be deleted or ignored.

But of course we want to chart an equation and not the above values. I’m sure you’ve now got the point. All we have to do is produce the data of the equation in the above format, i.e. produce them inside curly brackets, like this;

Of course, you can expand this code to apply to different situations and requirements.

Be aware that you can add another graph to the same chart by adding this for example;

ActiveChart.SeriesCollection(2).Formula = “=SERIES(””Cost””, {“”Q1””,””Q2””,””Q3””,””Q4””},{40,60,10,50},2)”

Notice the number 2 in the SeriesCollection and at the end of the line. This indicates the second series of the chart. But remember if you are going to add a second series you need to first have your initial small table to include a second series by modifying it like;

ActiveChart.SetSourceData Range(“A1:C1”), PlotBy:=xlColumns

Notice A1:C1 rather than A1:B1.

Don’t forget that if you need, you can set the chart to change to a line chart by;

ActiveChart.ChartType = xlLine