Recording a Macro

We want to record a macro that will fit a decaying exponential curve to two data sets. Once recorded, this macro may be applied to any other set of data with the same format.

This macro could then be extended to fit more than two data sets by recording the fitting process multiple times but doing this is not a flexible procedure. So we will replace the second half of the macro with a simple for-loop that will generalize the macro to allow fitting any number of data sets. After this a dialog will be added to the macro to allow changing the number of data sets fitted without entering the macro code and changing the Last parameter in the for-loop.

If you want to follow along with this example click on the link to download a self-extracting file containing a three SigmaPlot notebooks with exponentially decaying data.

The two data sets that we want to fit are displayed below. To view this data double click the graph icon in the Exponential Data Set 1.jnb notebook.

Start macro recording by selecting the Record New Macro button from the Toolbox ribbon. The small Record dialog appears indicating that every step you make thereafter will be recorded.

Place your cursor over one of the black data symbols and right click. Select the Fit Curve option as shown on the left below to obtain the first Regression Wizard dialog. Part of this dialog is shown on the right where the Single, 2 Parameter fit function is selected from the Exponential Decay equation category.

Click Next twice to perform the curve fit (nothing needs to be done after the first Next click). Click Next again and select the options to place variables in the worksheet as shown on the left below. Then click next and make sure that the “Add curve to” option is checked.

Click Finish to place the fit line for the solid data symbol on the graph.

We want to repeat the same process for the second data set but since the options in the Regression Wizard are sticky we only need to repeat the first two steps. Right click on a data point with an open symbol and select Fit Curve. Then click Finish.

The same function is fit to the second data set and the fit line placed on the graph.

This completes our macro recording so click on the Stop Recording button on the small Record dialog and enter the name Exponential Fit for your macro and click Ok.

Lets test it and learn about some important issues with macro recording. But before we do, let´s make accessing the macro much easier. See how to add the macro name to a ribbon button so that we can run it directly from the Toolbox ribbon.

Open the Exponential Data Set 2.jnb notebook and double click on the graph icon to obtain the following graph.

Make sure the graph page is the current window (if it isn’t you will get an error message; more about this later). Run the macro directly from the menu item that you just set up.

The macro runs, fits the exponential function to the two data sets and places the fit lines on the graph. If the graph window is not the current window and is hidden behind the worksheet, then click on it to view it (we will make a slight modification to the macro later to improve this behavior).

You have successfully recorded a macro that will save considerable time when analyzing your data. But the macro has the limitation that you must always fit two data sets – no more, no less. To improve this you can add a for-loop in the macro to allow any number of data sets to be analyzed.

If you want to analyze a single data set or more than two data sets you can modify the code to change the “Last” value in the for-loop to a value different than 2. However, it is better to generalize the macro by adding a dialog to allow the user to enter the number of data sets to be analyzed without modifying the code. For eight data sets, demonstrated in this link, this macro really begins to shine and save operator time

It is important to realize that a recorded macro repeats exactly the same steps that were recorded and needs, therefore, exactly the same environment under which these steps were recorded. For example, trying to run a macro that curve fits data in columns 1 and 2 in a worksheet that contains data only in columns 3 and 4 will obviously not work. Look at the macro code to understand in more detail what this means.

Try SigmaPlot FREE for 30 Days!