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 chunk 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.
