ACC: How to Pass an Array as an Argument to a Procedure

Last reviewed: August 29, 1997
Article ID: Q116140
The information in this article applies to:
  • Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

In order for a procedure to accept an array as an argument, the procedure must declare the array argument, with opening and closing parentheses after the argument name. Additionally, when passing the array to the procedure, you must include parentheses after the array name unless you are using Visual Basic. Otherwise, the error message "Parameter type mismatch" or "Duplicate definition" may occur.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0

MORE INFORMATION

The following example demonstrates the correct syntax for procedure declarations with an array:

   Function YourFunctionName (InArray() As String) As Integer

   Sub YourSubName (InArray() As String)

The following example demonstrates the correct syntax for procedure calls with an array:

   Result = YourFunctionName(YourArrayName())

   YourSubName YourArrayName()

NOTE: When calling procedures in Visual Basic, you do not have to include the opening and closing parentheses after "YourArrayName" in the above example.

Example

The following example demonstrates a sample user-defined function that loads an array with string values:

  1. Create a new module and enter the following code:

    NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

          '---------------------------------------------------------------
          'GLOBAL DECLARATIONS SECTION
          '---------------------------------------------------------------
          Option Explicit
    

          '---------------------------------------------------------------
          'The function LoadArray() loads an array called MyArray
          'with string values. After loading the array, the function
          'calls a procedure that displays each array element
          'in a message box.
          '---------------------------------------------------------------
    

          Function LoadArray()
    
             Dim i as Integer
             ReDim MyArray(10) As String
             For i = 1 to 10
                MyArray(i) = "Test Value: " & i
             Next i
             DisplayArray MyArray()
          End Function
    
          '---------------------------------------------------------------
          'LoadArray() Sub Procedure
          '---------------------------------------------------------------
    
          Sub DisplayArray (InArray() As String)
             Dim i as Integer
             For i = 1 to UBound(InArray)
                MsgBox InArray(i)
             Next i
          End Sub
    
    

  2. On the View menu, click Debug Window (or Immediate Window in versions 1.x and 2.0)

  3. Type the following line, and then press ENTER:

          ? LoadArray()
    

REFERENCES

For more information about Arrays, search the Help Index for "Arrays."

Keywords          : kbprg PgmHowTo SynFnc MdlOthr
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto


================================================================================


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: August 29, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.