[Introduction]  [Signal arithmetic]  [Signals and noise]   [Smoothing]   [Differentiation]  [Peak Sharpening]  [Harmonic analysis]   [Fourier convolution]  [Fourier deconvolution]  [Fourier filter]  [Wavelets]   [Peak area measurement]  [Linear Least Squares]  [Multicomponent Spectroscopy]  [Iterative Curve Fitting]  [Hyperlinear quantitative absorption spectrophotometry] [Appendix and Case Studies]  [Peak Finding and Measurement]  [iPeak]   [iSignal]  [Peak Fitters]   [iFilter]  [iPower]  [List of downloadable software]  [Interactive tools]

     

Appendix N: Using macros to extend the capability of spreadsheets

Both Microsoft Excel and OpenOffice Calc have the ability to automate repetitive tasks using "macros", saved sequences of commands or keystrokes that are stored for later use. Macros can be most easily created using the built-in "Macro Recorder", which will literally watch all your clicks, drags, and keystrokes and record them for later playback. Or you can write or edit your macros in the macro language of that spreadsheet (VBA in Excel; Python or JavaScript in Calc). Or you can do both: use the macro recorder first, then edit the resulting code manually to modify it.

To enable macros in Excel, click on File, Options, click Customize Ribbon Tab and check 'Developer' and click 'OK'. To access the macro recorder, click Developer, Record Macro, give the macro a name, click Options, assign a Ctrl-key shortcut, and click OK. Then perform your spreadsheet operations, and when finished, click Stop Recording and save the spreadsheet. Thereafter, simply pressing your Ctrl-key shortcut will run the macro and perform all the spreadsheet operations that you recorded.
 
Here I will demonstrate two applications in Excel using macros with the Solver function.  (See http://peltiertech.com/Excel/SolverVBA.html#Solver2 for information about setting up macros and solver on your version of Excel).

A previous section described the use of the Solver function applied to the iterative fitting of overlapping peaks in a spreadsheet. The steps listed in the second paragraph on that page can easily be captured with the macro recorder and saved with the spreadsheet. However, a different macro will needed for each different number of peaks, because the block of cells representing the "Proposed Model" will be different for each number of peaks. For example, the template CurveFitter2Gaussian.xlsm includes a macro named 'fit' for a 2-peak fit, activated by pressing Ctrl-f.  Here is the text of that macro:


Sub fit()
'
' fit Macro
'
' Keyboard Shortcut: Ctrl+f
'
    SolverOk SetCell:="$C$12", MaxMinVal:=2, ValueOf:=0,
       ByChange:="$C$8:$D$9", _
Engine:=1, EngineDesc:=
       "GRG Nonlinear"       
    SolverSolve
End Sub


 You can see that the text of the macro uses only two macro instructions: "SolverOK" and "SolverSolve". SolverOK specifies all the information in the "Solver Parameters" dialog box in its input arguments: 'SetCell' sets the objective as the percent fitting error in cell C12, 'MaxMinVal' is set to the second choice (Minimum), and 'ByChange' specifies the table of cells representing the proposed model (C8:D9) whose values are to be changed to minimize the objective in cell C12. The last argument sets the solver engine to 'GRC Nonlinear', the best one for iterative peak fitting. Finally, "SolverSolve" starts the Solver engine. You could easily modify this macro for curve fitter templates with other numbers of peaks just by changing the cells referenced in the 'ByChange' argument, e.g. C8:E9 for a 3-peak fit. In this case, though, is probably just as easy to use the macro recorder to record a macro for each curve fitter template.
 
 
Macros are especially useful if the task becomes more complex, as in TransmissionFittingCalibrationCurve.xls (screen image). This creates a calibration curve for a series of standard concentrations in the TFit method, which was previously described on TFit.html#spreadsheet. Here's a portion of that macro:


 
   Range("AF10").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Calculate
    Range("J6").Select
    Selection.Copy
    Range("I6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Calculate
    SolverOk SetCell:="$H$6", MaxMinVal:=2, ValueOf:=0, ByChange:="$I$6", Engine:=1 _
        , EngineDesc:="GRG Nonlinear"
    SolverOk SetCell:="$H$6", MaxMinVal:=2, ValueOf:=0, ByChange:="$I$6", Engine:=1 _
        , EngineDesc:="GRG Nonlinear"
    SolverSolve userFinish:=True
    SolverSolve userFinish:=True
    SolverSolve userFinish:=True
    Range("I6:J6").Select
    Selection.Copy
    Range("AG10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


The macro in this spreadsheet repeats this ch
unk of code several times, once for each concentration in the calibration curve (changing only the "AF10" in the first line to pick up a different concentration from the "Results table" in column AF). This macro uses several additional instructions, to select ranges ("Range...Select"), copy ("Selection.Copy") and paste ("Selection.PasteSpecial Paste:=xlPasteValues") values from one place to another, and re-calculate the spreadsheet ("Calculate"). Each separate click, menu selection, or key press creates one or more lines of macro text. The syntax is wordy but quite explicit and clear; you can learn quite a bit just by recording various spreadsheet actions and looking at the resulting macro text.


This page is part of "A Pragmatic Introduction to Signal Processing", created and maintained by Prof. Tom O'Haver , Department of Chemistry and Biochemistry, The University of Maryland at College Park. Comments, suggestions and questions should be directed to Prof. O'Haver at toh@umd.edu. Updated July, 2022.