This chapter describes some core elements of Excel's Visual Basic for Applications (VBA). A good familiarity with this is essential for anyone wishing to progress beyond basic level financial modelling applications. The text focuses on the topics that we consider to be the most important. We aim to enable the reader who is a beginner with VBA to rapidly become familiar with core operations and to be able to write and use some simple, yet practical and robust code. In particular, we focus on subroutines and user-defined functions: subroutines consist of sequences of tasks or processes, whereas user-defined functions perform bespoke calculations that may not be available as Excel functions. Other areas of VBA, such as user forms and class modules, are not covered in this text. In this way, we hope to have created a text that is accessible for the beginner whilst still being of high value when applied to many real-life modelling situations.
This chapter attempts to present only those concepts which are essential to getting started and to demonstrate the core concepts; topics concerning the writing of more general and robust code are covered later in the text. The first section of this chapter provides an overview of the main uses of VBA. The second section presents some core building blocks and key operations.
Note that in this Part of the text, we use the term “Excel” (or “pure Excel”) to refer to the traditional part of Excel where no ...