Calling a function

A function can b e called as a direct call in Excel, i.e. you use it like any other built-in function, by clicking on the cell where it is to be used and typing '=' then writing its name - in this case - doubleValue followed by the value that you want doubled. (Remember, it has to be an Integer). Figure 9.3 shows how the above function could b e called using the v alue of the argument=12.

E? Microsoft Excel - SALESMAN


[ IfQ -File Edit View . Insert Format Tools Data Window Help - S X □ftS SI BPS) 1 «rial - 10 - B I U K m M * A - »


fx =double^!oe(T2)























ij St ¡I n >Sheetlj£weeWysales / H

W. safest, data /


1 i



Figure 9.3 Calling the function doubleValue() in Excel

Figure 9.3 Calling the function doubleValue() in Excel

This is very similar to the way in which any other Excel function is called, i.e. the function is executed by beginning with the =sign in the active cell.

The function doubleValue() can be called within other sub or function procedures. For example, in the following macro called testDouble(), a call to the function doubleValue is made with the line: MsgBox "the value is: " & doubleValue(37)

Sub testDouble()

MsgBox "the value is: " & doubleValue(37) End Sub

The function doubleValue() can also be used within a cell formula, e.g. =doubleValue(F10)

This example rewrites the calling subs example from pages 170-171, by implementing the called procedure as a function instead of a sub. The procedures are written in Listings 9.4 and 9.5. To translate the called procedure to a function you will see the following changes have been made:

The called procedure countValidCells begins with the word Function, and an End Function statement is used to terminate it (see Listing 9.5). A range parameter has been passed into the function, and we have designed the function such that a string is returned that will contain the number of cells in the valid range (see Listing 9.5).

The function value is returned in the function by the statement: countValidCells = Str(myCount)

The function value is returned to the calling procedure by the statement: thisCount = countValidCells(Rng)

Both the argument passed to the function doubleValue() and the return value was declared as being Integer. If you therefore pass a non-integer value, the result will not be as expected. For example, "=doubleValue(3.4)" will return 6 (the truncated value).

♦ The function value is output in the calling sub procedure using the message box statement:

MsgBox answerString, vblnformation, "Count Cells"

Listing 9.4 Sub GetRange _

Sub getRange()

Dim thisCount As String 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 thisCount = countValidCells(Rng)

answerString = "There are "+ thisCount + " valid cell(s)in this selection" MsgBox answerString, vbInformation, "Count Cells" End Sub

Listing 9.5 Function CountValidCells

Function countValidCells(Rng As Range) As String Dim myCount As Integer Dim strCount As String myCount = Application.CountA(Selection) strCount = Str(myCount) countValidCells = Str(myCount) End Function

0 0

Post a comment