Suppose you needed to write code that enabled the user to select a range of cells, and then from the range chosen, output the number of valid cells (i.e.
non-blank cells) using a message box. To implement this we will use two sub procedures. The first will be used to select the range, this will then call the second sub procedure whose purpose will be to calculate the number of non-blank cells in the range. We have already seen a similar example in the previous chapter when we looked at the Step over tool for debugging.
To call a sub, you write its name followed by any arguments that are passed into it. In this example, we are not passing any arguments.
The two subs are getRange and countValidCells (Listings 9.1 and 9.2). The purpose of the getRange procedure is to enable the user to enter a cell range, and the procedure would select the cell range on the worksheet. It does this by using a range variable, Rng, stored from the InputBox method of the Application object. The If Rng Is Nothing Then statement checks to see that the user has selected a range. If not, then an Operation Cancelled message appears, otherwise the selected range is highlighted. The next statement will call the CountValidCells sub. This works by creating two variables: an integer variable to store the number of non-blank cells, and a string variable called strCount to store a string version of this variable so that it can be formatted for use with a MsgBox function. The statement: myCount = Application.CountA(Selection) uses the CountA method for counting all non-blank cells in the range, and the statement: strCount = Str(myCount) converts the integer variable to a string variable for use in the MsgBox.
Listing 9.1 The GetRange sub
'Highlights selected range and then calls a sub called CountValidRange 'which will count non-empty cells in selected range
Sub getRange() Dim Rng As Range
Set Rng = Application.InputBox(prompt:="Enter range", Type:=8) If Rng Is Nothing Then
MsgBox "Operation Cancelled"
ElseRng.Select End If
CountValidCells End Sub
Listing 9.2 The CountValidCells sub Sub countValidCells()
Dim myCount As Integer Dim strCount As String
myCount = Application.CountA(Selection) strCount = Str(myCount)
answerString = "There are " + strCount + " valid cell(s)in this selection"
MsgBox answerString, vbInformation, "Count Cells"
End Sub
Figure 9.1 Output of Listings 9.1 and 9.2
Functions
Any Excel user will be aware of functions such as Sum, Average, Count, and so on. These functions can be used from Excel, just like defining a formula in a cell, i.e., begin with the = sign, then select the function required from the Function list box. These are the built-in functions. The idea behind a function is that you send data into it and the function sends back, or returns, an answer. These examples show how built-in functions are used in Excel:
=Tan (0.7)
The trigonometric Tan function returns the value of Tan(0.7) = 0.842288
=Count(C1:C4)
The Count function returns the number of cells in the range C1:C4 = 4.
varAnswer = Application.WorksheetFunction.Sum(Range("E1:E32"))
This statement will call WorksheetFunction.Sum to sum the range from E1 to E32 and assign the result to varAnswer. In this function, you send in to it the cell range, and return the sum of that range.
varAnswer = Application.WorksheetFunction.Sum(Range("A1", Range("A1").End(xlDown)))
This statement will use WorksheetFunction.Sum to sum the range from E1 to the last active cell in the column and assign the result to varAnswer.
If LCase(Selection.value)= "" then MsgBox "Good"
This statement uses the LCase function within the test and translates the string into lower case letters. This is a very useful function when there is a possibility of the user selecting a non-tested text case. You send it a text string and it returns the same string in lower-case.
In all of the above examples we can see that one or more values, or arguments, are send into the function and a value, the answer, is returned.
These built-in functions can be used with the VBA language and we have already used some of them in previous chapters. We have come across functions that convert data from one type to another such as Val (string) for converting string format to numeric, or Str (number) for converting a numeric into a string representation (see Chapter 5). We have also used functions to manipulate strings. The following example uses more string conversion functions.
The example in Listing 9.3 allows the user to select a number of cells then checks each of them to see if there is a formula. If there is, a message box is displayed giving the address of the cell and its formula. If not, no message box will be displayed. The test to find out if each cell uses a formula is the line that reads:
If Mid(Cell.Formula, 1, 1) = "=" Then
This line uses the built-in string function Mid(). This takes three arguments:
the first looks at the cell formula, the second refers to the position in the string (in this case 1) and the third refers to the number of characters to be extracted (in this case 1). Clearly, if this value is the = character, then there must be a formula in the cell, since every cell containing a formula begins with =.
Listing 9.3 Example of using the built-in String function Mid()
Sub addressFormulasMsgBox() 'Displays the address and formula For Each Cell In Selection
If Mid(Cell.Formula, 1, 1) = "=" Then
MsgBox "The formula in " & Cell.Address(rowAbsolute:=False, _ columnAbsolute:=False) & " is: " & Cell.Formula, vbInformation End If
Next End Sub
Figure 9.2 Screenshot of output from Listing 9.3