Credit Risk Modeling Using Excel and VBA with DVD

Appendix A1

Visual Basics for Applications (VBA)

MACROS AND FUNCTIONS

In this book, we use VBA (Visual Basic for Applications) to write macros and user-defined functions. Macros and functions are routines for running a series of commands specified by the user. The key differences between macros and functions are as follows:

• User-defined functions can be used like standard spreadsheet functions, e. g., we can type =OURFUNCTION(arguments) into a cell (or range of cells in the case of an array function) of the spreadsheet. The function will be run and the result will be returned in the sheet. Macros have to be called by some action that we take outside a cell.
• The output of a function extends only to the cells in the sheet that it applies to; a function cannot be used to change other cells. For example, if we type =OURFUNCTION(arguments) into cell A1, we cannot make this function fill B3 with some value. With a macro, we can change any cell in the worksheet, move to other sheets and so on.

WRITING A NEW MACRO OR FUNCTION

To start writing a macro or function, open the VBA editor: the short-cut would be pressing [alt]+[F11]. Although this shortcut works in all Excel versions, the alternative menu path differs. Prior to Excel 2007 select Tools→Macro→Visual Basic Editor. In Excel 2007 you find the Visual Basic item on the Developer tab. If the Developer tab is not shown, click on the Office button (the round symbol in the upper left), click Excel Options, then Popular and select ‘Show ...