Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

Advanced Function Techniques

The advanced function techniques covered in this section allow you to get the most out of the procedures you build. First, you learn the difference between passing your parameters by reference and passing them by value, and see that the default method of passing parameters isn't always the most prudent method.

The second part of this section shows you how to work with optional parameters, which help you build flexibility into your functions. Whereas optional parameters allow you to omit parameters, named parameters help you to add readability to your code. Named parameters also shelter you from having to worry about the order in which the parameters must appear. After reading this section, you can build much more robust and easy-to-use functions.

Passing by Reference Versus Passing by Value

By default, parameters in Access are passed

by reference . This means that a memory reference to the variable being passed is received by the function. This process is best illustrated by an example:

Sub PassByRef()
'Declare string variables
Dim strFirstName As String
Dim strLastName As String
'Assign values to the string variables
strFirstName = "Alison"
strLastName = "Balter"
'Call a subroutine that receives the two variables as
'parameters by reference
Call FuncByRef(strFirstName, strLastName)
'Print the changed values of the variables
Debug.Print strFirstName
Debug.Print strLastName
End Sub
Sub FuncByRef(strFirstParm As String, strSecondParm As String)
'Modify the values of the parameters
strFirstParm = "Bill"
strSecondParm = "Gates"
End Sub

You might be surprised that the Debug.Print statements found in the subroutine PassByRef print "Bill" and "Gates". This is because strFirstParm is actually a reference to the same location in memory as strFirstName, and strSecondParm is a reference to the same location in memory as strLastName. This violates the concepts of

black-box processing , in which a variable can't be changed by any routine other than the one it was declared in. The following code eliminates this problem:

Sub PassByVal()
'Declare the string variables
Dim strFirstName As String
Dim strLastName As String
'Assign values to the string variables
strFirstName = "Alison"
strLastName = "Balter"
'Call a subroutine that receives the two variables as
'parameters by value
Call FuncByVal(strFirstName, strLastName)
'Print the unchanged values of the variables
Debug.Print strFirstName
Debug.Print strLastName
End Sub
Sub FuncByVal(ByVal strFirstParm As String, _
ByVal strSecondParm As String)
'Change the values of the parameters
'Since they are received by value,
'the original variables are unchanged
strFirstParm = "Bill"
strSecondParm = "Gates"
End Sub

This FuncByVal subroutine receives the parameters

by value . This means that the code passes only the values in strFirstName and strLastName to the FuncByVal routine. The strFirstName and strLastName variables, therefore, can't be modified by the FuncByVal subroutine. The Debug.Print statements print "Alison" and "Balter".

The following example illustrates a great reason why you might want to pass a parameter by reference:

Sub GoodPassByRef()
'Declare variables
Dim blnSuccess As Boolean
Dim strName As String
'Set the value of the string variable
strName = "Microsoft"
'Set the boolean variable equal to the value
'returned from the GoodFunc function
blnSuccess = GoodFunc(strName)
'Print the value of the boolean variable
Debug.Print blnSuccess
End Sub
Function GoodFunc(strName As String)
'Evaluate the length of the value received
'as a parameter
'Convert to uppercase and return true if not zero-length
'Return false if zero-length
If Len(strName) Then
strName = UCase$(strName)
GoodFunc = True
Else
GoodFunc = False
End If
End Function

In essence, the GoodFunc function needs to return two values. Not only does the function need to return the uppercase version of the string passed to it, but it also needs to return a success code. Because a function can return only one value, you need to be able to modify the value of strName within the function. As long as you're aware of what you're doing and why you're doing it, there's no problem with passing a parameter by reference.

TIP

I use a special technique to help readers of my code see whether I'm passing parameters by reference or by value. When passing parameters by reference, I refer to the parameters by the same name in both the calling routine and the actual procedure that I'm calling. On the other hand, when passing parameters by value, I refer to the parameters by different names in the calling routine and in the procedure that's being called.

After reading this section, you might ask yourself whether it is better to pass parameters by reference or by value. Although in terms of black-box processing, it is better to pass by value, code that involves parameters passed by reference actually executes more quickly than those passed by value. As long as you and the programmers that you work with are aware of the potential problems with passing parameters by reference, in general, in VBA, I feel that it is better to pass parameters by reference.

Optional Parameters: Building Flexibility into Functions

The VBA language allows you to use optional parameters. In other words, it isn't necessary to know how many parameters will be passed. The ReturnInit function in the following code receives the last two parameters as optional; it then evaluates whether the parameters are missing and responds accordingly:

Function ReturnInit(ByVal strFName As String, _
Optional ByVal strMI, Optional ByVal strLName)
'If strMI parameter is not received, prompt user for value
If IsMissing(strMI) Then
strMI = InputBox("Enter Middle Initial")
End If
'If strLName parameter is not received, prompt user for value
If IsMissing(strLName) Then
strLName = InputBox("Enter Last Name")
End If
'Return concatenation of last name, first name,
'and middle initial
ReturnInit = strLName & "," & strFName & " " & strMI
End Function

This function could be called as follows:

strName = ReturnInit("Bill",,"Gates")

