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