APPENDIX A
Excel and VBA
Premature optimization is the root of all evil (or at least most of it) in programming.
—Donald Knuth
The competent programmer is fully aware of the limited size of his own skull. He therefore approaches his task with full humility, and avoids clever tricks like the plague.
—Edsger Dijkstra
This appendix summarizes a number of years of hard-earned experience in using Excel and VBA. It is meant as a compendium of advice rather than a programming manual. For precise system and language semantics, please refer to online help facilities, Internet user interest group discussions, modeling books (e.g., Gottfried 1996, Benninga 2000, and Jackson and Staunton 2001), and programming texts. Some of my suggestions are personal and as such can be disputed. However, Excel can be notoriously nonmodular if used in a haphazard manner. Most of these “rules” force the modeler to abide by a stricter methodology to avoid common pitfalls.
Migration is key to model development in Excel. One typical migration path is as follows:
Pure spreadsheet model.
↓
Spreadsheet model with recorded macros for common operations.
↓
Spreadsheet model with user-written VBA macros (perhaps modified recorded macros) and functions.
↓
“Hollowed out” model with most computation in VBA, the spreadsheet used primarily for I/O and data archive.
↓
“Hollowed out” model with interface to Access or other database for data management.
↓
“Hollowed out” model with VBA code linked to compiled VBA libraries. ...
