Access Cookbook

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 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 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.

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 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 ...