Chapter 24 Flashcards

Question

How to invoke Excel worksheet functions

Answer 1

Some, but not all, of the functions that you can use in a worksheet are available for use in VBA. E.g., you can use functions such as LEFT, RIGHT, or MID, in Excel VBA and Excel VBA will understand you are referring to string functions. However, some other functions such as Vlookup, are not directly usable in VBA. To use (or invoke) these functions, you need to prefix these functions with the words APPLICATION.WORKSHEETFUNCTION. This tells VBA that you are invoking (or using, or calling) a function that is normally used in a worksheet. Unfortunately, not all worksheet functions are available for use in VBA (even by using the APPLICATION.WORKSHEETFUNCTION prefix). Luckily, we do not have to remember which functions are available and which are not. When you are in the Visual Basic Editor (VBE) and type APPLICATION.WORKSHEETFUNCTION. (that is, application period worksheetfunction period) followed by the worksheet function. Excel VBA helpfully provides a list of available worksheet functions.

Answer 2

- Active cell is the cell in which the cursor presently is. (In VBA code, the active cell refers to the cell which will be read, or where a value will be placed) - Active worksheet is the worksheet presently being processed, or referred to - Active workbook is the current workbook (when you have more than one workbook open, active workbook helps select the workbook where you want actions to be completed)

Answer 3

- Selecting a cell: You can select a cell with the code: Cells(RowNumber, ColumnNumber).select RowNumber and ColumnNumber supply two arguments for the Cells() function. You do not have to call the two arguments 'RowNumber' and 'ColumnNumber'. You can call the two arguments almost anything you want. Note that the first argument for the Cells() function is always the row number and the second argument is always the column number. - Selecting a row: You can select a row with the code: Cells(SpecificRowNumber, AnyColumnNumber).entirerow.select Here the row number is important as you are selecting the entire row. The column number does not play an important role. You can also select a row with the code: Range("A1").EntireRow.Select Here, row 1 will be selected. - Selecting a column: You can select a column with the code Cells(AnyRowNumber, SpecificColumnnumber).entirecolumn.select Here the column number is important as you are selecting the entire column. The row number does not play an important role. You can also select a column with the code: Range("A1").EntireColumn.Select Here column A will be selected. -Selecting a range: You can select a range in different ways. Remember that for a range, one end of the range is the upper left cell and the other end is the lower right cell in a rectangular block. Both the upper left cell of the range and the lower right cell of the range, are determined by their respective row and column numbers. Using the cells approach, you can select a range in one of the following ways: Range(Cells(2,3), Cells(10,4)).Select Range("C2:D10").Select

Answer 4

When you are in the Integrated Development Environment, you can create a module by clicking on the Insert menu and then choosing Module. If you have recorded a macro and stored it in that workbook, you will see that it already has a module. A module can have more than one macro (also known as procedure) in it.

Answer 5

``` How to use IF condition in visual Basic for Applications. In any worksheet type the number 100 into cell A1. Then copy and paste the following code in a module. Sub Grading() GradePoints = ActiveSheet.Range("A1").Value If GradePoints > 93 Then LetterGrade = "A" ElseIf GradePoints > 83 Then LetterGrade = "B" ElseIf GradePoints > 73 Then LetterGrade = "C" Else LetterGrade = "F" End If ActiveSheet.Range("B1").Value = LetterGrade End Sub ``` If you change the number 100 in cell A1 to 50, the letter grade in cell B1 will change from A to F. The first line (after the name of the macro) takes the value in cell A1 in the active sheet and stores it in a variable called GradePoints. The IF condition starts with the word IF, and ends with ENDIF. It is not necessary to have ELSEIF as a part of your IF condition. ELSEIF is useful if you have multiple possible values in the IF condition.

Answer 6

Visual Basic for Applications is very useful if you want to do a repetitive task. Let us say that you want to put a number starting from 1 to 100 in 100 cells, and that you want to color even numbers red and odd numbers green. Here is the code to do this. Sub OddEvenColor() For rownumber = 1 To 100 Cells(rownumber, 1).Value = rownumber If Application.WorksheetFunction.IsEven(rownumber) Then Cells(rownumber, 1).Interior.Color = vbRed Else Cells(rownumber, 1).Interior.Color = vbGreen End If Next End Sub The first line (after the name of the macro) starts a counter from 1 to 100. Just as we started the IF condition with IF and finished it with END IF, a FOR loop starts with FOR and ends with NEXT. Here the loop assigns the value 1 to a variable. The variable is 'rownumber'. Each time the program sees NEXT it increments the variable 'rownumber' by 1 until it completes 100. The line Cells (rownumber, 1).Value = rownumber assigns the value represented by 'rownumber' to a cell. The reserved word 'cells' has two arguments: the first number represents the row, and the second number represents the column. You can refer to any cell on the active sheet by referring to that cell's row and column. (If you do not specify which worksheet, it is assumed that the cells are in the current worksheet in the current workbook.) The next line If Application.WorksheetFunction.IsEven (rownumber) then checks if the rownumber is even. To do this, we use the function Is Even (). The function IsEven() takes one argument, a number. If the number is even, the function returns the value 'TRUE'. We read this as saying it is true that rownumber is even. The function IsEven() is available for use in a worksheet. To let Excel know that we are using a function that is available in a worksheet we write the code as: Application.WorksheetFunction.IsEven(rownumber). If the cell contains an even number then we want to color the cell red. We take the cell object and refer to its interior property. (This distinguishes it from the cell's border property. So we can use different commands to color the border differently). We then assign the color red to the interior of the cell. Colors in VBA can be represented by various numbers that are difficult to remember. For convenience, Excel provides some values that are called constants. vbRed, vbGreen, vbYellow etc., are constants.

