Modeling Structured Finance Cash Flows with Microsoft® Excel®: A Step-by-Step Guide

CHAPTER 10

Automation Using Visual Basic Applications (VBA)

Apowerful capability of all Microsoft Office applications is the ability to program actions using the Visual Basic Applications (VBA) programming language. For Microsoft Excel, VBA is especially useful for frequently used commands that require multiple procedures, repetitive actions, and in more advanced situations for calculations that exceed the spreadsheet’s processing ability. Examples relevant to cash flow modeling include creating buttons that control print commands and goal seek functions, building a quick system to run multiple scenarios, or constructing an amortization engine that can generate and aggregate the cash flow for thousands of loans. Implementing such functionality requires a basic understanding of the VBA language and how the language interacts with Excel.

Most users have unknowingly used VBA by recording a macro to complete simple repetitive tasks. However, few take the step to learn how to write and edit VBA code by hand. The problem most users have with unlocking the full potential of VBA is learning how an object-oriented programming (OOP) language works. While entire books can and have been written on using VBA, this chapter intends to introduce the model operator to the basics of using VBA through additions to Project Model Builder and other relevant examples. Beginners may find additional texts helpful for further explanation, while intermediate to advanced users may want to skip to the specific ...