ACC2000: How to Call Functions by Using a String Variable (210511)

MORE INFORMATION

To allow significant programming flexibility, you can call a function in Visual Basic for Applications when the function name is stored in a string variable. The Eval function used in the following example evaluates three strings as if they were expressions, and then returns their values:

1. Start Microsoft Access and create a new, blank database.
2. Create a module, and then type the following line in the Declarations section if it is not already there:

   Option Explicit

3. Type or paste the following procedures:

   '------------------------------------------------------------------
   'GLOBAL DECLARATIONS SECTION
   '------------------------------------------------------------------
   
   
   '------------------------------------------------------------------
   ' The CallMyArray() function creates an array of strings, then
   ' loops, using the Eval() function, to call each element of the
   ' array.
   '------------------------------------------------------------------
   
   Function CallMyArray()
      Dim MyArray(), i As Integer
      
       For i = 0 To 2
          ReDim Preserve MyArray(i)
          MyArray(i) = "MyFunction" & i & "(" & i & ")"
       Next i
   
       For i = 0 To 2
           Eval (MyArray(i))
       Next i
   End Function
   '------------------------------------------------------------------
   '    The first function called by CallMyArray().
   '------------------------------------------------------------------
   Function MyFunction0(nParam)
      MsgBox "This is the output of MyFunction" & nParam
   End Function
   '------------------------------------------------------------------
   '    The second function called by CallMyArray().
   '------------------------------------------------------------------
   Function MyFunction1(nParam)
      MsgBox "This is the output of MyFunction" & nParam
   End Function
   '------------------------------------------------------------------
   '    The third function called by CallMyArray().
   '------------------------------------------------------------------
   Function MyFunction2(nParam)
      MsgBox "This is the output of MyFunction" & nParam
   End Function
   					

4. Type the following line in the Immediate window:

   ? CallMyArray()

   Notice that you receive dialog boxes as the output of each of the three functions: MyFunction0, MyFunction1, and MyFunction2.