Answer 7

This loop is useful to address all objects in a collection. For instance, you want to name each worksheet in your workbook JohnDoeSheet1, JohnDoeSheet2 and so forth. You can do this as follows: Sub NameSheetJohnDoe () For Each wsSheet In ActiveWorkbook.Worksheets wsCount = wsCount + 1 wsSheet.Name = "JohnDoeSheet" & wscount Next wsSheet End Sub The first line (after the name of the procedure, or macro) refers to each object in the collection. Here the collection is all the worksheets in the activeworkbook. (Activeworkbook is the workbook you are currently in. You could have other workbooks open, but these will not be affected). wsSheet is the variable name to represent each object in the collection. The line wsCount = wsCount + 1 is used to increment the variable wscount by one each time it is encountered. The line wsSheet.Name refers to the Name property of the object wsSheet. The line Next wsSheet closes the loop and the code repeats from the line For Each until each object in the collection has been addressed. If you wanted to name all worksheets in all open workbooks, you can set up a loop inside another loop. Sub NameSheetJohnDoe() For Each wbook In Application.Workbooks wbook.Activate For Each ws In ActiveWorkbook.Worksheets wscount = wscount + 1 ws.Name = "JohnDoeSheet" & wscount Next ws Next wbook End Sub The first line (after the procedure name) refers to the collection of workbook objects in the Excel application. One by one, the code 'activates' each workbook object in the collection so that that workbook becomes the ActiveWorkbook. It then names each sheet in the active workbook.

Answer 8

Suppose you want to do an action while some condition is true, and you want to continue doing this action until that action becomes false. For such an action, you will use a Do While loop. In the following example, we want to toss three coins and see if all three coins turn up heads. We will keep tossing three coins at a time, until such time we get three heads. We want to determine the number of tosses it takes before we get three heads together. We will use the following code. Sub ThreeHeadsTogether() ThreeHeads = False Do While ThreeHeads = False CoinTossNumber = CoinTossNumber + 1 'we will assume that if Coin1 has a value of 1 it is heads, otherwise tails Coin1 = Application.WorksheetFunction.RandBetween(1, 2) Coin2 = Application.WorksheetFunction.RandBetween(1, 2) Coin3 = Application.WorksheetFunction.RandBetween(1, 2) If Coin1 = 1 And Coin2 = 1 And Coin3 = 1 Then ThreeHeads = True End If Loop MsgBox CoinTossNumber End Sub To start, we declare a variable ThreeHeads to have a value of False. Then, the next line Do While ThreeHeads = False starts the loop that will be repeated as long as the variable ThreeHeads as a value False. To keep track of how many times we are tossing the coins, we have a variable CoinTossNumber that is incremented each time we toss the coins. We then toss three coins, Coin1, Coin2, and Coin3. We will generate a random number between 1 and 2. That is each time we generate the random number, we get either 1 or 2. We will also assume that a 1 represents heads and 2 represents tails. To generate a random number we will use the worksheet function RandBetween(1,2). The RandBetween(x,y) function takes two arguments: the first provides the lower boundary; the second provides the upper boundary. Since the RandBetween function is a worksheet function, we have to indicate this by writing Application.WorksheetFunction.RandBetween(1,2). The random value generated is stored in the three variables Coin1, Coin2, and Coin3. The IF THEN condition evaluates if all three coins are showing heads. If they are, then the variable ThreeHeads takes the value TRUE. The Loop then goes back to the Do While ThreeHeads=False line. However, ThreeHeads now has the value True, so the loop does not continue. The line MsgBox CoinTossNumber shows the values of the CoinTossNumber which reflects how many times we tossed the three coins.

Answer 9

``` The Select Case code begins with Select Case and ends with End Select. It works similar to the IF-THEN-END IF construct. The procedure that we wrote earlier called Grading is repeated below but this time using Select Case. Sub Grading() GradePoints = ActiveSheet.Range("A1").Value Select Case GradePoints Case Is > 93 LetterGrade = "A" Case Is > 83 LetterGrade = "B" Case Is > 73 LetterGrade = "C" Case Else LetterGrade = "F" End Select ActiveSheet.Range("B1").Value = LetterGrade End Sub ```

Answer 10

You usually store code in a module. The module can reside in the current workbook or the Personal Macro Workbook. Whenever you start Excel, the Personal Macro Workbook opens automatically. Any macro that is stored in the Personal Macro Workbook is then available for you. For instance, if you have a macro that expands a column or increases the height of a row, and store this macro in the Personal Macro Workbook, then you can invoke (or use, or call) the macro from any worksheet that you are working in. Excel stores the Personal Macro Workbook in a folder called XLSTART. Any file that is stored in XLSTART is opened automatically each time that you open Excel. You may find it convenient to keep the Personal Macro Workbook hidden so that you have the macros in that workbook available for use, but it does not get in the way of your work.

Answer 11

c. A module

Answer 12

b. If Then

Answer 13

c. VBA is used within an application

Chapter 24 Flashcards

(39 cards)