O'Reilly logo

Structured Finance Modeling with Object-Oriented VBA by Evan Tick

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required