46
Introduction to VBA
65
For reference, the macros to generate the efficient frontier are in ModuleS of VBSUB
and can be run from the Eff1 sheet. Before you use any macro containing Solver, you
must establish a reference to the Solver add-in. With a Visual Basic module active,
click References on the Tools menu, then Browse and find Solver.xla (usually in the
nOfficenLibrary subfolder).
SUMMARY
VBA is an object-oriented version of the Basic programming language. As well as familiar
programming ideas concerning variables and coding, there are also methods and properties
for use with Excel objects.
You automate operations in Excel first by writing the code for subroutines and functions
in a VBA Module within the workbook, then by running your macros. The Visual Basic
Environment provides some tools for debugging macros and access to VBA libraries and
online help.
The purpose of VBA subroutines is to carry out actions: in contrast, VBA user-defined
functions return values. In the context of financial modelling, we have found that functions
are more useful than subroutines. The exceptions are operations that require the production
of charts, and automating optimisation tasks using Solver. Subroutines are frequently
useful for one-off administrative tasks, which require lots of repetition.
The macro recorder can be used to translate your actions into VBA code. Recording
keystrokes in macros is sometimes useful as a starting point, for getting coding clues and
insights. Whereas the recorder tends to generate verbose code for simple operations, it
can produce highly sophisticated code for many menu-based procedures.
Most Excel functions can be included in VBA macros and there are some special VBA
functions for use in macros. This is a useful way of leveraging the knowledge of the
experienced Excel model builder.
There are advantages to mastering VBA. The replacement of calculations by functions
can lead to more powerful cell formulas and makes calculation sequences more robust
and faster. Skilful use of macros to control repeated operations can remove other sources
of error from spreadsheet operations.
REFERENCES
Green, J., 1999, Excel 2000 VBA Programmer’s Reference, Wrox Press Ltd., Birmingham.
Leonhard,W.,L.HudspethandT.J.Lee,1997,Excel97Annoyances,O’Reilly& Associates,Inc.,Sebastopol, CA.
Walkenbach, J., 1999, Excel 2000 Power Programming with VBA, IDG Books, Foster City, CA.
Wells, E. and S. Harshbarger, 1997, Microsoft Excel 97 Developer’s Handbook, Microsoft Press.
APPENDIX 3A THE VISUAL BASIC EDITOR
The Visual Basic Editor was substantially changed for Excel 97 (and later versions) and
this appendix details some of its features. It can be activated by pressing the VBE button
(on the VB toolbar) or by pressing AltCF11 in the Excel window. Once activated, you
can toggle between the Excel and the Visual Basic windows with AltCTab in the usual
manner. The Visual Basic Editor can also be opened via the Excel menubar by choosing
Tools then Macro then Visual Basic Editor.
The Visual Basic window should look something like that shown in Figure 3.7, which
includes a menubar and a toolbar across the top; the Project Explorer and the Properties
windows on the left-hand side and the Code window on the right. In the illustration, the
