1 / 12

Introduction to Excel VBA

University of Chicago Graduate School of Business. Introduction to Computer Based Models Bus-36102-81 Mr. Schrage Spring 2003. Introduction to Excel VBA. What is the Visual Basic Add-in for Excel?

caesar
Download Presentation

Introduction to Excel VBA

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. University of Chicago Graduate School of Business Introduction to Computer Based Models Bus-36102-81 Mr. Schrage Spring 2003 Introduction to Excel VBA

  2. 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? • 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. • Add new functions that can be used like a regular function in formulae, e.g., add the Black-Scholes formula. • Add new features that are essentially impossible in simple Excel, • e.g., fancy dialog boxes.

  3. 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.

  4. 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

  5. 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

  6. 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.

  7. 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

  8. 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

  9. 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

  10. 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

  11. 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.

  12. 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 .

More Related