Introducing Visual Basic 0) Introduction: 1) Subroutines and MsgBox: 2) Cells: 3) Range: 4) For Loops: 5) Summary: 0) Introduction: In this lecture we introduce Visual Basic (VB aka VBA). We introduce it as a programing language in its own right, but mostly as a scripting language for Excel: when Excel records macros, it records them as VB subroutines, which we can modify (typically generalize) by using what we will soon learn about VB. Typically, each line in the macro is a VB statement; learning VB mostly means learning its statements. In this lecture we will learn some fundamentals of VB, including how to write a VB subroutine (which is like an Excel macro), send messages to the user (with the MsgBox statement), how to use Cells/Range/Value to move values from an Excel worksheet to VB and from VB to an Excel worksheet, how to run Excel functions in VB, and how to write For loops to repeat any statements in VB. We start by learning how to edit VB code. For now we will completely write our VB subroutines in the editor, but at the end of this lecture we will see how to edit macros already recorded as VB subroutines. When we click the Macros icon in the Code group on the Developer tab we can click any already existing macro and edit it. We already know how to record a macro, which we can edit later. Now we will learn another way to create VB subroutines/macros. We can create a new "module" in which to write subroutines by clicking the Visual Basic icon in the Code group on the Developer Tab. Excel brings up a new window named Microsoft Visual Basic, whose left pane is named Project-VBA Project and whose right pane is the subrouting/macro code (if any). We can create a new module in which to write code by right-clicking "VBAProject (lecture18start.xlsm)" and then hovering over Insert and clicking Module. At this point it will create a new module # icon under "Modules" and the right pane will allow us to edit subroutines in in that module. If "Option Explicit" appears at the top of the edit window, delete it. -->In lecture18start.xlsm, create a new module following the instructions --> above, which will create Module1 -->It shows the code for Module2, which contains various macros recorded for --> the Life simulation done in a previous lab -->Right-click Module1 and click Remove Module1... to remove it; answer no --> in the pop-up window 1) Subroutines and MsgBox: The first VB statement that we will learn is MsgBox. This allows us to display any text to the user, while the subroutine is running, and wait until the user clicks OK to continue running the subroutine. MsgBox is useful to help us debug our subroutines: it allows us to monitor useful values from inside the subroutine. Note that we can construct text in MsgBox using strings (text in quotes) or values, catenated together with the & operator (just as in Excel). All statements that we want VB to run must be in a subroutine. To write a subroutine named SampleMessage, we first enter Sub SampleMessage() in the VB editor. This starts a subroutine named SampleMessage. When we press return, VB automatically adds closing End Sub In between the start and end of this subroutine, we will add the simplest statement, a MsgBox command, which will appear as Sub SampleMessage() MsgBox "Hello" End Sub This is a complete subroutine, with one statement inside it. -->In lecture18start.xlsm, recreate a new module following the instructions --> above, which will create Module1 -->Remove the Option Explicit at the top of the right pane if it appears -->Type this subroutine and the MsgBox statement inside the right pane, as --> described below (which is a bit different than what appears above) Type the statements INSIDE a subroutine (called the body of the subroutine) in all lower-case letters, indented as shown. VB will convert words that it recognizes in statements to upper case (e.g., it will change msgbox to MsgBox because MsgBox has a built-in meaning to VB). When we type variables (which we will learn about later), it should leave them in lower case. So, if we expect VB to convert a word to upper-case (e.g., MsgBox) and it does not, we might have mispelled the word. If we expect VB to leave a word in lower case (for variables) and it converts them to upper case, we should use a different name for the variable (VB already has a built-in meaning for the variable name). In this way VB helps us recognize built-in names from names we can use. Once we define a subroutine, we can run it using the standard macro mechanism, using the name of the subroutine. -->Click the X (terminate) icon on the upper right of the Microsoft VisualBasic --> window (it will save the updated copy of that window) -->Run the SampleMessage subroutine (by clicking the Macros icon in the Code --> group on the Developer tab, clicking the SampleMessage macro name, and --> clicking run -->Watch Excel display the text from the mesage and click the OK button Now that we have a subroutine, we can edit it either by clicking the "Visual Basic" icon (and double clicking the module it is in) or by clicking the Macros icon, selecting the subroutine, and clicking Edit (in which case Excel automatically goes to the module containing that subroutine). -->Update the body of the subroutine to be the following MsgBox statement --> MsgBox "Hello, answser is "&(3*15/12) -->Excel will reformat this statement as follows, adding spaces around the --> operators: MsgBox "Hello, answser is " & (3 * 15 / 12) -->Click the X (terminate) icon on the upper right of the Microsoft VisualBasic --> window -->Run the new SampleMessage subroutine (by clicking the Macros icon in the --> Code group on the Developer tab, clicking the SampleMessage macro name, --> and clicking run -->Watch Excel display the new text from the mesage and click the OK button Again, note the & operator, which creates one large text value from the smaller text value and the formula. We can write formulas in VB using values, variables and the standard arithmetic operators we know from Excel. The information coming after MsgBox can be a formula that evaluates to text or a number. Finally, as with any macro (or VB subroutine) we can attach it to run when a button is pressed (just as we did with the macros we recorded earlier in the quarter. 2) Cells: The Cells keyword allows our VB subroutine to specify one cell from the worksheet it is running in. We can use Cells BOTH to get a value from the Excel worksheet into a VB subroutine or put a value from the VB subroutine into the Excel worksheet. It is one important way to move information between a worksheet and subroutine. The form of the Cells keyword is Cells(row,column) So, it looks like a function with two arguments: the first specifies a row number (rows are already numbered on each worksheet), the second is a column number (the columns on a worksheet appear as letters; in the Cells function we use 1 for column A, 2 for column B, etc). So to specify worksheet cell E2, we would use Cells(2,5). Most of the time that we use the Cells function, we also specify ".Value" to refer to the value of that cell. We will see how to instruct VB to BOTH examine the value in that cell or put a new value in that cell. We can also use Cell/Value to get a value FROM a worksheet INTO a subtroutine. -->Write the following subroutine below the SampleMessage subroutine in Module1 -->Sub DisplayCell() --> MsgBox "A1 = " & Cells(1, 1).Value -->End Sub Again, type each statement INSIDE the subroutine body in all lower-case letters: msgbox "A1 = "&cells(1,1).value VB will capitalize MsgBox, Cells, and Value and put in some extra spaces. VB will also automatically show a line separating the two subroutines. We can also put in blank lines in front of the new subroutine, to make the separation clearer. -->Terminate the Microsoft VisualBasic window -->Enter a numeric value in cell A1 and run this subroutine -->Re-enter a text value in cell A1 and run this subroutine In each case, the MsgBox statement will display some text that includes "A1 = " followed by the value entered in that cell. A faster way to run the subroutine is by first selecting it in the VB editor: just click the cursor on the start/end of the subroutine, or any statements in its body. Then click the right-pointing green triangle (hover over this icon and it will show "Run Sub/Userform"). It appears under the work Debug. It performs its job by going back into Excel, running the code, and then when we press OK, by returning to the VB editor. When we select a subroutine in this way, its name will appear in the pull-down list at the top right of the VB editor (where the subroutines appear in alphabetical order). We can also use this pull-down list to select a subroutine and then run it. -->Click on a statement in the SampleMsg subroutine and watch that name appear --> in the top-right pull down list -->Click on a statement in the DisplayMsg subroutine and watch that name appear --> in the top-right pull down list -->Click the right-pointing green triangle and watch Excel run the DisplayMsg --> subroutine We can also use Cell/Value to store a value FROM a subroutine INTO a cell in a worksheet. -->Write the following subroutine below the DisplayCell subroutine -->Sub StoreCell() --> Cells(2, 1).Value = 0 -->End Sub Run this subroutine using the right-pointing green arrow. Examine the worksheet (click X on the outermost window) and notice that it now has a 0 as the value of Cell(2,1) which corresponds to cell A2. -->Change the statement in the body of the subroutine to --> Cells(2, 1).Value = "Hello" -->and rerun the subroutine. Note the 0 is replaced in A2 by the text Hello. -->Change the statement back to its original form and rerun it, storing 0 back --> into cell A2 Now, we will put together these two uses of Cell/Value to write a subroutine that copies a value from worksheet cell A1 into cell B1. -->Write the following subroutine below the StoreCell subroutine -->Write it exactly as is: spaces are important! -->Sub CopyCell() --> MsgBox "Copying " & Cells(1, 1).Value & " from A1 to B1" --> Cells(1, 2).Value = Cells(1, 1).Value --> MsgBox "Copying Done" -->End Sub This subroutine has three statements in it: the first is a MsgBox to display some information (catenating text, a value, and more text), the second takes whatever value is stored in cell A1 and copies it into cell B1, the third is a MsgBox to display an acknowledgement; you can see the new value in B1. Run this subroutine using the right-pointing green arrow. -->Retype the second statement in the subroutine without spaces around &, as --> MsgBox "Copying " &Cells(1,1).Value&" from A1 to B1" -->press Enter or select another line with the cursor. You will see see that VB indicates that this statement has a syntax error, because it appears in red and Excel beeps. In this case we can fix the error by putting a space between .Value and &. We can use named variables to store values, and then use those variables in later statements in a VB subrouting. By using variables, we can simplify our code. We illustrate one simple use of variables by writing a variant of the CopyCell subroutine -->Write the following subroutine below the CopyCell subroutine -->Sub CopyCell2() --> v = Cells(1, 1).Value --> MsgBox "Copying " & v & " from A1 to B1" --> Cells(1, 2).Value = v --> MsgBox "Copying Done" -->End Sub The variable v (we could have used any name here) exists inside the CopyCell2 VB subroutine only (it is NOT a name defined in Excel). We store into it a value from a worksheet cell, and then use the variable name in the MsgBox and final statement, to store that variable into a different worksheet cell. Run this subroutine using the right-pointing green arrow. We will learn how to do more complicated computations in VB, but now we know how to do two very important operations: get information into subroutines from a worksheet cell and put information from subroutines into a worksheet cell. 3) Range: The Range function is similar to the Cells function: we can use Range to specify one OR MORE cells, using text that specifies cells as we do in Excel. For example, we can specify Range("A1") or Range("A1:A10"). We often use .Value with the Range function, but sometimes we do not. -->Write the following subroutine below the CopyCell2 subroutine -->Sub CopyCell3() --> v = Range("A1").Value --> MsgBox "Copying " & v & " from A1 to B1" --> Range("B1").Value = v --> MsgBox "Copying Done" -->End Sub Here we use Range just like Cells (with .Value) to specify which cell to get the value from and which cell to put the value into. So, even a single cell can be considered a range, and we can write ranges using letters and number to specify the column and row of a cell. Run this subroutine using the right-pointing green arrow. We can use Range (without .Value) to specify a cell range to standard Excel function like Sum (for adding up all the cells in a range). -->Write the following subroutine below the CopyCell3 subroutine -->Sub AddRange() --> Range("A5").Value = WorksheetFunction.Sum(Range("C1:C5")) -->End Sub Here, WorksheetFunction.Sum denotes the Excel SUM function. We apply this function on a range (not using .Value), so that it addds all the values in the range C1:C5 and puts the result in cell A5 (note that .Value is used with A5). Put 5 values in the range C1:C5 and then run this subroutine using the right-pointing green arrow. Finally, remove the last ) in the last statement in the AddRange subroutine and press Enter. Notice again the syntax error indicated by VB; restore the last ). 4) For Loops: A For loop is a statement that repeats the statements inside its body some number of times, specified by the lower- and upper-bound (inclusive) of the For loop. It uses an "index-variable" to keep track of which loop iteration it is on: we can use the value of the index variable inside the loop's body (often it will specify the row or column of a cell that we want to process in a Cells function: this can get complicated, so we will start with a simpler example). The general form of simple For loop is For index-variable-name = lower-bound To upper-bound statements in the body of the For loop: can use index-variable Next index-variable-name for example Sub ForLoopDemo1() For i = 1 To 5 MsgBox "Inside For loop: i is now " & i Next i MsgBox "For loop is finished" End Sub As with subroutines themselves, we ident the statement(s) in the body of a For loop, putting the closing Next at the same indentation as the For loop. When the For loop starts, it (1) stores lower-bound into index-variable (here, stores 1 into i) (2) runs the body of the loop (here, displays Inside For loop: i is now 1) (3) reaches Next i and puts the next value into i (here, stores 2 into i) because i (here 2) is <= than the upper-bound (here 5) it loops again (4) runs the body of the loop (here, displays Inside For loop: i is now 2) (5) reaches Next i and puts the next value into i (here, stores 3 into i) because i (here 3) is <= than the upper-bound (here 5) it loops again (6) runs the body of the loop (here, displays Inside For loop: i is now 3) (7) reaches Next i and puts the next value into i (here, stores 4 into i) because i (here 4) is <= than the upper-bound (here 5) it loops again (8) runs the body of the loop (here, displays Inside For loop: i is now 4) (9) reaches Next i and puts the next value into i (here, stores 5 into i) because i (here 5) is <= than the upper-bound (here 5) it loops again (10) runs the body of the loop (here, displays Inside For loop: i is now 5) (11) reaches Next i and puts the next value into i (here, stores 6 into i) because i (here 6) is NOT <= than the upper-bound (here 5) it stops the loop and goes to the statement after the loop (12) goes to the statement following the For loop and displays For loop is finished -->Write the subroutine shown above the AddRange subroutine -->Run this subroutine -->Try changing the lower-bound and/or upper-bound and rerun the subroutine Note that we can use MsgBox to help us understand what is happening inside the loop, by printing information about the values stored int he variables. We can generalize such a loop by getting the upper-bound from a cell in the spreadsheet: storing it in a variable and using that variable as the upper-bound. Generally, the lower- and upper-bound can be any numeric formula. -->Write the following subroutine below the ForLoopDemo1 subroutine -->Sub ForLoopDemo2() --> ub = Range("C3").Value --> For i = 1 To ub --> MsgBox "Inisde For loop: i is now " & i --> Next i --> MsgBox "For loop is finished" -->End Sub Enter a small value into cell C3 and run this subroutine. Enter a new value into C3 and run this subroutine again. As mentioned above, we often use the index variable to specify the row or column number of a cell inside a worksheet: a different one each time the loop does the statement in its body. The following subroutine fills E1, E2, E3, ... with the values 1, 2, 3, etc. How many cells does it fill? Whatever number is in worksheet cell D1. So, if we put 20 in D1, Excel fills E1:E20 with the numbers 1-20 (E is column 5). -->Write the following subroutine below the ForLoopDemo2 subroutine -->Sub ForLoopFill() --> ub = Range("D1").Value --> For i = 1 To ub --> Cells(i, 5).Value = i '5 specifies row i in column E --> Next i -->End Sub Note use of ' to start comment; VB ignore everything on a line after a '. Sometimes it is useful to us (when we are writing the subroutine) to include information in the subroutine in the form of a comment that we can read but VB ignores. Finally, we can use the index-variable with the Range function to achieve the same effect (using the & operator). Here is that variant of the ForLoopFill subroutine. -->Write the following subroutine below the ForLoopFill subroutine -->Sub ForLoopFill2() --> ub = Range("D1").Value --> For i = 1 To ub --> Range("E" & i).Value = i 'for example, "E"&1 is "E1" --> Next i -->End Sub Again, by catenating "E" & i, each time in the body of the loop i will have a different value, so VB will be specifying a different range: "E1", "E2", ... each time through the loop. In fact, we don't even need to use the "ub" variable. We could have written the following subroutine, which does the same thing, without "ub" as follows. Sub ForLoopFill2() For i = 1 To Range("D1").Value Range("E" & i).Value = i 'for example, "E"&1 is "E1" Next i End Sub We will use this form in the following section. A Real Example: Multi-Generation Life Finally, recall that in the Life part of Assignment 6, we recorded a macro to advance the matrix by 1 generation. We created the "Update" button to run this macro. Suppose that we want to be able to specify how many generations to advance by one click of the "Update" button (recall the glider moved and reassembled itself every 4 iterations, and it was clumsy to click "Update" so many times). We can solve this problem by generalizing the macro we recorded: by putting all its statements INSIDE a For loop, using some upper bound specified by another cell on the worksheet. Let's assume that we will use cell S8 in the Life worksheet to store a number that specifies how many generations we update for each click of the "Update" button. We can take the body of the LifeUpdate macro and put it into the following For loop. For i = 1 To Range("S8").Value ...put all the statements from the original macro here Next i VB runs the loop index-variable from 1 through whatever value is stored in cell S8. It runs all the statements inside the For loop (originally recorded as a macro) each time the loop body runs. None of the statements inside the For loop refer to the index-variable: they just update Life to the next generation. -->Edit the LifeUpdate macro as described above and put 4 in cell S8 -->Click the Update button and watch the glider reassemble itself in new --> position. IMPORTANT: the square, two to the left of the right green triangle, resets an incorrect execution of a macro. If you are having problems executing the macro, click this square, correct the macro, and start over. We will explore "fixing errors" more in future lectures. 5) Summary: Here is a quick summary of skills to acquire from working on this lecture. You should be able to discuss each of these topics a bit, but more importantly know HOW TO DO something in Excel. Understand the MsgBox statement and be able to use it to annotate subroutines as they execute so that we can better understand them. Be able to use the Cells and Range functions (along with .Value) to copy information from a worksheet to the subroutine or from a subroutine to the worksheet. Understand how to write a For loop and understand how it works.