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] FunctionFunctionName
(Param1
AsDataType1
, _Param2
AsDataType2
,...) AsReturnType
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.
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.
![]() |
Note that, ...
Get Writing Excel Macros with VBA, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.