Download Vba coding cheat sheet and more Cheat Sheet Computer Science in PDF only on Docsity!
VISUAL BASIC FOR APPLICATIONS (VBA)
Visual Basic for Applications (VBA) is the programming language attached to Excel. VBA is very functional and flexible. Because of its ready integration with Excel worksheets, VBA is widely used in the financial community. VBA incorporates many features that are part of standard programming languages, and it is not difficult to master if you have some programming experience. You do not need to be proficient in VBA to understand Sections I–VI of Financial Modeling. These sections can be understood without anything more than the very rudimentary VBA principles incorporated in the preface to this book (or alternatively in the small file called “Adding Getformula to your Spreadsheet” that is part of the disk that comes with the book). The four chapters of this section cover Visual Basic for Applications (VBA) topics for the reader interested in developing his or her own programs. Chapter 36 shows how to write functions that can be added in to Excel spreadsheets. Financial Modeling uses many of these “homemade” functions. Examples are the two-stage Gordon model (Chapter 3), Black-Scholes pricing of options (Chapter 17), and derivation of the Nelson-Siegel term structure (Chapter 22). Chapter 37 discusses more advanced topics related to variables and arrays in VBA. We have used this topic in fixing the bugs in Excel ’s XNPV and XIRR functions (Chapter 1). Chapter 38 shows how to build subroutines in VBA. A subroutine is not a function, but rather an automation of some repeti- tive action. Financial Modeling uses subroutines in a number of places—for example, in computing the efficient frontier without short sales (Chapter 12). Finally, Chapter 39 discusses objects and add-ins. Among other topics dis- cussed in this chapter is the creation of user-defined add-ins in Excel.
VII
946 Chapter 36
In this section we will write our first user-defined function. Before you can do this, you need to activate the VBA editor. You can do this either by using the keyboard shortcut [Alt] + F11 or from the Excel ribbon ( Developer Tab|Visual Basic Editor ). By default, Excel doesn ’t display the Developer tab on the Excel ribbon. To show the Developer tab, go to File|Options| Customize the Ribbon and indicate Developer :
947 User-Defined Functions with VBA
The result in both cases is a new window like the following screen shot (your window may look slightly different, but it will be functionally equivalent).
949 User-Defined Functions with VBA
As soon as you end the line with a tap on the Enter key, VBA will do a cleanup job. The color of all the words that VBA recognizes as part of its programming language (“reserved words”) will change. All reserved words will be capitalized. A space will be added after the comma separating the first parameter from the second parameter. The closing line for the function will be inserted, and the cursor will be in position between the header and the closing line ready for you to go on typing.
950 Chapter 36
Function plus(parameter1, parameter2) plus = parameter1 + parameter End Function
We are now ready to type our function line. This is the line that makes our function do something.^2 Our first function will take two variables and return their sum:
- The indentation of lines in VBA code, which we added manually, is not required by VBA but makes reading the code much easier.
You can now use this function in your spreadsheet:
1 2 3 4
A B C
Parameter1 3. Parameter2 1. Plus 4.75 <-- =plus(B2,B3)
PLUS IN ACTION
952 Chapter 36
You can also use the function in the Excel Function Wizard. Clicking on this icon on the toolbar will produce the following screen:
953 User-Defined Functions with VBA
Selecting User Defined from the pull-down menu will present the following screen listing all user-defined functions; one of them should be the function we have just added, plus :
955 User-Defined Functions with VBA
36.3 Providing Help for User-Defined Functions in the Function Wizard
Excel ’s Function Wizard (shown below) provides a short help line (an explana- tion of what the function does). Here ’s how Excel explains its own functions in the Function Wizard:
956 Chapter 36
Click in the Macro name box, and type the name of the function (notice that you don ’t see the function name in the macro dialogue box above … you have to type it in):
To attach a text description to our function, activate the macro selection box. You can do this either from the Excel ribbon ( Developer|Macros ) or by using the keyboard shortcut [Alt] + F8.
958 Chapter 36
Type the description in the Description box. Click OK , and close the macro selection box. Our function now has a help line.
Excel functions have help lines attached to each of the parameters and a help file entry. We can supply the same for our function; sadly, the subject is beyond the scope of this introduction.
36.4 Saving Excel Workbook with VBA Content
At some point in the process, you need to save your work. 3 Starting with Excel 2007, an Excel workbook with VBA content has to be saved as a “macro- enabled file.” When you first try to save a workbook with VBA content, Excel will present you with the following message:
- We suggest soon and often.
959 User-Defined Functions with VBA
You should choose No and get the Save As dialog to enable you to choose a new file type.
961 User-Defined Functions with VBA
Mistake 2: Right Syntax with a Typing Error
It ’s easy to make typing errors that will only be detected once you try to use the function. In the example below, we define two functions— function1 and function2. Unfortunately, the program line for function2 mistakenly calls the function “function1”:
The VBA editor does not immediately recognize this mistake. The mistake will pop up when you try to use the function in a worksheet. Excel will notify you that you ’ve made a mistake and take you to the VBA editor:
If you recognize your mistake, you can correct it. You can also try to go to the VBA help by clicking Help (in many cases this will lead to an incompre- hensibly complicated explanation).
962 Chapter 36
Suppose you recognize your mistake. You click OK , and get ready to correct the error by replacing the word “Function1” with “Function2.” At this point your screen looks like this:
Notice:
A. The word [break] in the title bar. B. The offending symbol is selected. C. The function line is highlighted and pointed to by an arrow in the margin.
Because VBA found an error while trying to execute the function, it moved into a special execution mode called debug-break mode. For now all we need to do is get out of this special mode so we can get on with our work. We do this by clicking the icon on the VBA toolbar. Now you can fix the function and use it. We can (and should) have VBA check the module for errors before trying to use the functions in the module. From the VBA menu we select Debug|Compile VBAproject ; this will find the first error in the module and point it out as before but without going into debug-break mode.
