Access Cookbook - Ken Getz [154]
Say you need a procedure that can accept any number of numeric arguments and perform some operation on them. The sample procedure SumThemUp accepts an array of integers, calculates their sum, and returns the total. To try it, type:
TestSum 15
in the Immediate window (you can use any number between 1 and 20). The sample routine, TestSum, will generate an array full of random integers between 1 and 9 and will send the array to SumThemUp for processing. Figure 7-10 shows TestSum working with 15 values.
Figure 7-10. TestSum summing 15 values
You may need to write a function that can accept a list of values instead of an array. The ParamArray declaration modifier allows you to do this. Try the MinValue function in basArrays: pass to it a comma-delimited list of values, and the function will return the minimum numeric value from the list you entered. For example:
varMin = MinValue(0, -10, 15)
will return -10, which is the minimum of the three values you passed it.
Both UCaseArray and SumThemUp accept a variant as a parameter. This variant variable can hold either a single value or an array of values. From the calling end, you can pass either a variant or an actual array of values. To send an array as a parameter, you must add the trailing ( ) characters, indicating to Access that the variable represents an array. Therefore, to pass the array named aintValues to SumThemUp, call the function like this, making sure to include the ( ) in the array name:
varSum = SumThemUp(aintValues( ))
To receive a parameter that is an array, the procedure declaration can include the parentheses:
Public Function SumThemUp (aintValues( ) As Integer) As Variant
in which case you can pass only an array. You can also declare it like this:
Public Function SumThemUp (varValues As Variant) As Variant
in which case you can pass it either a single variant value or an array of values.
Once the procedure has received the array, it needs a way to loop through all the elements of the array. Access provides two methods for walking the array: looping through the items either with a For...Next loop (by index number), or with a For Each...Next loop (without using the index). UCaseArray uses the first method to loop through all the members of its array, and SumThemUp uses the second.
To loop through the elements of an array by number, you must know the bounds of the array; i.e., the lowest and highest element numbers. Access provides two functions, LBound and UBound, to retrieve the lowest and highest element numbers. UCaseArray includes code like this:
For intI = LBound(varValues) To UBound(varValues)
varValues(intI) = UCase(varValues(intI))
Next intI
This code loops through all the elements in the array, no matter what the starting and ending items are. In Basic, you can declare an array with any positive integer as its start and end points. For example, in this expression:
Dim avarArray(13 To 97) as Integer
you'd need to loop from 13 to 97 to access each element of the array. The LBound and UBound functions make it possible for generic routines to loop through all the elements of an array, even though they don't know ahead of time how many elements there will be.
The UCaseArray procedure is quite simple: once it determines that the input value is actually an array (using the IsArray function), it loops through all the elements of the passed-in array, converting each to uppercase. The array is passed by reference, using the ByRef keyword, which means that the modified array is returned to the calling procedure. The code for UCaseArray is:
Public Sub UCaseArray(ByRef varValues As Variant)
' Convert the entire passed-in array to uppercase.
Dim intI As Integer
If IsArray(varValues) Then
For intI = LBound(varValues) To UBound(varValues)
varValues(intI) = UCase(varValues(intI))
Next intI
Else
varValues = UCase(varValues)
End If
End Sub
The SumThemUp function is no more complex. It uses the For Each...Next syntax to walk through all the elements of the array, maintaining a running sum as it loops.