Encyclopedia of Financial Models III

Introduction to Visual Basic for Applications

DESSISLAVA A. PACHAMANOVA, PhD

Associate Professor of Operations Research, Babson College

Abstract: Visual Basic for Applications (VBA) is a programming language environment that allows Microsoft Excel users to automate tasks, create their own functions, perform complex calculations, and interact with spreadsheets. Despite some important limitations, VBA adds useful capabilities to spreadsheet modeling and is a good tool to know for finance professionals for whom Microsoft Excel is the platform of choice.

This entry is a brief introduction to Visual Basic for Applications (VBA), the programming language environment that allows Microsoft Excel users to automate tasks, create their own functions, perform complex calculations, and interact with spreadsheets. We focus on features of VBA useful for financial applications. For a comprehensive introduction to VBA, good references are Walkenbach (2004) and Roman (2002). The Excel VBA help is also useful as a quick reference. All Excel commands listed in this entry are based on Microsoft Office 2007.

A SIMPLE EXAMPLE OF A VBA PROGRAM

Before we review some important characteristics of the VBA language, let us create a simple example of a VBA program. Excel has a tool for recording tasks performed in a spreadsheet, which can then be replayed as a macro. Macros in Excel record a sequence of commands, so that you do not have to repeat the same set of instructions if you need to perform the ...