Excel macro tutorial: graphing equations
Embedded within Excel we have the ability to program keystrokes, such that repetitive tasks can be automated. Here we will learn to write our first macro. This tutorial will use the “kiss” approach, i.e. “keep it simple”. That is, concepts covered will be extremely basic. That is the way I learn, so I hope it helps you as well. We will be using Excel 2010, but most if not all of the concepts should apply to earlier versions as well. In order to start the tutorial, refer to this earlier published hub:
It shows us how to display the Developer tab, which we will need to write our macro. One you get the tab displayed, we can get started. Macros are good for simple repetitive tasks. A much more powerful tool, VBA, or Visual Basic programming, is also accessible under the Developer tab. VBA programming will be considered in other hubs to follow. Open up Excel, you will see worksheet “Book1.” Rename it: File --> Save As --> call it “macro”. Since the workbook will contain code, we should save it as type “Excel macro enabled workbook”. There are three sheets, “Sheet1”, “Sheet2”and “Sheet3”. Cell A1 is selected in Sheet 1. Select “Sheet2” and “Sheet 3” tabs at bottom, right click and delete them.
Our macro will be useful for mathematics, as it will graph polynomials. We need to set up Sheet 1 data table for graphing, as shown in diagram #1. See we have x values from -5 to 5, and y values calculated according to the linear equation y = 2x + 1. So column B applies the formula to column A’s x values. The formula was typed into cell B2, and then copied/pasted into B3 to B6. Now go select x and y data as per diagram #2. With the data selected, we will record the graphing macro. Click on the Developer tab and then on “record macro”. A box will come up as per diagram #3. Fill it in as shown. We named it “graph” and made the shortcut key “ctrl a”. When we are done, ctrl a will generate the graph. From here on in, any key strokes we do in the workbook will get recorded as part of the macro. So, we have to be careful and deliberate. If a mistake is made, just hit “stop recording” under Developer tab. Click macros and delete macro “graph”. You can then start over.
Start recording the macro: Insert --> scatter --> click 2nd chart type down in left column. The graph will appear. Click “Select Data” --> ”Edit” and blinking cursor in “series name” will appear. Click on worksheet cell B2 where we find “y = 2x+1”, and hit “OK” twice. Title “y = 2x+1” shows up on graph. Click “Developer” tab and then “stop recording” at left. The macro has been recorded. To test it, single click on chart and delete it. Now hit “ctrl a” and see the graph again.
We will do another graph with the same macro. Delete the graph/chart. Change the equation as per diagram #4. “y = x^2” is placed in cell B1, and formula “= A2^2” in B2. The formula in B2 is then copied/pasted into B3 to B6. We will now see the graph of parabola y = x2 when we hit “ctrl a”. We have successfully created a graphing macro. Note you can also change the x values to make the graph larger. But, our macro only looks for five x values. If you want more x values, you can start fresh with, say 10 of them, and record a 2nd macro using a larger number of selected data points at the start. One cautionary note, our macro will not handle the square root of a negative number. It will try to graph zero, which is of course, incorrect. Put another way, it cannot handle complex numbers.