Application.Run for Macro returning Arrays

Question

我想从带有

"Application.Run"的xll-addin调用VBA中的函数"arrayfun1">

Sub test()
Dim val As Variant
val = Application.Run("arrayfun1", arg1, arg2, ...)
End Sub

这适用于只返回一个输出的函数，但不适用于返回数组的函数。但是，如果我在工作表中将该函数用作数组公式，它可以工作。但我正在寻找一个程序化的解决方案。

有什么想法吗？

Answer 1

我在Chip的网站上找到了这个例子：http://www.cpearson.com/excel/passingandreturningarrays.htm

Sub AAATest()
    Dim Arr() As Long
    Dim N As Long
    Arr = LoadNumbers(Low:=101, High:=110)
    If IsArrayAllocated(Arr:=Arr) = True Then
        For N = LBound(Arr) To UBound(Arr)
           Debug.Print Arr(N)
        Next N
    Else
        ''''''''''''''''''''''''''''''''''''
        ' Code in case Arr is not allocated.
        ''''''''''''''''''''''''''''''''''''
    End If
End Sub
Function LoadNumbers(Low As Long, High As Long) As Long()
    '''''''''''''''''''''''''''''''''''''''
    ' Returns an array of Longs, containing
    ' the numbers from Low to High. The 
    ' number of elements in the returned
    ' array will vary depending on the 
    ' values of Low and High.
    ''''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''''''
    ' Declare ResultArray as a dynamic array
    ' to be resized based on the values of
    ' Low and High.
    '''''''''''''''''''''''''''''''''''''''''
    Dim ResultArray() As Long
    Dim Ndx As Long
    Dim Val As Long
    '''''''''''''''''''''''''''''''''''''''''
    ' Ensure Low <= High
    '''''''''''''''''''''''''''''''''''''''''
    If Low > High Then
        Exit Function
    End If
    '''''''''''''''''''''''''''''''''''''''''
    ' Resize the array
    '''''''''''''''''''''''''''''''''''''''''
    ReDim ResultArray(1 To (High - Low + 1))
    ''''''''''''''''''''''''''''''''''''''''
    ' Fill the array with values.
    ''''''''''''''''''''''''''''''''''''''''
    Val = Low
    For Ndx = LBound(ResultArray) To UBound(ResultArray)
        ResultArray(Ndx) = Val
        Val = Val + 1
    Next Ndx
    ''''''''''''''''''''''''''''''''''''''''
    ' Return the array.
    ''''''''''''''''''''''''''''''''''''''''
    LoadNumbers = ResultArray()
End Function

Answer 2

您可以使用

VBA 中的 Declare Function 语句调用该函数(尽管您需要获取一些工具来将参数从 VBA 格式转换为 Excel SDK 格式，反之亦然(或使用 ExecuteExcel4Macro 或 Evaluate(尽管这些需要先将所有参数转换为字符串(。

请参阅此网页：https://fastexcel.wordpress.com/2014/12/13/calling-xlamxllautomation-udfs-from-vba-evaluate-run-or-reference/

还有这个问题：如何从 vba 调用 xll 插件函数？