Calling subs

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 b ox. To implement this w e 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 b e to calculate the number of nonblank 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.

'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" Else

Rng.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, vblnformation, "Count Cells" End Sub

E3 Microsoft Excel - SALESMAN

_1 File Edit Vfaw Insert Format Lools Data Window Help Type a question for help

E3 Microsoft Excel - SALESMAN

_1 File Edit Vfaw Insert Format Lools Data Window Help Type a question for help

■ä

1

1 1

b

e

F | Ä

1

b

x

3

10

5

3

12

1

. ill

Ti"

Count Cells

Ts"

/IThere are 5 valid :illiiurtth; selection

1b"

Sp

:1e

19

1

20

_

"21

22

Figure 9.1 Output of Listings 9.1 and 9.2

Figure 9.1 Output of Listings 9.1 and 9.2

0 0

Post a comment