Access VBA Compile error Invalid ParamArray Use

Question

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?

Erik A · Accepted Answer · 2017-11-24 11:00:55Z

5

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

answered Nov 24, 2017 at 11:00

Erik A

32.8k13 gold badges48 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

noobmaster69 Over a year ago

Great thanks Erik. Out of curiosity, why does this work in Excel and not Access?

Erik A Over a year ago

I've noticed multiple weird/buggy things about Application.Run in Excel, such as Application.Run "MyFunction()" running MyFunction twice (causes a runtime error in Access). All I can say is: it shouldn't work afaik.

Collectives™ on Stack Overflow

Access VBA Compile error Invalid ParamArray Use

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related