Returning an array from a function

[TheServant]

Hi everyone,
I have been trying and searching for everything that I can think of, but I still have no solution, which makes me wonder if I'm on the wrong track.

Function tester() As Variant
    Dim fray(11) As Variant
 
    For i = 1 To 12
        fray(i - 1) = i
    Next
 
    tester = fray
End Function

Now my output is all 1's in each of the 12 cells that I have included in my excel array formula:

{=tester()}

This is probably because tester is not an array. My question is, how to I make tester and array, and are there any restrictions of function inputs because of this? Or am I totally on the wrong track?

[MikeTheBike]

@TheServant
Hi

before we can comment it would be useful to know precisely what you are trying to do with the array after you have assigned it to the function !!

For instance this produces the correct ouput

Option Explicit
 
Function tester() As Variant
    Dim fray(11) As Variant
    Dim i As Integer
 
    For i = 1 To 12
        fray(i - 1) = i
    Next i
 
    tester = fray
End Function
 
Sub testFunction()
    Dim ThisArray() As Variant
    Dim i As Integer
 
    ThisArray = tester
    For i = 0 To UBound(ThisArray)
        MsgBox ThisArray(i)
    Next i
 
End Sub

??

MTB

[TheServant]

Thanks for your reply.
What I want is to cycle through several tables with the month key. For each row I want to do a complex function, which I could do in excel, but the if and nested if statements would just be a lot easier in VB. Now that would be simple to have a sub and go through that way...
However, if rows are inserted or deleted I would like it to still work, and since a sub would require defined ranges, I thought it would be simpler to have an excel input (which can be updated by excel as needed) and return the values.

Thanks for your time.

[TheServant]

Solved:

Function tester() As Variant
    Dim fray() As Variant
    Dim i As Long
 
    ReDim fray(1 To 12, 1 To 1)
    For i = 1 To 12
        fray(i, 1) = i
    Next
    tester = fray
End Function

Thanks for your help.