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;
1 | theTitle = “Velocity” |
2 | theEquation = “3*x^2” |
3 | Xmin = -10 : Xmax = 10 |
4 | Xaxis = "{" : Yaxis = "{" |
5 | For x = Xmin To Xmax |
6 | Xaxis = Xaxis & """" & x & """," |
7 | theEquation = Replace(theEquation, "x", "(" & x & ")") |
8 | Yaxis = Yaxis & Evaluate(theEquation) & "," |
9 | Next x |
10 | Xaxis = Left(Xaxis, Len(Xaxis) - 1) & "}" |
11 | Yaxis = Left(Yaxis, Len(Yaxis) - 1) & "}" |
12 | theSeries = "=SERIES(""" & theTitle & """," & Xaxis & "," & Yaxis & "," & seriesNo & ")" |
13 | ActiveChart.SeriesCollection(1).Formula = theSeries |
Line 4 | prepares to hold the Category (Xaxis) and Series (Yaxis) data by opening curly bracket “{“. |
Line 5 | starts a loop varying x from Xmin to Xmax. |
Line 6 | produces Category data x in an Array format. |
Line 7 | replaces every occurrence of x in the equation with the current x value from the loop to create a calculable value. |
Line 8 | calculates (Evaluates) the value of the equation for each x. It also puts these values next to each other in an Array format. |
Lines 10 and 11 | close the Arrays by adding a “}”. |
Line 12 | puts them all together to produce the appropriate text for the Formula property of the SeriesCollection of the Chart. |
Line 13 | actually sets the Formula property to theSeries. |
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
How do your Excel skills stack up?
Test NowNext up:
- Don’t risk your confidential data – protect it with symmetric encryption
- Common sense is not so common
- Excel Shortcuts you will thank us for
- How-to: Open an Excel workbook containing macros, without running the macros
- Secure and sync in OneDrive for Business
- PowerShell Basics Series - Date and Time Manipulations
- Your next presentation … nailed!
- Create with Adobe’s Capture Mobile App: Patterns
- Quick Tuts: Word 2010 - Easily align text with leader tabs
- Handling difficult conversations at work
Previously
- Don’t risk your confidential data – protect it with symmetric encryption
- Automation control at your fingertips
- The four skill levels of facilitation
- Is your email inbox overloaded? Use these clever tips to manage it.
- Create and use workflow templates in SharePoint Designer 2013
- Round and round again with Excel
- 3 punctuation marks you might be using incorrectly
- Ready, set, goals
- 4 guides to help you become a PowerPoint pro
- Easy customisations in Word 2013