A Brief Introduction to VBA

1. A Brief Introduction to VBA

2. Overview • The purpose of this presentation is to provide you with a very brief introduction to VBA, a programming language that is embedded within the Microsoft Office suite of software products. • The goals of this presentation are modest: they are to get you started, to provide you with the basic tools and background that you will need to be immediately able to start working on your projects. This is not a comprehensive guide to VBA – it is a very complex language – rather, it is designed to teach you the rudiments of the language.

3. Overview In particular this presentation will : • Show you how to record a macro and then edit it. • Explain the differences between macros, functions and subroutines. • Discuss how to insert modules for functions and subroutines, and how to enter functions and subroutines. • Discuss how the keys to program flow and control. • Introduce you to two different types of loops. • Discuss how to perform basic debugging operations.

4. Macros and VBA • VBA is Visual Basic for Applications. It is a modified version of Microsoft’s well-known Visual Basic language. • Although many users treat VBA as nothing more than a macro-language, in truth it can be much more. It allows: • moderately complex programming, • one to include in VBA programs some of the functions that are built into Excel, • it allows the user to read data from and write data into Excel, • and it allows the user to create programs that can use various components from the Microsoft suite of products. • The key to realize is that VBA is a tool to use to supplement Excel.

5. Macros and VBA Most users of Excel are at least somewhat familiar to macros. We will start with a simple Macro example, and then examine the structure and code of the example to begin to gain insight into VBA. • To begin, open a new workbook in Excel • Click on the Tools menu, and look toward the bottom. One of your options will be “Macros”. Click on the Macro’s button. • The second choice will be “Record New Macro”. Click on that choice. • You will then receive a pop-up window, asking you to name your macro (the default will be something like Macro1). Type in “Create_text”. Make sure that in the window where it says “Save In”, that “This WorkBook” is selected.

6. Macros and VBA • Click on “OK” to begin recording your Macro. • The macro we are going to create we only want to do something simple: Insert a few lines of text and today’s date in a few specific places in the spreadsheet. • To record this macro, all you have to do is demonstrate to Excel what you want the macro to do. • First, press the F5 key – this is the “goto” key. You will then be prompted for which cell you wish to go to: enter a1. • You will be taken to cell A1. Enter the following text: TITLE PAGE MACRO FOR FINANCE.

7. Macros and VBA • After typing in the line of text (and, of course, pressing the “enter” key), press the F5 button again, this time tell Excel to go to cell c15. • Once you get to cell c15, enter “=today()”. This is a function that simply puts today’s date in the cell. • Now, let’s stop the macro recording. To do this click on the little blue square in the macro popup box that is on the screen. • To demonstrate that the macro works, clear the entire spreadsheet. To do this, press ctrl-A to highlight the entire spreadsheet and then press the delete key.

8. Macros and VBA • Now we can demonstrate that the macro works. Again click on the “tools” menu and select “macro”. • This time, however, select the first option “Macros”. • This will bring up a list of all macros in the spreadsheet (there will likely only be one, the one you just created labeled “create_text”). Select “create_text”, and then click on the RUN button. This will run your macro.

9. Macros and VBA • We can now examine the macro. The macro is really just a small subroutine written in VBA. To see the macro text, just once again click on Tools, Macro, and then click once on your macro name. Then click on the “Edit” button. • This will bring up a new item on your application bar – the Microsoft Visual Basic Editor (we’ll show another way of bringing this up later.) • Your program should look something like this:

10. Macros and VBA Sub create_text() ' ' create_text Macro ' Macro recorded 7/28/2003 ' ' Application.Goto Reference:="R1C1" ActiveCell.FormulaR1C1 = "Financial Modeling Title Page" Range("A2").Select Application.Goto Reference:="R10C3" ActiveCell.FormulaR1C1 = "=TODAY()" Range("C11").Select End Sub

11. Macros and VBA • Notice that the “macro” is enclosed between two lines: sub create_text() end sub • Formally a macro is a subroutine to visual basic. The line sub create_text() tells visual basic that a subroutine named create_text is beginning. VBA assumes that each line that follows is part of that subroutine until it reaches the line end sub at which point it knows the subroutine is over.

