More On Input/Two Examples 0) Introduction: 1) Rectangular Selections: 2) InputBoxes: 3) A Data-Table-Like Subroutine: 4) Generalizing Recorded Macros, A Final Example: 0) Introduction: In this lecture we look at two more ways to get information into VB subroutines and functions. The first is a variant on Selection, the second in the InputBox function, which prompts the user for input. We will also examine a subroutine that acts much like a Data Table and finally we will see a final example of how to record a macro for creating a Scatter chart, and generalize it to create the data first and chart it. 1) Rectangular Selections: Recall that we can use Selection.Row and Selection.Col to find the row and column number of a selection. If we select a range of cells (row, column, or rectangular range), these numbers correspond to the row and column of the upper-left hand cell. We can also find the number of rows and columns in a selection, using Selection.Rows.Count and Selection.Columns.Count (note the plural "Rows" and "Columns"). If we select one cell, both are 1; if we select only a row, then Selection.Rows.Count is 1; if we select only a column, then Selection.Column.Count is 1; if we select a rectangular range, neither is 1. Let's use of knowledge to write a subroutine that fills any selected rectangular region (actually, it will work for pure row and column selections too) with 0s. This task will use two "nested" For loops (one inside the other) to fill in a rectangular range (not just a row or column) with 0s. We will start writing this subroutine so that it just displays the upper-left hand row and column, and a count of the rows and columns in the selection. By first calling this version of the subroutine, we can select various ranges and see that the correct information is retrieved about the range. Sub Fill0Start() srow = Selection.Row scol = Selection.Column rcount = Selection.Rows.Count ccount = Selection.Columns.Count MsgBox "Upper-Left is (" & srow & "," & scol & ") with " & _ rcount & " rows and " & ccount & " columns" End Sub --> On the Selections worksheet, choose different ranges (single cells, pure --> rows, pure columns, rectangular ranges) and run the Fill0Start --> subroutine on them; verify the correct information is displayed by the --> MsgBox. Once we have verified this information is correct, we can use two nested For loops to visit every cell in a range and store 0 in it. The looping structure will be For r = srow To srow + rcount - 1 For c = scol To scol + ccount - 1 Cells(r, c).Value = 0 Next c Next r To be concrete, suppose that we chose the following range 6 7 8 9 +---+---+---+---+ 4 | | | | | +---+---+---+---+ 5 | | | | | +---+---+---+---+ 6 | | | | | +---+---+---+---+ Here srow is 4, scol is 6 (4,6 is the upper-left cell), rcount 3 (3 rows: 4, 5, 6), and ccount is 4 (4 columns: 6, 7, 8, 9). We write an outer For loop for each possible row: we need rows 4, 5, and 6 so the lowest row is srow, and the highest row is srow + rcount - 1 (Generally doing small examples like this can clarify the formulas needed.) For each row, we must examine cells in every column of that row: we need columns 6, 7, 8, and 9 so the lowest column is scol, and the highest column is scol + ccount -1 (the same formula as for rows, but with scol and ccount). Recall that the forumula for how many times a loop will repeat is b - a + 1. In the case of the first/outer loop this is srow+rcount-1 - srow + 1 = rcount. So, when rcount is 4 (as in the example above), r will take on 4 different values: srow to srow + rcount - 1. We can apply this same calculation to the second/inner loop. So, the actual code is as follows, with the MsgBox commented out so that it produces no results. Sub Fill0() arow = Selection.Row acol = Selection.Column rcount = Selection.Rows.Count ccount = Selection.Columns.Count 'MsgBox "(" & arow & "," & acol & ") with " & _ ' rcount & " rows and " & ccount & " columns" For r = arow To arow + rcount - 1 For c = acol To acol + ccount - 1 Cells(r, c).Value = 0 Next c Next r End Sub What follows below is a step-by-step trace of VB executing this code. I suggest that you read it while stepping through the code above. Observe the pattern of values for r and c and when the inner and outer loops iterate. The outer For loop would initialize r to 4; then the inner for loop would initialize c to 6, and then set Cells(4,6).Value to 0. The Next c line would increment c to 7 and again execute the body of the "For c" loop, and set Cells(4,7).Value to 0. The Next c line would increment c to 8 and again execute the body of the "For c" loop, and set Cells(4,8).Value to 0. The Next c line would increment c to 9 and again execute the body of the "For c" loop, and set Cells(4,9).Value to 0. The Next c line would increment c to 10, which is bigger than scol + ccount - 1, so VB is finished with the inner/"For c" loop. The Next r line would increment r to 5 and again execute the body of the outer/"For r" loop, which again executes the "For c" loop. The inner for loop would again initialize c as 6, and then set Cells(5,6).Value to 0. The Next c line would increment c to 7 and again execute the body of the "For c" loop, and set Cells(5,7).Value to 0. The Next c line would increment c to 8 and again execute the body of the "For c" loop, and set Cells(5,8).Value to 0. The Next c line would increment c to 9 and again execute the body of the "For c" loop, and set Cells(5,9).Value to 0. The Next c line would increment c to 10, which is bigger than scol + ccount -1, so VB is finished with "For c" loop again. The Next r line would increment r to 6 and again execute the body of the outer/"For r" loop, which again executes the "For c" loop. The inner for loop would again initialize c as 6, and then set Cells(6,6).Value to 0. The Next c line would increment c to 7 and again execute the body of the "For c" loop, and set Cells(6,7).Value to 0. The Next c line would increment c to 8 and again execute the body of the "For c" loop, and set Cells(6,8).Value to 0. The Next c line would increment c to 9 and again execute the body of the "For c" loop, and set Cells(6,9).Value to 0. The Next c line would increment c to 10, which is bigger than scol + ccount -1, so VB is finished with "For c" loop again. The Next r line would increment r to 7, which is bigger than srow + rcount -1, so VB is finished with the "For r" loop, and finished with the entire subroutine So, for every value that the variable r in the outer loop takes on, the inner loop will iterate through all its values. -->On the Selections worksheet, choose different ranges (single cells, pure --> rows, pure columns, rectangular ranges) and run the Fill0 subroutine on --> them; verify the correct information is displayed by the MsgBox. -->Use the Locals Window to observe the pattern of values for r and c 2) InputBoxes: So far we have learned 3 ways to get data into a function/subroutine for it to operate on 1) from a cell/range (using Cells and Range) 2) from a Selection (for rows, using Selection.Row/Selection.Rows.Count) 3) from parameters (getting their values from arguments when called) The InputBox function is another way. When this function is called, it prompts the user with a pop-up window (like a Message Box), but this one allows the user to enter a number of text. A simple example is writing. x = InputBox("Enter the value of x") Which will pop-up a window with the text "Enter the value of x" and a box in which the user can enter the value of x. The InputBox function evaluates to this value, which is stored in the variable x, so this value can be used throughout the subroutine/function by referring to x. The InputBox function actually allows more arguments that we can supply. For example if we wrote x = InputBox("Enter the value of x", "Heading", "57", 0, 0) VB would pop-up a window with the same text, but the heading on the window would be "Heading"; the box in which the user enters the value will already have 57 in it (the default value), and the window will have its upper-left corner at location 0,0 on the computer screen: 0,0 is the upper-left hand corner of the screening: increasing the first number moves the window to the right; increasing the second number moves the window down). -->Examine and run the InputTest subroutine on the InputTest worksheet -->See how the inputs are put in the cells on the spreadsheet, including a --> formula that is evaluated. 3) A Data-Table-Like Subroutine Now we will examine a subroutine that works similarly to how Data Tables work in Excel (similarly, but not exactly the same). Assume that a selection is made before the subroutine is called, and that the selection is always a 2 column region (with any number of rows). The top row will contain a left cell with some value (used as the INPUT to the function) and a right cell will contain an OUTPUT function (using the value in the left cell). For example, we might select the rectangular region below, which has the required information in the top row (row 17). B C +----------+----------+ 17 | 10 | =A17^2 | +----------+----------+ 18 | 1 | | +----------+----------+ 19 | 2 | | +----------+----------+ 20 | 3 | | +----------+----------+ 21 | 4 | | +----------+----------+ The subroutine will successively copy each value in the first column of rows 18 through 21 into the input cell B17; copying a new value there will update the value of the function in C17; the subroutine will copy each new value calculated in C17 into the second column of the row the input came from. When finished, the cells above show display as follows. B C +----------+----------+ 17 | 10 | =B17^2 | +----------+----------+ 18 | 1 | 1 | +----------+----------+ 19 | 2 | 4 | +----------+----------+ 20 | 3 | 9 | +----------+----------+ 21 | 4 | 16 | +----------+----------+ Here is the subroutine 'Assumes two columns selected: input in ' (Selection.Row, Selection.Column) and output in ' (Selection.Row, Selection.Column + 1) 'Uses all subsequent rows as inputs; puts outputs in each of these rows, ' one column to their right Sub SimplifiedDataTable() rcount = Selection.Rows.Count inputRow = Selection.Row inputCol = Selection.Column outputRow = inputRow outputCol = inputCol + 1 original = Cells(inputRow, inputCol).Value 'Save the value; restore it later For r = inputRow + 1 To inputRow + rcount - 1 Cells(inputRow, inputCol).Value = Cells(r, inputCol).Value Cells(r, outputCol).Value = Cells(outputRow, outputCol).Value Next r Cells(inputRow, inputcol).Value = original 'Restore saved values End Sub Initially, we compute the input row and column, and the output row and column (one to its right) and a count of the number of rows to process. Tecnically we don't need outputRow, since we could always use inputRow, but it is more consistent (and easier to read) to have these two variable names. We then save the value in the input cell into the variable original (and restore it at the very end of this subroutine). The main work is done in a VB loop containing two statements. The For loop iterates through every required row (one past the input row to the last row, so it uses the familiar formula inputRow + rcount = 1). The first statement stores the value for each INPUT (each row in Column B) into cell B17 (the INPUT cell), which causes the value in C17 (the OUTPUT) to recalculate its value; the second statement stores the new value computed in C17 (the OUTPUT) into the cell to the right of cell that the new INPUT just came from. -->In the SimplfiedDataTable worksheet select the rectangular region B17:C21 --> (whose output in cell C17 is the square of its input cell in B17) and --> run the SimplifiedDataTable subroutine -->Delete the values in C18:C21 and Step through this subroutine using the --> Locals Window to observe the operation of this subroutine in more detail -->Repeat both steps after selecting the rectangular region D3:E13 (whose --> output in cell E3 is 2 raised to the power of its input cell in D3) IMPORTANT: Notice that unlike a real Data Table, changing the formula or inputs does NOT automatically recalculate the SimplifiedDataTable. So, we do not yet know enough about VisualBasic to write a real DataTable command, but with what we know we can get close, and get some more insight into DataTable command. We can make this subroutine perform even more like a real Data Table by using an InputBox to prompt the user as to what cell is the input cell to the function (i.e., not assuming that the INPUT cell is to the right of the OUTPUT cell). 'Assumes two columns selected: input cell is prompted for, and output is in ' (Selection.Row, Selection.Column + 1) 'Uses all subsequent rows as inputs; puts outputs in each of these rows, ' one column to their right Sub SimplifiedDataTable2() rcount = Selection.Rows.Count inputRow = Selection.Row inputCol = Selection.Column inputCell = InputBox("Enter Input Cell (e.g., A1)", "Input Cell") outputRow = inputRow outputCol = inputCol + 1 original = Range(inputCell).Value 'Save the value; restore it later For r = inputRow + 1 To inputRow + rcount - 1 Range(inputCell).Value = Cells(r, inputCol).Value Cells(r, outputCol).Value = Cells(outputRow, outputCol).Value Next r Range(inputCell).Value = original 'Restore saved values End Sub -->In the SimplfiedDataTable worksheet select the rectangular region A27:B32 --> (whose output in cell C17 is the square of its input cell in A25) and --> run the SimplifiedDataTable subroutine; when prompted for the input cell --> enter A25 -->Delete the values in B28:B32 and Step through this subroutine using the --> Locals Window to observe the operation of this subroutine in more detail 4) Generalizing Recorded Macros, A Final Example: Finally, let's return to a combination of recording marcos and then generalizing their subroutines by entering VB statements. Suppose that we wanted to generate some number of dart throws (specified via an InputBox) where each dart throw is represented by 2 random numbers between -1 and +1. Then we want to create a Scatter chart for that data, looking at all the places the dart landed. We can accomplish this task in two phases. (1) Record a macro for creating a Scatter chart from some small amount of data that we created (2) Edit the macro to include VB code that generates the dart throws itself, and adapting the recorded macro code to use our data Examine the workbook for this lecture. It contains (in Module2) code that selects the data in A1:B10 and created a Scatter chart of it, using the series name "MySeries", and setting the X and Y axis to span -1 to +1. I then copied this code into a subroutine I wrote named ChartDartThrows. I edited this subroutine to start with the following code: n = InputBox("Enter# of darts to throw") DataRange = "A1:B" & n SeriesName = "Dart Throws" This prompts for a number and stores it in the variable n; it puts in variable DataRange some text specifying the range of data. For example, if the user entered 1000, DataRange would be the string "A1:B1000" (we've seen the & operator before). Finally, it puts in the variable SeriesName the string "Dart Throws". Then this subroutine generates the number of dart throws specified, putting the coordinates in columns A and B. For r = 1 To n Cells(r, 1).Value = 2 * Rnd - 1 Cells(r, 2).Value = 2 * Rnd - 1 Next r Then comes the code from the macro we recorded. I have marked the statements changed by adding the 'Edited comment after these statements. In this code we have subsituted DataRange for the string "A1:B10". For example, the first line in the macro was Range("A1:B10").Select but now it is Range(DataRange).Select We made this same substitution in one other line, changing ActiveChart.SetSourceData Source:=Sheets("DartGraph").Range("A1:B10"), _ PlotBy:=xlColumns to ActiveChart.SetSourceData Source:=Sheets("DartGraph").Range(DataRange), _ PlotBy:=xlColumns Likewise we twice substituted the variable SeriesName for "MySeries". One of these substitutions requires a bit more explanation. The original macro includes the line ActiveChart.SeriesCollection(1).Name = "=""MySeries""" Inside a string, a pair of adjacent "" means to put a quotation mark in the string. For example "John said, ""Hi""" represents the text John said, "Hi" All the text apears between " and whenever "" appears it means NOT TO END the string but to put a " in the string. So the text on the right of the = sign that is ="MySeries" now, we could have just rewritten the string after the = as "=""Dart Throws""" but I wanted to use a variable that stored a string, so I had to rewrite it as "=""" & SeriesName & """" This specifies 3 pieces of text that are catenated together =" the information stored in the variable SeriesName " So the result of the catenation is ="Darts Throws" which is stored in ActiveChart.SeriesCollection(1).Name. I know that this is incredibly strange. This is a spot where the notation used to describe something in a programming language is overly complicated. In any case, once we have made these substitutions, we can run this subroutine. It will prompt us for the number of darts to throw, generate those number of x,y coordinates in columns A and B, then create a Scatter chart (with that number of coordinates and the appropriate x-axis and y-axis) to display the data. -->Run the CharDartThrows subroutine on the DartGraph worksheet -->When prompted, enter 100 for the number of dart throws -->Expand the graph. -->Practice creating these macros yourself -->On a different worksheet, enter values in the range A1:B3 (three x,y --> cordinates for dart throws) and then record a macro of you creating --> a Scatter Chart of this data. -->Create a new subroutine (copy/pasting into its body the contents of --> the first macro) and then make the additions and substitutions shown --> above. The purpose of this example is to show that once you know how your actions translate into VB code, you can create more general and powerful macros.