Chapter 6. Functions and Subroutines

In this chapter:

  • Calling Functions

  • Calling Subroutines

  • Parameters and Arguments

  • Exiting a Procedure

  • Public and Private Procedures

  • Referencing a Project

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.

Calling Functions

A function declaration has the form:

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

Note that you 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.

I discuss the optional keywords Public and Private later, 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, just place the call to the function within the expression in the location where you 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, ...

Get Writing Word Macros, Second Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.