Microsoft Office Tutorials and References
In Depth Information
Example 6-2. Assigning a Function's Return Value
In order to return a value from a function, we must assign the function's name to the return value
somewhere within the body of the function. Example 6-2 shows a slightly more complicated
example of a function.
Example 6-2. Assigning a Function's Return Value
Function ReturnCount() As Variant
' Return count of cells in current selection
If TypeName(Selection) = "Range" Then
ReturnCount = Selection.Count
ReturnCount = "Not applicable"
End If
End Function
This function returns a count of the number of cells in the current selection, provided that the
selection is a range of cells. If the selection is another type of object (such as a chart), the function
returns the words "Not applicable." Note that since the return value may be a number or a string,
we declare the return type as Variant. Note also that ReturnCount is assigned twice within the
body of the function. Its value, and hence the value of the function, is set differently depending
upon the value returned by the TypeName(Selection) function. Since these assignments are
mutually exclusive, only one of them will occur each time the function is called.
Because functions return values, you can't call them directly from the Macro dialog that appears
when you select Tools Macro Macros, nor can you assign them to an Excel toolbar or
menu through Excel's user interface. If you want to be able to call a function, you'll have to "wrap"
it in—that is, have it called by—a subroutine, the topic that we'll cover next.
6.2 Calling Subroutines
A subroutine declaration has the form:
[Public or Private] Sub SubroutineName Param1 As DataType1 , _ (
Param2 As DataType2 ,...)
This is similar to the function declaration, with the notable absence of the As ReturnType
portion. (Note also the word Sub in place of Function .)
Since subroutines do not return a value, they cannot be used within an expression. To call a
subroutine named SubroutineA , we can write either:
Call SubroutineA(parameters, . . .)
or simply:
SubroutineA parameters, . . .
Note that any parameters must be enclosed in parentheses when using the Call keyword, but not
Search JabSto ::

Custom Search