Introduction to Excel VBA

University of ChicagoGraduate School of Business

Introduction to Computer Based Models Bus-36102-81Mr. Schrage Spring 2003

What is the Visual Basic Add-in for Excel?

Makes the power of the Visual Basic programming language available to Excel models.

Why is this good?

1) Can automate tedious manual steps in using the spread sheet.e.g., run multiple cases, automatically adjust the sheet to handle data sets of different sizes.

2) Add new functions that can be used like a regular function in formulae, e.g., add the Black-Scholes formula.

3) Add new features that are essentially impossible in simple Excel, e.g., fancy dialog boxes.

Accessing VBA from Excel

Within Excel, click on:

Tools | Macro | Visual Basic Editor

To bring up a code sheet, click on:

Insert | Module

You can now enter code for your function in the code sheet.

Example: A One Argument User Defined Function

An important function in evaluating alternatives in decision making under uncertainty is the linear loss function.

If X is a random variable, and S is a decisionmaker specified threshold, then the expected linear loss is E[max(0,X-S)].

For example, the Black-Scholes formula is essentially the linear loss function for the log-Normal distribution.

Excel has no built-in linear loss functions. LINGO has built-in linear loss functions for the Normal(@PSL) and the Poisson(@PPL).

The following implements the linear loss function for the Normal distribution.

See file: vbapsl.xls

The code for the User defined function: psl();

See the file: vbapsl.xls

Function psl(z)

'Standard Normal linear loss function.

'Given the number of standard deviations, z,

'above the mean,

'psl = expected amount by which a standard Normal

' deviate exceeds z, i.e., psl = E[max(0,X-z)].

'

psl = Exp(-z * z / 2) / (2 * Application.Pi()) ^ 0.5 _

+ z * Application.NormSDist(z) - z

End Function

Things to note about a VBA function:

-Comment lines start with a ‘

-To use in VBA, a function that is in the application to which VBA is attached, Excel in this case, the function name must be preceded by “Application.”, e.g., Application.NormSDist(z).

-To continue an statement to the next line, put an underscore, _, at the end of the line being continued.

Example: Functions with More Arguments

The following simple user defined function computes the mean of a range.

Not a big contribution, but the same code is easily extended to compute standard deviations and correlations.

Some of the built-in functions in Excel are not very accurate and you may wish to replace them, e.g., stdev().

See the file: vbamymean.xls

Function mymean(region As Range)' Our own version of a function to compute the mean of a range Dim ourcopy As Variant Dim nrows As Long, ncols As Long, i As Long, j As Long Dim sum As Double ' Transfer the data to our own array ourcopy = region ' Find out how many rows and columns nrows = UBound(ourcopy, 1) ncols = UBound(ourcopy, 2) ' Add up all the elements sum = 0.0 For i = 1 To nrows For j = 1 To ncols sum = sum + ourcopy(i, j) Next j Next i ' Finally, the mean mymean = sum / (nrows * ncols)End Function

Automating Standard Keystroke Sequences

If there are certain keystroke sequences, you may wish to automate them so that either one or two keystrokes or a mouse click is all that is needed to cause the sequence to be executed.

There are two ways of doing this:

a) Use the Tools | Macro | Record feature in Excel, or b)Write a VBA function to do it if you want to do some fancy things along the way.

The following example illustrates the automation of a simple set of keystrokes you might use if you were managing a check register in Excel.

See the file vbabutton.xls

Sub addleftup()

' Subroutine to first

' move the cursor one cell right

ActiveCell.Offset(0, 1).Range("A1").Select

' and then insert a formula in the active cell

' to add the contents of cells just to the

' left and just above the active cell

ActiveCell.FormulaR1C1 = "=RC[-1]+R[-1]C"

End Sub

Adding a Button to a Sheet

Bring up the “Forms” toolbar by clicking on:

View | Toolbars | Forms

Click on the button button (4th from left) on Forms toolbar

and draw a button.

Right click on the newly drawn button to

edit the text in the button and to

assign it to the macro you have just written.

Learning More

There are a number of “popular” books on Visual Basic and VBA, but apparently none of “text book” quality w.r.t. thoroughness and consistent level of coverage.

When looking for books, realize the distinction between Visual Basic the stand-alone language, and VBA the add-in for Microsoft products such as Excel and MSWord.

A “popular” style book is:

Green, J., R. Rosenberg(2001), Excel 2002 VBA Programmer’s Reference, Wrox Press Ltd., Birmingham, UK, ISBN 1-861005-70-9 .