Advanced Modelling in Finance Using Excel and VBA

Chapter 4. Writing VBA User-defined Functions

As well as automating spreadsheet operations, VBA code can be used to write functions which work in the same way as Excel's 'built-in' functions. Functions are particularly useful when they automate calculation tasks that are required repeatedly. Function calculations are carried out 'off sheet', allowing leaner and cleaner layouts to be devised, and functions are portable so that once programmed they can be copied to other workbooks.

Whereas a VBA subroutine usually performs one or more actions, a VBA function is a set of instructions that returns a single value (similar to the SUM function) or an array of values (like the LINEST function). User-defined functions combine real programming (complete with loops and conditional branching) together with Excel functions (such as NORMSDIST and MMULT).

Functions are simplest to write when they operate on single numerical inputs (scalar inputs) and when they return single cell values. This chapter starts by developing a simple sales commission function with one input and one output to illustrate the steps involved in writing and using functions. Functions with several scalar inputs are written in much the same way. As an illustration, a mathematically more challenging function based on the Black–Scholes option value formula is used. Function writing becomes more exacting when the inputs are arrays rather than single values. To illustrate some aspects of handling arrays, functions for the expected ...