Selection, Assignment and IF Statements: Compute PI Example 0) Introduction: 1) Selection: 2) Assignment Statements in VB: 3) If Statements in VB: 4) Computing Pi by Throwing Darts (Stepping Through Subroutines Interlude) 5) Summary: 0) Introduction: In this lecture we will learn about how to use the Selection (the cell(s) selected in Excel when the function/subroutine is called) to communicate with VB. We will also examine in detail a VB subroutine that does a calculation similar to the one we did early in the quarter in Excel: calculating an approximation to pi by throwing darts randomly. To write this subroutine (and eventually turn it into a function) we need to learn about the IF statement in VB. In the process, we will discuss Assignment statements (using the = operator, storing a value in a variable) and IF statements. Along with For loop and While loop statements (we will discuss While loops soon) -and an occassional MsgBox statement for debugging- these are the primary statements that we will use in VB programming. 1) Selection: We can use a special variable named Selection to get information from an Excel worksheet into our VB subroutines/functions. Recall that we also already know how to get information from Excel worksheeets into VB by using Cells and Range functions. The simplest information that we can get is the row and column of the selected cell. We can write these in VB as Selection.Row Selection.Column Row and Column are called "properties" of the selection variable. We can also retrieve and update information about the Font of a cell by using the Font property; Selection.Font.Bold is a boolean property indicating whether or not the font is bold-face; Selection.Font.Size is a numeric property indicating the size (in units called "points") of the font in that cell. We can write a simple subroutine to display the values of these four values for any cell selected before we run the subroutine. Sub SelectionInfo() MsgBox "Row = " & Selection.Row & "; Col = " & Selection.Column _ & "; Bold = " & Selection.Font.Bold & ";Size = " & Selection.Font.Size End Sub Note that the MsgBox statement must fit on one line, and we could write it as one very long line. But VB has a mechanism that allows us to WRITE ANY STATEMENT ON MULTIPLE, SHORTER LINES. If we put the underscore character at the end of a line (as we did above), VB considers the next line to be a continuation of the line with the underscore. So VB considers the two lines inside SelectionInfo as one long line. -->In the workbook accompanying this lecture, put some text in a cell, format --> that cell (possibly changing it to bold and/or changing its size, etc.) --> select that cell, and run the SelectionInfo subroutine. We can write a subroutine to toggle the Bold boolean value (changing it from TRUE to FALSE or from FALSE to TRUE) of a slected cell. VB has a Not operator that computes the opposite of a boolean values. We can write the ToggleBold subroutine as follows Sub ToggleBold() Selection.Font.Bold = Not Selection.Font.Bold End Sub So, if Selection.Font.Bold is FALSE, the formula Not Selection.Font.Bold is TRUE, and this value is assigned back into Selection.Font.Bold -->Select some cell that has text in it (see the text above that you used to --> run the SelectionInfo subroutine) and run the ToggleBold subroutine twice --> on it We can also use Selection to copy a value one cell to its right. Study the following subroutine Sub CopyToRight() srow = Selection.Row scol = Selection.Column Cells(srow, scol + 1).Value = Cells(srow, scol).Value End Sub It uses Selection to determine the row and column of the selection (srow and scol) and then uses Cells to store into the cell to its right (same row but a column one bigger) whatever value is in the selected Cell. In fact, we could have simplified the last line to use the Value property of Selection and written it as Cells(srow, scol + 1).Value = Selection.Value We can also write a variant of this method that moves the selected value to the right. Sub MoveToRight() srow = Selection.Row scol = Selection.Column Cells(srow, scol + 1).Value = Selection.Value Cells(srow, scol).Value = "" End Sub We can also select a cell on a worksheet, using Select and Cells (or Range). UseSelection is a subroutine that first selects cell J1 and displays its value with MsgBox. Then it selects each of the cells J1:J5, and after selecting each cell it calls the previously written subroutine CopyToRight (notice the form of this call) to copy each value one to the right. Sub UseSelection() Range("J1").Select MsgBox "Selected Value = " & Selection.Value For i = 1 To 5 Cells(i, 10).Select Call CopyToRight() Next i End Sub -->Enter the values 1 through 5 in J1:J5 and run the UseSelect subroutine; We will now look as assignment statements and If statements in VB in more detail. Then, along with For loop statements, we will write some complicated Visual Basic code that approximates Pi by throwing darts. 2) Assignments Statments in VB: We have seen four kinds of statements in VB: MsgBox, For loop, assignment statement, and calling a subroutine. Note that there are different meanings for the = sign in math and VB. Writing x = 0 in math is asserting that x has the value 0. Writing x = 0 in VB is an imperative command that says to calculate the value on the right of the = sign (here just the value 0, but it could be a more complicated formulat )and put that value into the variable on the left of the = sign (removing any prior value that was stored there). PLEASE REREAD THE PARAGRAPH ABOVE. IT IS CRITICAL TO VB PROGRAMMING. Often we illustrate variables as a box with the name of the variable appearing on top of the box and the value of the variable appearing inside the box. Let's look at a more complicated example. In mathematics writing x = y is asserting that the variables x and y store the same value. Writing x = y in VB is an imperative command that says to calculate the value stored in y and put that value into x. So, x and y have the same value: the value originally in y. In math, writing x = y has the same meaning as y = x. In VB the first form stores the current value of y into x; the second form stores the current value of x into y. So, if we start with x y +---+ +---+ | 0 | | 1 | +---+ +---+ and write x = y, the result is x y +---+ +---+ | 1 | | 1 | +---+ +---+ Again, if we started with x y +---+ +---+ | 0 | | 1 | +---+ +---+ and write y = x (note the difference from the above: y is on the left side of the = sign), the result is x y +---+ +---+ | 0 | | 0 | +---+ +---+ So in any case both x and y store the same values AFTER the assignment statement, BUT which value they store (x's original value or y's original value) depends on which variable is on the left side and which is on the right side. We can put any calculation of the right side of the = sign. Here is another interesting form of the assignment statement x = x + 1 Such a statement is meaningless in mathematics: no variable can be one bigger than itself. But this statement has an important meaning in VB. It says to calculate a number one bigger than the current value in x (the right side), and then to store that new value back into x (the left side): the result is that it increments x by 1. Such a statement is useful for counting how often some interesting event occurs (and we will see it used when calculating pi below, counting how often a random dart is inside the circle). 3) If Statments in VB: Now we will learn about a new kind of statement in VB, the IF statement. It has two standard forms (note the indentation). If boolean-formula Then Compute boolean-formula; if it is TRUE, statment do each statement after Then, up to statment End If, then VB is done with the If .... and it does statements after End If statment End If If boolean-formula Then Compute boolean-formula; if it is TRUE, statment do each statement after Then, up to statment up to ELSE, then VB is done with the If .... and it does statements after End If. statment If boolean-formaul is FALSE, do each Else statement after Else, up to End If, statment VB is done with the If and it does statment statements after End If. .... statment End If We will now use all these statements to ultimately build a function that approximates pi by throwing darts randomly. We will build it up a bit at a time: a good approach to follow when writing anything complicated in VB. 4) Computing Pi by Throwing Darts (Stepping Through Subroutines Interlude): MyPi1 is a subroutine that throws the number of darts specified by the selected cell (use a value like 10 or 20 to start with). Fundamentally it retrieves the value of the Selection and uses it as the upper-bound of a For loop. To simulate throwing a dart, we will start by just putting the text "Dart" into rows in Column A, and then we will check that "Dart" appears the number of times specified by the selection. Sub MyPi1() times = Selection.Value For i = 1 To times Cells(i, 1).Value = "Dart" Next i End Sub -->Put 10 in G1, select this cell, and run the MyPi1 subroutine and ensure --> Dart is written in rows 1-10 of Column A -->Ensure you understand all the statements in this subroutine, and how they --> work together to achieve the desired effect ------------------------------------------------------------------------------ Stepping Through Subroutines: Interlude If we click the Macros icon, we can select a macro name and Run it (top button on the pop-up window named Macro). We can also select the macro and STEP INTO the macro: execute it a line at a time and observe its actions, even without MsgBox statements. To do so, we click the STEP INTO button (beneath Run) on the pop-up window named Macro. We can also click the Visual Basic icon, select a subroutine, and click the "Step Into" button on the Debug toolbar in the VB window. The STEP INTO icon shows an arrow pointing INTO a paragraph of code The STEP OVER icon shows an arrow pointing AFTER a paragraph of code The STEP OUT icon shows an arrow pointing from paragraph OUT of it IF YOU DO NOT SEE THIS TOOLBAR in the Visual Basic window, you can create them (and put them on the Microsoft Visual Basic Toolbar) by clicking View (on that window), then hovering over Toolbars, and finally clicking Debug (which should become checked, along with Standard toolbar, which should already be checked). The Debug toolbar will appear (with 13 icons). Place it under the Standard Toolbar No matter how we start stepping into a subroutine, we can click this Step Into icon on the VB window toolbar to run a subroutine "a line at a time". This icon appears 4th after the righ-pointing green arrow; it looks like an arrow pointing INTO a paragraph. We can also press F8 to step into a function. Finally, we can click the Locals Window on the icon. This icon appears 7th after the righ-pointing green arrow; it looks like an window icon. When we click this icon, a window appears at the bottom (after) the window showing the text of our subroutines. We can move this Window anywhere that it is convenient. When we execute a subroutine, this Locals Window shows the values of all the variables used in the executing subroutine. Even without the Locals Window, ee can also hover over a variable in a subroutine, and VB will show a small pop-up with that variable and its value. ------------------------------------------------------------------------------ -->Enter 10 in cell G1 -->Select cell G1 and repeatedly click the step-into icon and watch the --> subroutine run a line at a time; watch the Locals Window to see how the --> variables times and i update their values; watch how values are put into --> cells in the Excel worksheet -->Notice that the line it is running is outlined in yellow -->Ensure you understand all the statements in this subroutine, and how they --> work together to achieve the desired effect If you want to stop running a subroutine, you can click the "Reset" icon on either the Standard or Debug toolbar in the VB window. This icon appears 2nd after the righ-pointing green arrow on both; it looks like blue square. If VB ever stops in a subroutine and displays a line in yellow, it found an rror on that line. We can correct the error, press reset, and then try running the subroutine again. -->Enter the values 1 through 5 in J1:J5 and single step through the UseSelect --> subroutine; observe what happens when the UseSelect subroutine calls the --> CopyToRight subroutine, and when the CopyToRight finishes MyPi2 extends MyPi1 by generating random x and y coordinates (in the range [-1,1))for each dart that is thrown. It puts those values in columns B and C, next to each "Dart". Sub MyPi2() times = Selection.Value For i = 1 To times Cells(i, 1).Value = "Dart" x = 2 * Rnd - 1 ' Rnd generates a value in [0,1) y = 2 * Rnd - 1 Cells(i, 2).Value = x Cells(i, 3).Value = y Next i End Sub -->Delete all the values in column A; enter 10 in cell G1 -->Select cell G1 and repeatedly click the step-into icon and watch the --> subroutine run a line at a time; watch the Locals Window to see how the --> variables times, i, x, and y update their values; watch how values are put --> into cells in the Excel worksheet -->Check that each x and y coordinate is in the range [-1,1) -->Notice that the line it is running is outlined in yellow -->Ensure you understand all the statements in this subroutine, and how they --> work together to achieve the desired effect -->Click the Reset icon before finishing the subroutine, to stop it MyPi3 extends MyPi2 by using a BOOLEAN formula to determine whether the x,y coordinates of each throw are inside/outside a circle of radius 1. It puts th text "Inside" or "Outside" in column D next to each pair of coordinates. Note the boolean formula (x ^ 2 + y ^ 2) ^ 0.5 <= 1 controls an IF statemement which which decides which other statements inside the If statement are executed. Sub MyPi3() times = Selection.Value For i = 1 To times Cells(i, 1).Value = "Dart" x = 2 * Rnd - 1 y = 2 * Rnd - 1 Cells(i, 2).Value = x Cells(i, 3).Value = y If (x ^ 2 + y ^ 2) ^ 0.5 <= 1 Then Cells(i, 4).Value = "Inside" Else Cells(i, 4).Value = "Outside" End If Next i End Sub -->Delete all the values in columns A, B, and C; enter 10 in cell G1 -->Select cell G1 and repeatedly click the step-into icon and watch the --> subroutine run a line at a time; watch the Locals Window to see how the --> variables times, i, x, and y update their values; watch how values are put --> into cells in the Excel worksheet -->Use a calculator (or Excel cell) to check that a few of these labels are --> calculated correctly -->Notice that the line it is running is outlined in yellow -->Ensure you understand all the statements in this subroutine, and how they --> work together to achieve the desired effect. MyPi4 extends MyPi3 by keep a count of the number of dart throws inside the circle. It initializes inCount to 0 (before the For loop) and it increments this variable (inCount = inCount + 1) for each "Inside" dart. It puts the current value of inCount in Column E after each "Inside"/"Outside", whether or not it has changed. Sub MyPi4() times = Selection.Value inCount = 0 For i = 1 To times Cells(i, 1).Value = "Dart" x = 2 * Rnd - 1 y = 2 * Rnd - 1 Cells(i, 2).Value = x Cells(i, 3).Value = y If (x ^ 2 + y ^ 2) ^ 0.5 <= 1 Then Cells(i, 4).Value = "Inside" inCount = inCount + 1 Else Cells(i, 4).Value = "Outside" End If Cells(i, 5).Value = inCount Next i End Sub -->Delete all the values in columns A, B, C, and D; enter 10 in cell G1 -->Select cell G1 and repeatedly click the step-into icon and watch the --> subroutine run a line at a time; watch the Locals Window to see how the --> variables times, i, x, and y update their values; watch how values are put --> into cells in the Excel worksheet -->Check that inCount is one bigger for each "Inside" but stays the same for --> each "Outside" -->Notice that the line it is running is outlined in yellow. -->Ensure you understand all the statements in this subroutine, and how they --> work together to achieve the desired effect. MyPi5 extends MyPi4 by computing an approximation for Pi and storing it in the cell to the right of the selected cell. It uses the formula 4*inCount/times (the number of darts thrown). I have also "commented-out" all the statements that put values into cells in the worksheet. Now when this subroutine runs, it doesn't put any "intermediate" values into spreadsheet, but just puts the final answer there. Sub MyPi5() times = Selection.Value srow = Selection.Row scol = Selection.Column inCount = 0 For i = 1 To times 'Cells(i, 1).Value = "Dart" x = 2 * Rnd - 1 y = 2 * Rnd - 1 'Cells(i, 2).Value = x 'Cells(i, 3).Value = y If (x ^ 2 + y ^ 2) ^ 0.5 <= 1 Then 'Cells(i, 4).Value = "Inside" inCount = inCount + 1 Else 'Cells(i, 4).Value = "Outside" End If 'Cells(i, 5).Value = incount Next i Cells(srow, scol + 1).Value = 4 * inCount / times End Sub -->Remove all the values from the cells on the worksheet. -->Enter a big number like 100,000 or even a million into G1 -->Select cell G1 and run this subroutine; it might take a few second (to tens --> of seconds) to compute the result. -->Ensure you understand all the statements in this subroutine, and how they --> work together to achieve the desired effect. MyPiF rewrites MyPi5 as a function. The number of darts to throw is now a parameter to the function (and the result is returned by assigning a value to MyPiF. I have removed all the commented-out lines and just left in the VB statements that perfrom the calculation. Function MyPiF(times) inCount = 0 For i = 1 To times x = 2 * Rnd - 1 y = 2 * Rnd - 1 If (x ^ 2 + y ^ 2) ^ 0.5 <= 1 Then inCount = inCount + 1 End If Next i MyPiF = 4 * inCount / times End Function -->Put =MyPiF(100000) into a worksheet cell and see the result of calling --> this function;; it might take a few second (to tens of seconds) to compute --> the result. -->Fill down this formula in the next 9 cells to simulate throwing that many --> darts 9 more times: each result should be similar, but probably not --> identical to the others Finally, The MyPi10 subroutine computes 10 approximations to PI under the selected cell (using that cell as the number of darts to throw in each simulation) listing the Try # and result for each in the column under the selection and the one to its right. Underneath all these it computes the average of the 10 tries. Sub MyPi10() srow = Selection.Row scol = Selection.Column allPIs = 0 For i = 1 To 10 Cells(srow + i, scol).Value = "Try #" & i newPI = MyPiF(Selection.Value) Cells(srow + i, scol + 1).Value = newPI allPIs = allPIs + newPI Next i Cells(srow + 11, scol).Value = "Average" Cells(srow + 11, scol + 1).Value = allPIs / 10 End Sub -->Select a cell that contains the values 1000 -->Step into the MyPi10 function and continually click Step In; observe that --> when it executes line 6, it goes into the MyPiF function, which will go --> around that loop 1,000 times (too much clicking) -->Instead, click the Step Out button and observe that the MyPiF function --> finishes and retrurns to line 7 in the MyPi10 subroutine -->From there, click the Step Over button and observe that when VB executes --> line 6 it does not step IN to the MyPiF function, but executes it --> completely in MyPi10 and keeps executing lines in MyPi10 Thus, we can use the Step In, Step Out, and Step Over command to execute (test and debug) our VB subroutines and fucntions. We could also use Excel to compute the average, simplifying our function as follows (not the removal of newPI and allPIs and the additional call to the WorksheetFunction.Average function, specifying the range it is to operate on via two calles to Cells -without the .Value) Sub MyPi10Alternative() srow = Selection.Row scol = Selection.Column For i = 1 To 10 Cells(srow + i, scol).Value = "Try #" & i Cells(srow + i, scol + 1).Value = MyPiF(Selection.Value) Next i Cells(srow + 11, scol).Value = "Average" Cells(srow + 11, scol + 1).Value = _ WorksheetFunction.Average(Cells(srow + 1, scol + 1), Cells(srow + 10, scol + 1)) 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 how to get the selected value from Excel into VB and how VB can select a cell in Excel. Understand how Assignment statements, If statements, and For loop statements work in VB. Understand how to single step into/out of subroutines and functions (and how to step into and over statements in subroutines and functions) and how to observe the local variables used in a function. This process is critical for testing and correcting functions that you are writing.