Hello all!
I am looking to see if there is a way to have a custom function to spill an array built in VBA?
So the sample code below is creating a simple number array from 1 to 5, but I am not sure how to make this work.
Thanks in advance!
Function TEST()
Set TestArray = CreateObject("System.Collections.ArrayList")
For i = 1 To 5
TestArray.Add i
Next i
TEST = TestArray
End Function
Something from ChatGPT:
You need to return a 2D variant array for the function to spill in Excel.
The function should be set up to work as an array function.
Here's a modified version of your VBA code that returns a 2D variant array, which can be spilled in Excel:
vba
Copy code
Function TEST() As Variant
Dim TestArray() As Variant
Dim i As Integer
' Redim the variant array to hold the values
ReDim TestArray(1 To 5, 1 To 1)
For i = 1 To 5
TestArray(i, 1) = i
Next i
TEST = TestArray
End Function
How to use this:
Press ALT + F11 to open the VBA editor.
Insert a new module.
Paste the function into this module.
Return to Excel, and in a cell, simply type =TEST(). The array will spill from that cell downwards, displaying numbers 1 through 5.
Remember that whenever you edit VBA UDFs (User Defined Functions), you might need to recalculate your sheet to see the updated results if you've changed the code. You can do this by pressing F9.
