Chapter 11. Functions and Subroutines

VBA allows two kinds of procedures: functions and subroutines. 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 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.

I 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 11-1 adds 1 to the original value.

Example 11-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 11-1, where the expression AddOne(5) is replaced by the return value of AddOne, which in this case is 6.

The message dialog box displayed by Example 11-1
Figure 11-1. The message dialog box displayed by Example 11-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 11-2 shows a slightly more complicated example of a function.

Example 11-2. Assigning a function’s return value
Function ReturnCount(  ) As Variant

' Return count of records in recordset

If rs Is Nothing Then
    ReturnCount = "No recordset"
Else
    ReturnCount = rs.RecordCount
End If

End Function

This function returns a count of the number of records in the recordset referenced by the variable rs. However, if rs does not currently reference a recordset, then the function returns the words "No recordset".

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 If statement.

Calling Subroutines

A subroutine declaration has the form:

[Public or Private] SubSubroutineName(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 otherwise.

Parameters and Arguments

Consider the following very simple subroutine, which does nothing more than display a message box declaring a person’s name:

Sub DisplayName(sName As String)
   MsgBox "My name is " & sName
End Sub

To call this subroutine, we would write, for example:

DisplayName "Wolfgang"

or:

Call DisplayName("Wolfgang")

The variable sName in the procedure declaration:

Sub DisplayName(sName As String)

is called a parameter of the procedure. The call to the procedure should contain a string variable or a literal string that is represented by the variable sName in this procedure (but see the discussion of optional arguments in the next section). The value used in place of the parameter when we make the procedure call is called an argument . Thus, in the previous example, the argument is the string “Wolfgang.”

Note that many programmers fail to make a distinction between parameters and arguments, using the names interchangeably. However, since a parameter is like a variable and an argument is like a value of that variable, failing to make this distinction is like failing to distinguish between a variable and its value!

Optional Arguments

In VBA, the arguments to a procedure may be specified as optional, using the Optional keyword. (It makes no sense to say that a parameter is optional; it is the value that is optional.)

For instance, the definition of the OpenRecordset method is:

Setrecordset = object.OpenRecordset(source, type, options, lockedits)

where type , options , and lockedits are optional. Thus, for instance, each of the following lines of code are legal:

Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("Names")
Set rs = CurrentDb.OpenRecordset("Names", dbOpenForwardOnly)
Set rs = CurrentDb.OpenRecordset("Names", dbOpenForwardOnly, dbReadOnly)
Set rs = CurrentDb.OpenRecordset("Names", dbOpenForwardOnly, _
	dbReadOnly, dbOptimistic)

To define a function with optional arguments, we just include the keyword Optional in the parameter declaration, as in Example 11-3.

Example 11-3. Using an optional argument
Sub ChangeFieldType(sFieldName As String, _
                     Optional NewSize As Variant)

' Change type to integer
rs!Fields(sFieldName).Type = dbInteger

' If size supplied, use it. Else use 25.
If Not IsMissing(NewSize) Then
   rs!Fields(sFieldName).Size = CInt(NewSize)
Else
   rs!Fields(sFieldName).Size = 25
End If

End Sub

The second parameter is declared with the Optional keyword. Because of this, we may call the procedure with or without an argument for this parameter, as in:

ChangeFieldType("Age", 10)

and:

ChangeFieldType("Age")

Note that the IsMissing function is used in the body of the procedure to test whether the argument is present. If the argument is present, then the font size is changed. Note also that we declared the NewSize parameter as type Variant because IsMissing works only with parameters of type Variant. (Other types of variables are given default values, which precludes the possibility of them going missing.) Thus, we converted the Variant to type Integer using the CInt function.

Note that a procedure may have any number of optional arguments, but they must all come at the end of the parameter list.

Named Arguments

Normally, the arguments to a function are matched to the parameters by their position in the function call. For instance, in the function call:

Set rs = CurrentDb.OpenRecordset("Objects", dbOpenForwardOnly)

Access can tell that the argument dbOpenForwardOnly is the value for the second parameter (Type) of the function. Such arguments are called positional arguments .

Many built-in VBA/DAO functions also allow named arguments. For example, the OpenRecordset function can be called as follows:

Set rs = CurrentDb.OpenRecordset(Name:="Objects", _
    Type:=dbOpenForwardOnly)

Here, each argument has the form:

ParameterName:=Argument

There are three main advantages to named arguments:

  • Named arguments can improve readability and clarity.

  • Blank spaces (separated by commas) are required for missing optional arguments when using a positional declaration, but not when using named arguments.

  • The order in which named arguments are listed is immaterial, which, of course, is not the case for positional arguments. For instance, the previous function call could be written:

    Set rs = CurrentDb.OpenRecordset(Type:=dbOpenForwardOnly, _
    Name:="Objects")

Named arguments can improve readability quite a bit, and they are highly recommended. However, they can require considerably more space, so for the short examples in this book, I usually will not use them.

ByRef Versus ByVal Parameters

Parameters come in two flavors: ByRef and ByVal. Many programmers do not have a clear understanding of these concepts, but they are very important and not that difficult to understand.

To explain the difference, I present the two procedures in Example 11-4. ProcedureA simply sets the value of the module-level variable x to 5, displays that value, calls the procedure AddOne with the argument x, and then displays the value of x again.

Example 11-4. Testing the ByVal and ByRef keywords
Sub ProcedureA(  )
   x = 5            ' Set x to 5
   MsgBox x         ' Display x
   Call AddOne(x)   ' Call AddOne
   MsgBox x         ' Display x again
End Sub

Sub AddOne(ByRef i As Integer)
   i = i + 1
End Sub

Note the presence of the ByRef keyword in the AddOne procedure declaration. This keyword tells VBA to pass a reference to the variable x to the AddOne procedure. Therefore, the AddOne procedure, in effect, replaces its parameter i by the variable x. As a result, the line:

i = i + 1

effectively becomes:

x = x + 1

So, after AddOne is called, the variable x has the value 6.

On the other hand, suppose we change the AddOne procedure, replacing the keyword ByRef with the keyword ByVal:

Sub AddOne(ByVal i As Integer)
   i = i + 1
End Sub

In this case, VBA does not pass a reference to the variable x, but rather it passes its value. Hence, the variable i in AddOne simply takes on the value 5. Adding 1 to that value gives 6. Thus, i equals 6, but the value of the argument x is not affected! Hence, both message boxes will display the value 5 for x.

ByRef and ByVal both have their uses. When we want to change the value of a variable, we must declare the corresponding parameter as ByRef so that the called procedure has access to the actual variable itself. This is the case in the previous example. Otherwise, the AddOne procedure does absolutely nothing, since the local variable i is incremented, but it is destroyed immediately afterwards, when the procedure ends.

On the other hand, when we pass an argument for informational purposes only, and we do not want the argument to be altered, it should be passed by value, using the ByVal keyword. In this way, the called procedure gets only the value of the argument.

There is one downside to passing arguments by value: it can take a lot of memory (and time). When passing a string variable that contains a large string by value, the entire string must be duplicated.

Thus, we can summarize by saying that if we want the procedure to modify an argument, the argument must be passed by reference. If not, the argument should be passed by value unless this will produce an unacceptable decrease in performance, or unless we are very sure that it will not get changed by accident.

It is important to note that VBA defaults to ByRef if we do not specify otherwise. This means that the values of arguments are subject to change by the called procedure, unless we explicitly include the keyword ByVal. Caveat scriptor !

Exiting a Procedure

VBA provides the Exit Sub and Exit Function statements, should we wish to exit from a procedure before the procedure would terminate naturally. For instance, if the value of a parameter is not suitable, we may want to issue a warning to the user and exit, as Example 11-5 shows.

Example 11-5. Using the Exit Sub statement
Sub DisplayName(sName As String)
   If sName = "" then
      Msgbox "Please enter a name."
      Exit Sub
   End If 
   MsgBox "Name entered is " & sName
End Sub

While we are on the subject of exiting, we should comment on the use of the End statement, which will terminate a procedure. Simply put, you should almost never use the End statement in VBA programming, since it produces a rather abrupt termination of a program. (I never like to say never.) Here is a partial list of what happens when the End statement is executed:

  • Code execution stops abruptly, without invoking the Unload, QueryUnload, or Terminate event of any forms in the application, which means that forms are not given the opportunity to prevent the program from terminating or from performing any necessary cleanup.

  • All module-level variables and all static local variables are reset. (Nonstatic local variables go out of scope, as expected.) Objects created from class modules are destroyed.

  • Files opened using the Open statement are closed.

While there may be some rather specialized situations in which this behavior is desirable, you will no doubt recognize such a situation if and when it arises. In the meantime, it is probably best to simply avoid using the End statement.

Public and Private Procedures

Just as variables and constants have a scope, so do procedures. We can declare a procedure using the Public or Private keyword, as in:

Public Function AddOne(i As Integer) As Integer

or:

Private Function AddOne(i As Integer) As Integer

The difference is simple: a Private procedure can only be called from within the module in which it is defined, whereas a Public procedure can be called from within any module in the project.

Note that if the Public or Private keyword is omitted from a procedure declaration, then the procedure is considered to be Public.

Fully Qualified Procedure Names

When we call a public procedure that lies in another code module, there is a potential problem with ambiguity, for there may be more than one public procedure with the same name in another module. VBA will execute the first one it finds, and this may not be the one we had in mind!

The solution is to use a qualified procedure name, which has the form:

ModuleName.ProcedureName

For instance, if a public procedure named AddOne lies in a module named Utilities, then we can call this procedure using the syntax:

Utilities.AddOne
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.144.151.126