Return an array from a function within a sub
I'm somehow new to excel VBA and I'm stuck with something that should be super easy and I hope you can help me.
I have a Sub (called test() in this case) and I want there to get an array from my function ReturnArray().
But all I get is "Run-time error '13': Type mismatch" when I try to run the sub.
Thanks in Advance.
Function ReturnArray() As Variant Dim Accounts As Variant Accounts = Array(1, 2, 3, 4, 5, 6, 7, 8, 9,) ReturnArray = Accounts End Function
Sub test() Dim acc() As Variant acc = ReturnArray Debug.Print acc End Sub
Dim acc() As Variant declares acc as an array of variants but you are returning a single variant from your ReturnArray function. That single variant happens to contain an array. But just one array.
Dim acc As Variant
Now with that said, you are now going to get a runtime error on Debug.Print acc because you cannot print out a whole array on one line. You need to loop over all the elements and print them one at a time. You can turn all the array elements into one string by joining them all together using the Join function: Debug.Print Join(acc, ",")