Advanced Modelling in Finance Using Excel and VBA

Chapter 3. Introduction to VBA

This chapter introduces the use of VBA and macros within Excel, and attempts to do so in the context of examples where VBA enhances spreadsheet functionality. Whilst not intended as a full introduction to programming in VBA, it argues the case for mastering some VBA, touches on the 'object-oriented' focus of the language, and suggests an incremental approach to mastering the code. Most of the examples in this chapter involve simple code, the first 'hands-on' examples being in section 3.3.3. However, in section 3.6, several applications are developed more fully and some fruitful areas for macros identified. These include subroutines to produce charts, to calculate and display normal probability plots, and to generate the efficient frontier. A related workbook, VBSUB.xls, contains the examples discussed in the chapter and can be used for reference. However, the reader is advised to start by entering code in a new workbook (without opening the VBSUB workbook).

Throughout the book, the VBA coding used for routines is reviewed. The functions and macros developed in subsequent chapters build on the basic foundations laid down in this and the next chapter.

ADVANTAGES OF MASTERING VBA

Macros form an important part of an experienced user's palette for two main reasons: they are an excellent way to control repeated calculations, and they can be written to assist third-party users who might have less familiarity with spreadsheets. From our perspective, the main ...