Published on March 6, 2009
A Brief Introduction to VBA : 1 A Brief Introduction to VBA Overview : 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. Overview : 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. Macros and VBA : 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. Macros and VBA : 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. Macros and VBA : 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 FINN 6210. Macros and VBA : 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. Macros and VBA : 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. Macros and VBA : 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: Macros and VBA : 10 Macros and VBA Sub create_text() ' ' create_text Macro ' Macro recorded 7/28/2003 ' ' Application.Goto Reference:="R1C1" ActiveCell.FormulaR1C1 = “FINN 6210 Title Page" Range("A2").Select Application.Goto Reference:="R10C3" ActiveCell.FormulaR1C1 = "=TODAY()" Range("C11").Select End Sub Macros and VBA : 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. Macros and VBA : 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 ' Macros and VBA : 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”. Macros and VBA : 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 = “FINN 6210 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 Macros and VBA : 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. Macros and VBA : 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. Macros and VBA : 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. Creating a Program : 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.) Creating a Program : 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. Subroutines vs. Functions : 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! Functions : 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. Functions : 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 Functions : 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. Functions : 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. Functions : 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. Functions : 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. Functions : 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 present value of a single cash flow to be received in N years when the interest rate is r. You could type in the following: function pres_val(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. Functions : 28 Functions We may want to calculate an intermediate result and to temporarily store it. We will need a variable to store those numbers. To create a variable in a VBA program, use the dim command. Let’s create a variable, pv_factor Function pres_val(cf,r,n) Dim pv_factor End function Functions : 29 Functions We can now use this variable in an intermediate role (albeit a rather silly one in this example Function pres_val(cf,r,n) Dim pv_factor pv_factor = (1+r)^-n pres_val = cf*pv_factor End function Functions : 30 Functions What the program does now is clear: It creates the variable pv_factor to hold the present value factor determined by r and n. It then determines the actual present value by multiplying pv_factor by the cash flow. It then returns the present value back to the calling function or routine. Functions vs. Subroutines : 31 Functions vs. Subroutines For our purposes, there are very few differences between programming a function and a subroutine. There is one very important difference, however: A function cannot directly write data back to the spreadsheet, it can only return the one value that it calcualtes. A subroutine can write data back to the spreadsheet whenever it wants. So how does a subroutine read and write data to the spreadsheet? There are actually many ways, but the easiest to use and understand is the cells(r,c).value method. Functions vs. Subroutines : 32 Functions vs. Subroutines To read data directly from a spreadsheet into a variable, use the following command: variable = cells(row,column).value where variable is the name of the variable, row is the row number of the cell containing the value, and column is the column number (not letter, you must use a number!) Example: Let’s say that you wanted to read the contents of cell b4 and to store it in the variable r. The line to do this would read: r = cells(4,2).value Functions vs. Subroutines : 33 Functions vs. Subroutines Writing data back to the spreadsheet is just as easy. Let’s say that you have calculated a variable x that you want to write back to cell d8. You would use the following command to do this: cells(8,4).value = x Let’s look at an example of this in an actual spreadsheet (the spreadsheet is named “input-output example.xls” and is on my web-site.) Other Items : 34 Other Items We have not yet gotten into a couple of other key issues, how to control the flow of your program, and how to make loops. We will examine the “flow control.xls” spreadsheet to do this. This spreadsheet has three functions embedded in it: Maxfinder(a,b) Sumcount(x) Fact(x) The purpose of these functions is to demonstrate how to control the flow of your VBA program. Let’s look at each of these functions. Other Items : 35 Other Items Maxfinder(a,b) This function simply returns the greater of a or b. While there is obviously a built-in excel function that can do this, I include it in this spreadsheet because it is the simplest way to illustrate the most basic control structure in programming: the if-then statement. The if-then-else statement is simply a way of telling VBA that if a condition is true to take one set of actions, and if it is not true to take a second set of actions. Generally, the format is: If (condition is true) then code to be executed if condition is true which can be many lines long… Else code to be executed if the condition is not tru which can also be many lines long… Endif Other Items : 36 Other Items Here is the code for the function MaxFinder ' A very simple use of an if-then loop. Function MaxFinder(a, b) Dim temp If a > b Then temp = a Else temp = b End If MaxFinder = temp End Function Another Example : 37 Another Example Finally, there is another example spreadsheet on my web-paged named “Starting Point.xls.” This spreadsheet contains an extremely well-documented subroutine that will read cash flows and an interest rate from the spreadsheet, will calculate the present value of those cash flows, and will return that present value to the spreadsheet. This subroutine uses all of the tools that we have developed in this set of slides.