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

user_3250992 0
Viewed 9740

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.

vba excel
3 Answers

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
user_7653950

You got 2 options.

1 split the string "This doesnt, work into 2 params

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

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

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

or use optional params:

Public Sub TestThis()
    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, Optional Param2 As String)
    Debug.Print Param1 & ", " & Param2
End Sub

EDIT: try this.

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

Public Sub MethodDynamically(MethodName As String, Optional params As String = "")
    If params = "" Then Application.Run MethodName
    Select Case selectParamNumbers(params)
        Case 0
        paramArr = paramSplit(params)
        Application.Run MethodName, paramArr(0)
        Case 1
        paramArr = paramSplit(params)
        Application.Run MethodName, paramArr(0), paramArr(1)
        Case 2
        paramArr = paramSplit(params)
        Application.Run MethodName, paramArr(0), paramArr(1), paramArr(2)
        'And so on...
   End Select
End Sub

Public Function paramSplit(param As String) As Variant
    paramSplit = Split(param, delimiter:=", ")
End Function

Public Function selectParamNumbers(param As String) As Long
    Dim paramAmount() As String
    paramAmount = Split(param, delimiter:=",")
    selectParamNumbers = UBound(paramAmount)
End Function

Public Sub MethodToBeCalled(Param1 As String, Optional Param2 As String)
    Debug.Print Param1 & " " & Param2
End Sub
user_90029060 
Public Sub Test()
    Call MethodDynamically("MethodToBeCalled", "This doesnt, work")
End Sub

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

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