Access Cookbook - Ken Getz [155]
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.
Discussion
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 upper case.
Dim intI As Integer
Dim astrWorking( ) As String
If IsArray(varValues) Then
ReDim astrWorking(LBound(varValues) To UBound(varValues))
For intI = LBound(varValues) To UBound(varValues)
astrWorking(intI) = CStr(UCase(varValues(intI)))
Next intI
UCaseArrayFunc = astrWorking
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.
7.7. Sort an Array in VBA
Problem
Although it's a database product, Access doesn't include a way to sort an array. You need to present sorted arrays in an application, and you can't find a reasonable way to sort them without first saving them to a table. You know you've seen array-sorting methods in other languages. Can you write a sorting routine that executes quickly?
Solution
It's true that Access doesn't provide a built-in sorting mechanism for arrays. Entire volumes in libraries are devoted to the study of various sorting and searching algorithms, but it's not necessary to dig too deep for array-sorting methods for Access. Because you'll probably place any large data sets into a table, most arrays in Access aren't very large. Therefore,