excel - Application.run 有超过 30 个参数

Question

我正在使用 Excel VBA。有谁知道克服 Application.Run() 的参数长度限制的方法？ ? (或者请建议可以做同样工作的其他功能。)

具体到我的情况，我的一些限制是:

我需要将被调用函数指定为字符串

该函数在标准模块内

是一个函数，需要返回值，所以Call()不管用。

在任何情况下，我都不希望更改被调用函数的参数列表(例如更改为变量数组或 ParamArray)，因为我已经编写了一些其他依赖于函数声明的功能。

编辑:针对下面的一些评论，我可以在此处提供我的项目的简化版本(尽管可能与原始问题无关)。事实上，除了 30-arg 约束之外，整个设计已经建立并且运行顺利。

最终目标是启用以下电子表格功能，可以像 =mySpreadSheetFn("calledFn", "para1=abc", "para2=2002", ...) 一样调用.这将调用函数 calledFn()其声明可能是:
Function calledFn(Optional para1 As String = "P1", _
Optional para2 As Integer = 202, _
Optional para3 As Boolean = True)
并且默认参数将被相应地替换，如 ParamArray 中指定的那样。在 mySpreadSheetFn()称呼。同样会有 calledFn2()最终用户可以使用的等。所以，必须有一个 Application.Run()里面 mySpreadSheetFn() .

以下是函数定义:

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

几乎没有任何改变前端的空间，因为这是用户界面所需要的。

有什么想法吗？

Answer 1

可能有点晚了，但是如果将方法转移到一个类中，一切都会变得容易得多:

“c1”类

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

模块“主”

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