Application.Run for Macro returning Arrays

Question

i want to call a function "arrayfun1" in vba from an xll-addin with "Application.Run"

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

this works for functions that return just one output, but not for functions that return arrays. However, if i use the function as Arrayformula in a sheet it works. But i am looking for a programatical solution.

any ideas?

score 0 · Answer 1 · edited May 08 '18 at 21:49

I found this example at Chip's website: 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

unfortunately this doesnt solve the problem. the problem is that the function itself returns an error. `val = Application.Run("arrayfun1", arg1, arg2, ...)` gives me back "#uncalculated". — eugene251, Jan 17 '18 at 17:29
The code I post is just an example, you need to modify the code and you will be able to receive an array as result. — , Jan 17 '18 at 18:04
i understand what you are suggesting but the function is in a xll-file so i can only run it with `Application.Run(...)` i can not see the function itself. — eugene251, Jan 17 '18 at 18:12
The function does return an array when it's called in the worksheet; he's asking how to get the array into VBA without using the worksheet as intermediary. — AnotherParker, Mar 16 '18 at 23:22

score 0 · Answer 2 · answered Mar 16 '18 at 23:25

You can call into the function using a Declare Function statement in VBA (though you'll need to acquire some tools to convert the parameters from VBA format to Excel SDK format, and vice versa for the return value), or using ExecuteExcel4Macro or Evaluate (though those require converting all the arguments to strings first).

See this web page: https://fastexcel.wordpress.com/2014/12/13/calling-xlamxllautomation-udfs-from-vba-evaluate-run-or-reference/

And this question: How do I call an xll addin function from vba?

Application.Run for Macro returning Arrays

2 Answers2