Application.run with more than 30 arguments

Question

I am using Excel VBA. Does anyone know about a way to overcome the argument length limit of Application.Run()? (Or please suggest other function that can do the same job.)

Specific to my situation some of my constraints are:

• I need to specify the called function as a string
• The function is within a standard module
• It is a function, the return value is needed, so Call() will not work.

In any case, I do not want the parameter list of the called function to be changed (e.g. to variant array or ParamArray) since I have written some other functionalities which depend on the function declaration.

---

EDIT: In response to some of the comments below I can provide a simplified version of my project here (could be off the original question though). In fact the whole design is established and running smooth except for the 30-arg constrain.

The very ultimate goal is to enable the following spreadsheet function which can be called like =mySpreadSheetFn("calledFn", "para1=abc", "para2=2002", ...). This will invoke the function calledFn() whose declaration may be:

Function calledFn(Optional para1 As String = "P1", _ Optional para2 As Integer = 202, _ Optional para3 As Boolean = True)

and the default argument will be replaced accordingly as specified in the ParamArray in the mySpreadSheetFn() call. Similarly there will be calledFn2() etc which an end user can use. So, there has to be an Application.Run() inside mySpreadSheetFn() .

And here are the function definitions:

Type paramInfo
    Val As Variant
    dataType As String 'can be Enum but let's forget it for this purpose
End Type

Function mySpreadSheetFunction(fnName As String, ParamArray otherParams())

Dim fnParams As Scripting.Dictionary
' getFnDefaultParams(fn): return the defaults and data types of fn's params
'   as a Dictionary. Each item is of type paramInfo (see above)
Set fnParams = getFnParams(fnName)

' For each specified arg check whether it exists for the function.
' If so, replaces the default value with the input value.
' If not exist, then just ignore it
' The problem is really not with this part so just know 
'    we have all the parameters resolved after the for-loop
For i = LBound(otherParams) To UBound(otherParams)
    Dim myKey As String
    myKey = Split(otherParams(i), "=")(0)
    If fnParams.Exists(myKey) Then
        ' parseParam() converts the input string into required data type
        fnParams(myKey).Val = parseParam(Split(otherParams(i), "=", 2)(1), _
                              fnParams(myKey).DataType _
                              )
    End If
Next

' Here is the issue since the call cannot go beyond 30 args
Dim lb As Integer: lb = LBound(fnParams)
Select Case UBound(fnParams) - LBound(fnParams) + 1
Case 1: Application.Run fnName, fnParams(lb).Val
Case 2: Application.Run fnName, fnParams(lb).Val, fnParams(lb + 1).Val
' Omitted, goes until Case 30
' What to do with Case 31??
End Select

' Some other operations for each call

End Function

' An example of function that can be called by the above mySpreadSheetFn()
Function calledFn(Optional para1 As String = "P1", _ 
    Optional para2 As Integer = 202, _ 
    Optional para3 As Boolean = True)

' needs to return value
calledFn = para1 & para2 * 1000

 End Function

There is hardly any room to change the front-end since this is how the user interface is desired.

Any thoughts?

Answer 1

Probably a bit late, but if you transfer the method to a class, everything becomes much easier:

Class "c1"

Public Sub IHaveTooManyArguments(ParamArray params())
    Debug.Print "Refactor me!"
End Sub

Module "Main"

Public Sub CallIHaveTooManyArguments(fnName As String, ParamArray params())
    Dim o as new c1
    CallByName o, fnName, VbMethod, params
End Sub

Answer 2

Pack several arguments into an array. Or pack several arguments into some sort of data document, like an Xml file/string, or a JSON file/string, or an ordinary text file.

Answer 3

If you're determined to call a procedure with more that 30 parameters via Application.Run, you'll need a trampoline procedure in order to match its function signature. Make a second prodecure that takes the parameters in an array (or some other package), and then pass that to a second procedure that calls the one with too many parameters:

Sub Test()
    Dim args As Variant
    args = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, _
                 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, _
                 31, 32)
    Application.Run "ToManyArgsTrampoline", "fnName", args
End Sub

Sub ToManyArgsTrampoline(fnName As String, args() As Variant)
    If UBound(args) = 31 Then
        IHaveTooManyArguments fnName, args(0), args(1), args(2), args(3), args(4), args(5), _
                              args(6), args(7), args(8), args(9), args(10), args(11), _
                              args(12), args(13), args(14), args(15), args(16), args(17), _
                              args(18), args(19), args(20), args(21), args(22), args(23), _
                              args(24), args(25), args(26), args(27), args(28), args(29), _
                              args(30), args(31)
    End If
End Sub

Sub IHaveTooManyArguments(fnName As String, ParamArray otherparams())
    Debug.Print "Refactor me!"
End Sub