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.

Expand|Select|Wrap|Line Numbers

 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:

Expand|Select|Wrap|Line Numbers

{=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

Quote:

Originally Posted by 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.

Expand|Select|Wrap|Line Numbers

 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:

Expand|Select|Wrap|Line Numbers

{=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?

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

Expand|Select|Wrap|Line Numbers

 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:

Expand|Select|Wrap|Line Numbers

 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.

Returning an array from a function

re: Returning an array from a function

re: Returning an array from a function

re: Returning an array from a function

Similar Visual Basic 4 / 5 / 6 bytes

/bytes/development

/bytes/it

/bytes/about