You need a procedure that will work on a list of items, and you don’t know ahead of time how many there will be. You know that VBA will allow you to use optional parameters, but this requires you to know exactly how many items you might ever need to pass, and it’s impossible to predict that value. How can you accomplish this?
You have two choices in solving this problem: you can pass an array as a parameter, or you can pass a comma-delimited list, which Access will convert into an array for you. An array (an ordered list of items) must contain a single data type. By using the variant data type, though, you can pass a list of varying types into your procedure. This solution demonstrates both these techniques.
From 07-06.MDB
, load the module basArrays in
design mode and do the following:
Open the debug window (click the debug window button on the toolbar or choose the View → Debug Window menu item). In these steps, you will run code from the debug window.
If you need a procedure that will take a list of words and convert each to uppercase, you can use the UCaseArray procedure. To test it, type the following in the debug window:
TestUCase 5
You can replace the 5 in the command line with any value between 1 and 26. The procedure will create as many strings as you request, place them into an array, and then call UCaseArray. This procedure will convert all the strings in the array to uppercase. The test procedure will display the original version, followed by the altered version of the array. As you can see, no matter how many items you specify for the UCaseArray procedure to work on, it’ll convert them all to uppercase. Figure 7-9 shows this procedure in use.
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 debug 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.
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
it’ll 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 ByRef
, 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. In this
case, the variant variable varItem
takes
on the value of each element of the array as it loops through the
items, and adds its value to varSum
. The
source code for SumThemUp is:
Public Function SumThemUp(varValues As Variant) As Variant ' Find the sum of the values passed in. Dim varItem As Variant Dim varSum As Variant varSum = 0 If IsArray(varValues) Then For Each varItem In varValues varSum = varSum + varItem Next varItem Else varSum = varValues End If SumThemUp = varSum End Function
Passing a list that Access converts to an
array for you is no more difficult. To use this technique, you must
declare your procedure’s formal parameters so that the list of
values is the last parameter the procedure expects to receive. Use
the ParamArray
keyword to indicate that you want
to treat an incoming list as an array, and declare your array
parameter as an array of variants:
Public Function MinValue(ParamArray varValues( ) As Variant) As Variant
Once inside the procedure, you can
treat the array parameter like any other array. That is, you can
either loop from LBound to
UBound for the array, or use a
For
Each...Next
loop to visit
each element.
To
use this method effectively, be aware that unless told otherwise,
Access always creates arrays with the first element numbered 0. Some
programmers insist on starting all arrays with 1 and so use the
Option
Base
1
statement in their modules’ Declarations
areas. Others are happy with 0 as their starting point, and some
leave the option base setting at 0 (its default) but disregard the
element numbered 0. You must never assume anything about the lower or
upper bounds on arrays, or sooner or later generic routines
won’t work. If you’re writing code that will be called by
other programmers, you need to be aware of these variations on the
normal usage.
If you decide to use the
For
Each...Next
syntax to
access all of the elements of an array, both the variable you use to
loop through the elements and the array itself must be variants. In
addition, note that you cannot set the values of items in an array
using the For Each...Next
syntax; it only allows
you to retrieve the values from the array. If you want to loop
through an array to set its values, you must use the standard
For...Next
syntax, using a numeric value as the
loop counter.
In Access 2000 and later, you can use an array as the return value for a function. Thus, you could rewrite the UCaseArray procedure as follows:
Public Function UCaseArrayFunc(ByVal varValues As Variant) As String( ) ' Convert the entire passed in array to uppercase. Dim intI As Integer Dim strWorking( ) As String If IsArray(varValues) Then ReDim strWorking(LBound(varValues) To UBound(varValues)) For intI = LBound(varValues) To UBound(varValues) strWorking(intI) = CStr(UCase(varValues(intI))) Next intI UCaseArrayFunc = strWorking End If End Function
The advantage of this technique is that the function returns a second
array and the original array, varValues, is not modified. Unlike the
first example, UCaseArray, the array is passed
ByVal
, which means that
UCaseArrayFunc works with a copy of the original
array. Any modifications occurring in
UCaseArrayFunc will affect only this copy,
leaving the original array in the calling procedure
unchanged.