Chapter 6. Functions and Subroutines

As we have seen, VBA allows two kinds of procedures: functions and subroutines. As a reminder, the only difference between a function and a subroutine is that a function returns a value, whereas a subroutine does not.

Calling Functions

A function declaration has the form:

[Public or Private] Function FunctionName(Param1 As DataType1, _
         Param2 As DataType2,...) As ReturnType

Note that we must declare the data types not only of each parameter to the function, but also of the return type. Otherwise, VBA declares these items as variants.

We will discuss the optional keywords Public and Private later in this chapter, but you can probably guess that they are used here to indicate the scope of the function, just as they are used in variable declarations.

For example, the AddOne function in Example 6-1 adds 1 to the original value.

Example 6-1. The AddOne Function

Public Function AddOne(Value As Integer) As Integer
   AddOne = Value + 1
End Function

To use the return value of a function, we just place the call to the function within the expression, in the location where we want the value. For instance, the code:

MsgBox "Adding 1 to 5 gives: " & AddOne(5)

produces the message box in Figure 6-1, where the expression AddOne (5) is replaced by the return value of AddOne, which, in this case, is 6.

Figure 6-1. The message dialog displayed by Example 6-1

Note that, in general, any parameters to a function must be enclosed in parentheses within the function call.

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
Else
  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.