Databases Reference
In-Depth Information
FIGURE 4.23
Recording a new macro named “LinReg.”
be used to capture the VBA commands utilized to accomplish a process. In this example, the macro
recorder will capture the commands involved in utilizing the linear regression tool. The macro
recorder can be started by selecting
To
ols->
M
acro->
R
ecord New Macro from the menu. This will
then bring up the dialog box shown in Figure 4.23.
The macro recording process will start after the user fills out the macro name to be recorded
(in this case “LinReg”) and presses the OK button. When the recording process starts, a GUI like
that shown in Figure 4.24
will appear. Excel will continue recording the VBA commands required
to automate the processes the user is carrying out until the square stop button is pressed (much
like a tape recorder).
To record the linear regression process, choose from the menu
T
ools->
D
ata Analysis and select
Regression in the dialog box as shown in Figure 4.25.
The sample linear regression data set is stored in the Workbook “linearfitexample.xls.” If the
user fills out the GUI as shown in Figure 4.26 and then presses the OK button, the following macro
will be recorded:
Sub LinReg()
' LinReg Macro
' Macro recorded 11/01/2004 by Brian Bissett
Application.Run "ATPVBAEN.XLA!Regress",
ActiveSheet.Range("$B$1:$B$11"), _
ActiveSheet.Range("$A$1:$A$11"), False, True, ,
"FitResults", False, _
False, False, True, , False
End Sub
Looking at this code, recording the linear regression process tells the user several things. First,
the regression is run in the template “ATPVBAEN.XLA,” and the subroutine name is Regress. This
subroutine has numerous parameters, and it is not immediately apparent looking at the code what
FIGURE 4.24
When recording a new macro, this GUI will appear.
