Preparation for Lab Workshop #13



Lab Workshop #8Purpose:SYMBOL 183 \f "Symbol" \s 10 \hdevelop VBA procedures to manipulate ranges of cells on the spreadsheetSYMBOL 183 \f "Symbol" \s 10 \hlearn different ways to run a VBA Sub from ExcelSYMBOL 183 \f "Symbol" \s 10 \hwrite a series of short procedures to manipulate arrays in VBASYMBOL 183 \f "Symbol" \s 10 \hcreate an array function that returns more than a single value to the spreadsheet1.Launch Excel 2007 and go through the typical setup procedure for VBA program development:4143375112395display the Developer tabset Macro security to enable all macrosset Require Variable Declaration in the Visual Basic Editor2.Enter the following numbers as shown on the spreadsheet to the right. The first VBA procedure you will develop has the purpose of condensing the range of cells with entries to remove the blank cells. This is a very common task, and one that comes up again and again as a candidate for a VBA procedure.First, to preserve your original entries, copy cells B2:B11 to D2:D11 and select the latter range of cells.Launch the VBE ( Alt-F11 ) and insert a module into the project associated with your Excel workbook.Write and test the following short VBA Sub.This demonstrates how you can determine from VBA how many rows are in an arbitrary selection of cells. It is useful for VBA code that must operate on a selection that might change from one execution to the next.Next, we need to know the code required to delete a cell and shift the cells below it upward. The easiest way to do this is to record a macro while performing the operation on the spreadsheet. Enter an arbitrary number in cell F2. Select that cell. Record a macro for the following procedureright-clickdeleteshift cells upOKExamine the VBA code created (in a separate module). Write in below what is the single VBA statement that accomplishes the delete._______________________________________________________________________________Return to the first module and modify the Sub as shown below.In the space below, draw a flowchart to represent the code above (insert tab, shapes->flowchart).<< space on worksheet >>Single-step the Sub with F8. Keep your eye on the value of the RowCount variable. Also, check the status of the spreadsheet frequently with Alt-F11.Note the statementWhen a cell is deleted and the ones below it are shifted up, then the original range of cells is shrunk by one. That implies then that the number of rows that need to be searched is reduced by one.Continue the execution until the Sub is complete.Return to the spreadsheet and observe the result. Then copy the cells B2:B11 to D2:D11 again and select cells D2:D11.3.Running a Sub (macro) from Excel540067589535A simple way to run a Sub from Excel is from the Macro menu. This can be done by clicking the Macros button on the Developer tab of the ribbon or via the shortcut key combination Alt-F8. Try both to see that you can access the Macro window. The Condense macro should appear there.With D2:D11 selected, click the Run button and the macro should run. Copy the cells B2:B11 to D2:D11 again. 540067531750Another, fancier way to set up for running a macro is to place a custom button on the worksheet. To do this, click the Insert button on the Developer tab. Click on the Button image in the Form Controls group. Move the mouse cursor to an empty region of the spreadsheet. It should appear as a plus. Drag out a button of reasonable size. An Assign Macro window should appear (it may cover up the button for now). Select the Customize macro from the list, as shown to the right. Click OK at the bottom of the window and the Condense macro has been linked to the button. Next, "scrub across" the button with your mouse and change the name on the button to "Condense." Click away from the button, and it should appear likeMake sure that D2:D11 are selected and these cells contain the same pattern as B2:B11. Then click the Condense button and the macro should run. Pretty cool!Let's try another way by adding a command button to the QuickLaunch toolbar. Click the Office Button, Excel Options and Customize. You should see357187516510In the Choose commands from: field, drop the list down and select Macros.When the Macros list appears, click on the Condense item, and then the Add button to the right.228600153670Click OK at the bottom of the window. A small button appears at the right of the toolbar at the upper left of the display.The new "Condense" command button is at the right.If you let your mouse hover on the button a "Condense" tooltip should appear. Very nice. Set the D2:D11 range up for the macro again and click the command button on the QuickLaunch toolbar.So, you might consider what's the difference between a button on the spreadsheet and one on the QuickLaunch toolbar. Consider the following:1)The button on the spreadsheet goes out of view when you move to another region of thespreadsheet; whereas the QuickLaunch command button is always visible.2)The Condense button on the QuickLaunch toolbar does not go away when the current workbookfile is closed. And, if you attempt to click the button when this workbook is closed, you will getan error message.These observations lead to the following strategy:Use buttons on the worksheet for macros that are particular to this workbook.3800475215900Use command buttons on the QuickLaunch toolbar for generic macros that are stored in the Personal Macro workbook.So, how to remove that button from the QuickLaunch toolbar. Go back through the Office Button, Excel Options and Customize. Select the Condense item on the right and click Remove. Then click OK.The command button should no longer appear on the QuickLaunch toolbar.Save your workbook at Lab08a.xlsm and close it out.4.Open a new workbook and enter the numbers as shown below.Since Excel is used frequently to manage numbers in rectangular arrays like this, it is useful to know how to access and manipulate arrays in VBA too. There are two ways to access or transfer an array of values to VBA:as an argument to a Functionby using a Range object in either a Sub or a FunctionFirst, you will develop a Function that returns the maximum value of in an array of cells which is the argument to the Function. Switch over to VBA and insert a new module. Enter the following code:Option ExplicitOption Base 1Function MaxVal(DataArray)Dim NumRows As Integer, NumCols As IntegerDim i As Integer, j As Integer MaxVal = DataArray(1, 1) NumRows = DataArray.Rows.Count NumCols = DataArray.Columns.Count For i = 1 To NumRows For j = 1 To NumCols If DataArray(i, j) > MaxVal Then MaxVal = DataArray(i, j) End If Next j Next iEnd FunctionReturn to the spreadsheet, and, in cell D7, enter a formula to test the MaxVal function:=MaxVal(A1:F4)What result is displayed? _______Now look back at the VBA code and study the following observations:a)The Option Base 1 directive is used prior to the Function statement so that array subscripts will start with 1 and not with 0.b)The function result, MaxVal, is first set to the 1,1 element of the input array. This is the element in the upper left-hand corner, in the first row and the first column.c)The Count property is used to determine the number of rows and columns in the input array, and the local variables NumRows and NumCols are used to store these values.d)Two For...Next loops, one nested inside the other, range through all the possible combinations of row and column index values.e)The If statement checks to see whether an element of the array is greater than the current value of MaxVal. If so, it sets MaxVal to this element.f) The loops then continue checking values in the array until all are checked.Create a second function below MaxVal called MinVal that determines the minimum value in the input array. Test the function by entering this formula in cell D8:=MinVal(A1:F4)What is displayed there? _______Now, create the function ArrayRange shown below that makes use of the two functions, MaxVal and MinVal, that you have already created.Function ArrayRange(DataArray)ArrayRange = MaxVal(DataArray) - MinVal(DataArray)End FunctionEnter this formula in cell D9 back on the spreadsheet:=ArrayRange(A1:F4)What value is displayed there? _________If you are executing a Sub directly from Excel, there are no input arguments [it looks like Sub Name()]. You must then use another method to transfer a set of cells into an array in VBA. Add the code below to your module to illustrate one way to do this.Sub ArrayCalcs()Dim DataArray As ObjectAn assignment of an objectSet DataArray = Range("A1:F4")requires the Set keyword.MsgBox ArrayRange(DataArray)End SubRun this Sub and see that the message box displays the correct result. Save your workbook at Lab08b.xlsm and close it out.5.It is possible for a function to return more than one value to the spreadsheet. This type of function is called an array function. You will now create a simple example of an array function. Open a new workbook and enter the numbers as shown below:Let's say we wanted to have a function called PctVal that would display the proportion in % of each of these numbers to the total of the numbers. This function would have to display its results in 4 cells.Switch over to VBA, insert a new module, and enter the function shown below:Option ExplicitOption Base 1Function PctVal(ValueArray)Dim NumRows As Integer, i As IntegerDim Pct() As SingleDim Sum As SingleNumRows = ValueArray.Rows.CountReDim Pct(NumRows)For i = 1 To NumRows Sum = Sum + ValueArray(i)Next iFor i = 1 To NumRows Pct(i) = ValueArray(i) / Sum * 100Next iPctVal = Application.WorksheetFunction.Transpose(Array(Pct))End FunctionReturn to the spreadsheet and select the cells B1:B4, and enter the formula as shown in the figure below.Since this is an array formula, you must enter it with Ctrl-Shift-Enter. The column B cells should then show the percentage proportions of the values in the A column. Adjust the display to 1 decimal place. What four values are displayed?________________________________Change one of the values in cells A1:A4 and note that the function updates automatically.Now, a few comments about the Function PctVal code:a)The Dim Pct() As Single statement sets up a local variable as an array, but without specifying the dimensions of the array yet. This is called a "dynamic" array. Later in the code, after the number of rows in the input array has been determined (NumRows), the Pct array is dimensioned to that number using the ReDim statement.b)The sum of the elements in the input array is accumulated in the Sum variable using a For...Next loop. This sum is needed for the following For...Next loop where the percentages are computed.c)Finally, the percentages are formed into a "variant array" that can be assigned to the name of the function, PctVal. The Array function is used for this, but, if only the Array function were used, the result would appear in a row of cells. Since we want the result to appear in a column of cells, we use Excel's Transpose function to reorder the row into a column.Save this workbook as Lab08c.xlsm and close it out.Leave Excel and return to WindowsLog out.End of Lab Workshop #8 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download