Advanced Ideas using VBA
(Page 1 of 5 )

This article, the second of two parts, continues our discussion of Visual Basic for Applications. It is excerpted from chapter seven of the Access Cookbook, Second Edition, written by Ken Getz, Paul Litwin, and Andy Baron (O'Reilly, 2005; ISBN: 0596006780). Copyright © 2005 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

7.6 Pass a Variable Number of Parameters to a Procedure

Problem

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 in your case, it's impossible to predict that value. How can you accomplish this?

Solution

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:

1. Open the Immediate window (press Ctrl+G or choose the View -> Immediate Window menu item). In these steps, you will run code from the Immediate window.
   
2. 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 Immediate 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. 
   
   
   Figure 7-9.  TestUCase with five strings converted
   
3. 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. 
   
4. 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.
   
   
   Figure 7-10.  TestSum summing 15 values

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. In this case, the variant variablevarItem  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.

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.

Next: 7.7 Sort an Array in VBA >>
 

More Microsoft Access Articles
More By O'Reilly Media

Comments On: More About VBA

· This article is an excerpt from the book "Access Cookbook, Second Edition,"...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter seven of the Access Cookbook, Second Edition, written by Ken Getz, Paul Litwin, and Andy Baron (O'Reilly, 2005; ISBN: 0596006780). Check it out today at your favorite bookstore. Buy this book now.