As you can see, the second parameter is missing. Rather than causing a compiler error, this code compiles and runs successfully. The IsMissing function, built into Access, determines whether a parameter has been passed. After identifying missing parameters, you must decide how to handle the situation in code. In the example, the function prompts for the missing information, but here are some other possible choices:

• Insert default values when parameters are missing.

• Accommodate for the missing parameters in your code.

Listing 12.1 and Listing 12.2 illustrate how to carry out these two alternatives.

Listing 12.1 Inserting Default Values When Parameters Are Missing

This example uses a default value of "A" for the middle initial and a default last name of "Roman". Now take a look at Listing 12.2, which illustrates another method of handling missing parameters.

Listing 12.2 Accommodating for Missing Parameters in Your Code

This example manipulates the return value, depending on which parameters it receives. If neither optional parameter is passed, just the first name displays. If the first name and middle initial are passed, the return value contains the first name followed by the middle initial. If the first name and last name are passed, the return value contains the last name, a comma, and the first name. If all three parameters are passed, the function returns the last name, a comma, a space, and the first name.

The declaration of the ReturnInit3 function shown in Listing 12.2 can easily be modified to provide default values for each optional parameter. The following declaration illustrates this:

Function ReturnInit4(Optional ByVal strFName As String = "Alison", _
Optional ByVal strMI As String = "J", _
Optional ByVal strLName As String = "Balter")

ReturnInit4 has three optional parameters. The declaration assigns a default value to each parameter. The function uses the default value if the calling routine does not supply the parameter.

NOTE

It is important to note that the IsMissing function only works with parameters with a data type of variant. This is because the IsMissing function returns true only if the value of the parameter is empty. If the parameter is numeric (for example, an integer), you will need to test for zero. If the parameter is a string, you will need to test for a zero-length string ("), or against the VBA constant vbNullString.

Named Parameters: Eliminate the Need to Count Commas

In all the examples you've seen so far, the parameters of a procedure have been supplied positionally. Named parameters allow you to supply parameters without regard for their position, which is particularly useful in procedures that receive optional parameters. Take a look at this example:

strName = ReturnInit3("Bill",,"Gates")

Because the second parameter isn't supplied, and the parameters are passed positionally, a comma must be used as a placemarker for the optional parameter. This requirement can become unwieldy when you're dealing with several optional parameters. The following example greatly simplifies the process of passing the parameters and also better documents what's happening:

strName = ReturnInit3(strFName:= "Bill",strLName:= "Gates")

As shown in the following example, when you pass parameters by name, it doesn't even matter in what order the parameters appear:

strName = ReturnInit3(strLName:= "Gates",strFName:="Bill")

This call to the ReturnInit3 function yields the same results as the call to the function in the previous example.

NOTE

When using named parameters, each parameter name must be exactly the same as the name of the parameter in the function being called. Besides requiring intimate knowledge of the function being called, this method of specifying parameters has one important disadvantage: If the author of the function modifies a parameter's name, all routines that use the named parameter will fail when calling the function.

Recursive Procedures

A

recursive procedure is one that calls itself. If a procedure calls itself over and over again, it will eventually render an error. This is because it runs out of stack space. Here's an example:

Function Recursive(lngSomeVal)
'Return value based on another call to the function
Recursive = Recursive(lngSomeVal)
End Function

There are practical reasons why you might want to call a function recursively. Here's an example:

Function GetFactorial(intValue as Integer) as Double
'If value passed is less than or equal to one, we're done
If intValue <= 1 Then
GetFactorial = 1
'If value passed is greater than one,
'call function again with decremented value
'and multiply by value
Else
GetFactorial = GetFactorial(intValue - 1) * intValue
End If
End Function

The code receives an input parameter (for example, 5). The value is evaluated to see whether it is less than or equal to 1. If it is, the function is exited. If the value is greater than 1, the function is called again, but is passed the previous input parameter minus 1 (for example, 4). The return value from the function is multiplied by the original parameter value (for example, 4*5). The function calls itself over and over again until the value that it passes to itself is 2 minus 1 (1), and the function is exited. In the example where 5 is passed to the function, it multiplies 5*4*3*2*1, resulting in 120, the factorial of 5.

Using Parameter Arrays

Using a parameter array, you can easily pass a variable number of arguments to a procedure. Here's an example:

Sub GetAverageSalary(strDepartment As String, _
ParamArray currSalaries() As Variant)
Dim sngTotalSalary As Single
Dim sngAverageSalary As Single
Dim intCounter As Integer
'Loop through the elements of the array,
'adding up all of the salaries
For intCounter = 0 To UBound(currSalaries())
sngTotalSalary = sngTotalSalary + currSalaries(intCounter)
Next intCounter
'Divide the total salary by the number of salaries in the array
sngAverageSalary = sngTotalSalary / (UBound(currSalaries()) + 1)
'Display the department and the average salary in a message box
MsgBox strDepartment & " has an average salary of " & _
sngAverageSalary
End Sub

The routine is called like this:

Call GetAverageSalary("Accounting", 60000, 20000, 30000, 25000, 80000)

The beauty of the ParamArray keyword is that you can pass a variable number of parameters to the procedure. In the example, a department name and a variable number of salaries are passed to the GetAverageSalary procedure. The procedure loops through all the salaries that it receives in the parameter array, adding them together. It then divides the total by the number of salaries contained in the array.