CODEWITHSUNDEEP
ms-accessvba
noobmaster69
noobmaster69

Reputation: 3115

Access VBA Compile error Invalid ParamArray Use

Context: In Microsoft Access, I'm trying to call functions and pass parameters, dynamically, using Application.Run. My code below works in Excel 2013 but does not work in Access 2013.

Code:

Public Sub Test()
    Call MethodDynamically("MethodToBeCalled1", "This", "works")
    Call MethodDynamically("MethodToBeCalled2", "This", "works", "too")
    Call MethodDynamically("MethodToBeCalled3", "This", "works", "too", "as well")
End Sub

Public Sub MethodDynamically(MethodName As String, ParamArray Params() as Variant)
    Application.Run MethodName, Params
End Sub
Public Sub MethodToBeCalled1(Params As Variant)
    Debug.Print Params(0) & " " & Params(1)
End Sub
Public Sub MethodToBeCalled2(Params As Variant)
    Debug.Print Params(0) & " " & Params(1) & " " & Params(2)
End Sub
Public Sub MethodToBeCalled3(Params As Variant)
    Debug.Print Params(0) & " " & Params(1) & " " & Params(2) & " " & Params(3)
End Sub

Returns output:

This works
This works too
This works too as well

Error: However in Microsoft Access, the same code gives the error: Compile error: Invalid ParamArray use.

Is there a way of adjusting this code for Access VBA?

Upvotes: 2

Views: 667

Answers (1)

Erik A
Erik A

Reputation: 32642

You can't pass a ParamArray to another function directly.

Try the following:

Public Sub MethodDynamically(MethodName As String, ParamArray Params() as Variant)
    Dim MyArray As Variant
    MyArray = Params
    Application.Run MethodName, MyArray
End Sub

Upvotes: 4

Related Questions