You are here Create UDF function return array with resize Create UDF function return array with resize

Create UDF function return array with resize

Programming the array function with the property of automatically receiving all the elements of the array into the sheet and automatically scaling to the size of the array is a special technology in the A-Tools Add-in such as the BS_SQL, BS_VLOOKUP,... A-Tools has opened an API library that allows you to know how to program VBA to create array functions with properties similar to those in A-Tools.

Source code:

Function GetArray() As Variant
    Dim fa As New BSFormulaArray
    Dim fi As New BSFormulaInfo
    On Error GoTo lbEndFunc
    If fa.Begin Then
        'Create array 2D
        Dim arr(2, 1)
        arr(0, 0) = "Q1": arr(0, 1) = 1
        arr(1, 0) = "Q2": arr(1, 1) = 2
        arr(2, 0) = "Q3": arr(2, 1) = 3
        'Setup information for function
        fi.FunctionName = "GetArray"
        'fi.PARAMS =array(list parameters)
        'Add array to BSFormulaArray
        GetArray = fa.Add(fi, arr)
    Else
        GetArray = fa.Result
    End If
lbEndFunc:
    Set fi = Nothing
    Set fa = Nothing
    If Err <> 0 Then
        'MsgBox Err.Description, vbCritical
    End If
End Function