Corporate Valuation Modeling: A Step-by-Step Guide

CHAPTER 11

Automation Using Visual Basic Applications (VBA)

A powerful 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 and repetitive actions, and in more advanced situations for calculations that exceed the spreadsheet’s processing ability. Examples relevant to corporate valuation modeling include creating a system to run various sensitivity scenarios, implementing iterative processes in code that overcome the circular references left in DCF models due to financial plugs, and installing buttons and checkboxes to control sheet printing and report distribution. 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 introduces the model operator to the basics of using VBA through additions to the example model. Beginners might find additional texts helpful for further explanation, while intermediate-to-advanced users might want to skip ...