Pass dynamic parameters to Application.Run in VBA - Error 449 argument not optional error

Question

Context: I am trying to dynamically call a method via VBA's Application.Run function and pass parameters to the method, dynamically. More of a proof of concept than an actual use case.

Code:

Public Sub Test()
    Call MethodDynamically("MethodToBeCalled", "This doesnt, work")
End Sub

Public Sub MethodDynamically(MethodName As String, Params As String)
    Application.Run MethodName, Params
End Sub

Public Sub MethodToBeCalled(Param1 As String, Param2 As String)
    Debug.Print Param1 & " " & Param2
End Sub

Error: Running the Test method I receive Run-time error '449': Argument not optional on the Application.Run line in the MethodDynamically method.

Expectation: My desire is that running the Test method will trigger MethodToBeCalled with This doesnt and work being passed as parameters. The result would be This doesnt work in the Immediate Window.

Answer 1

This question already has an answer here but it's worth considering an example that allows for MethodDynamically to call other sub-routines with an arbitrary number of arguments.

The solution is to use the ParamArray to deal with an unknown number of arguments. For example:

Option Explicit

Public Sub Test()
    Call MethodDynamically("MethodToBeCalled1", "This", "works") '<-- 2 args
    Call MethodDynamically("MethodToBeCalled2", "This", "works", "too") '<-- 3 args
    Call MethodDynamically("MethodToBeCalled3", "This", "works", "too", "as well") '<-- 4 args
    Call MethodDynamically("MethodToBeCalled4", "Working", 10, 2, 35) '<-- 4 args; different types
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

Public Sub MethodToBeCalled4(Params As Variant)
    Debug.Print Params(0) & " " & CStr((Params(1) ^ Params(2)) + Params(3))
End Sub

Outputs:

This works
This works too
This works too as well
Working 135