12. Macros and VBA • Each line of the program is executed sequentially – that is, one after the other. Let’s look at what each line does • The first several lines (all beginning with ') are simply comment lines that VBA ignores. ' ' create_text Macro ' Macro recorded 7/28/2003 '

13. Macros and VBA • Application.Goto Reference:="R1C1“ • This line is how the macro tells the spreadsheet to go to cell A1. “Application.Goto” means for VBA to use the “GOTO” function from the application – in this case from Excel (remember VBA can be used from Word, Access, and other Office applications.) So literally “Application.Goto” means, use the function named “goto” embedded in this application. • The second part of this line , Reference:=“R1C1” tells VBA to go to row 1, column 1 (which is column “A”). • Note that later on, when we want to go to cell C10, we use the same line, but with Reference:”R10C3”.

14. Macros and VBA • The “Application.” construct is important in VBA. For the most part, you can access any of Excel’s built in functions that way! • The next line is: ActiveCell.FormulaR1C1 = "Financial Modeling Title Page" • This line says to make the contents of the current cell the text which is to the right of the equal sign. • The line that follows that is saying that after entering the text, go down one column (the default action in Excel.) Range("A2").Select

15. Macros and VBA • The “Application.Goto” command is used again, this time to go to cell c10. Application.Goto Reference:="R10C3" • Finally, the “today” function is inserted in cell R10C13: ActiveCell.FormulaR1C1 = "=TODAY()“ • Notice that the only difference between that line and the line where we entered our text, is that instead of entering just the text, it puts the equal signs inside of the quote.

16. Macros and VBA • Notice that you can easily modify your macro now that you are in the program. For example, you could modify the text to say “My modified title page”. Just go to the line that says ActiveCell.FormulaR1C1 = "Financial Modeling Title Page“ and replace “Financial Modeling Title Page” with “my modified title page”. • Similarly if you wanted to enter something other than today’s date in the cell C10, you could replace “=today()” with anything you wanted.

17. Macros and VBA • Using the Macro record feature has a lot of advantages, especially if you would like to learn the appropriate VBA code or command, but it is of only limited use when trying to develop more complex program – such as a program than can take conditional actions, or that repeat an action for a specific (or even conditional) number of times. • To do that, we really need to be able to create programs without first recording them as macros. • We will examine how to do this in the next several sections.

18. Creating a Program • So how do you create a program in VBA? Its relatively easy. • To make our example clear, shut down Excel, and then restart it, opening a clean spreadsheet. • Once again, click on the “Tools” menu and select “Macro”. • This time however, select the option on the menu labeled “Visual Basic Editor”. This will open up a new window (and new item on your application bar at the bottom of you screen.)

19. Creating a Program • This new screen – the VBA editor – is very busy and has a lot of very useful tools on it, but for now we are largely going to ignore them and focus on building some very simple tools. • To do this we first need to find a place to enter our new program: we must create a “module” in which to store it. • Do to this click on the “insert” menu and then click on “module”. • This will open up a white-screen on the right side of the screen. • We are now almost ready to begin, but first we have to decide what type of program to create.

20. Subroutines vs. Functions • As mentioned a moment ago, Macros are considered subroutines within VBA. In actuality, however, there are two types of programs in VBA that we will examine, subroutines and functions. • Subroutines are the more general of the two – they perform any number of activities. • Functions are more specific types of programs. They accept a fixed number of inputs and then return a single value back to the user. The nice thing about a function is that you can call it from within excel in exactly the same way that you call an Excel function – by putting the = sign in front of the name!

21. Functions • Because they are somewhat more limited (and also because you are more likely to use them first), we will begin with functions. • Let us begin with a very simple function, one that takes two numbers, let’s call them a and b, adds them together and then returns them to the user. Let’s call this function adder. Thus, once written, we will call it from Excel entering the line: =adder(a,b) • Where a and b would be replaced by actual numbers.

22. Functions • To begin with we have to tell Excel we are creating a function and tell it the name of that function. To do this we enter the following: function adder(a, b) • This says that you are creating the function adder and that the user will supply to parameters, a and b. • Notice that as soon as you type this in, Excel automatically adds the line end function

23. Functions • All of the lines for this function must go between those two lines. • We are going to make the simplest possible function: on the first line simply type: adder = a+b • That is it, we have completed the function. What this says is to assign the function adder (and return to the cell in the spreadsheet) the sum of the parameters a and b.

24. Functions • Go back to your main spreadsheet (click on the Excel tab on the bottom of your screen). • Select any cell you like, type in =adder(5,5) • Notice that 10 is now returned placed in the cell. • Go to another cell, type in =adder(12,12) • Notice that 24 is now entered in the new cell, but 10 is kept in the old cell.

25. Functions • Indeed, you can notice that you can use the adder function within excel just like you would use any Excel function. • For example, the Excel Function max(y,z) places in the cell the greater of the two values y or z. Type in the following line: =max(10,adder(13,-2)) • The adder function evaluates to 11, and that is the max of 10 and 11, and so it is returned.

26. Functions • You can even have recursive calling of a function (meaning the function can call itself): =adder(5,adder(3,6)) Will equal 14. • In many ways Excel will treat this as if it were a built-in function. For example, if you click on the fx button, it will call up all of Excel’s function. If you click on the function type drop down menu, you will see “User Defined” as one of your options. Click on User Defined and you will find “adder”. Click on that and it will bring up a function box with instructions on using adder.

27. Functions • Of course you most likely will be doing more complex tasks within a function. • Let’s say that you wanted to determine the modified duration of a single cash flow to be received in N years when the interest rate is r. • You could type in the following: function mod_dur(cf,r,n) • Again, excel will put the “end function” line in for you. • This says that your function will need three inputs, the cash flow, the discount rate, and the number of periods before you receive the cash flow.

28. Functions • We also know that we modified duration is the same as dollar duration divided by the price of the bond (in this case we are assuming it is a zero coupon bond!) • We may want to calculate those to values first and then calculate modified duration. We will need a variable to store those numbers. To create a variable in a VBA program, use the dim command. Let’s create two variables, dpdr and p: Function mod_dur(cf,r,n) Dim dpdr, p End function

29. Functions • We can now use those two variables to store our values: Function mod_dur(cf,r,n) Dim dpdr, p P = cf/(1+r)^n Dpdr = (-n*cf)/(1+r)^(1+n) Mod_dur=drdr/p End function

30. Function • What the program does now is clear: • It creates the variables p and dpdr • It assigns to p the present value of the cash flow discounted for n periods at the entered discount rate. • It assigns to dpdr the dollar duration of the cash flow. • It then tells the function to return dpdr/p.