Return array from function in VBA

Question

I would like to write a function to return an array of integers so I can index them, but I am not aware of the syntax for VBA. Here is the pseudo code:

function getStats() as integer
    dim returnVal(4) as integer
    returnVal(0)=c2percent14
    returnVal(1)=c3percent14
    returnVal(2)=c4percent14
    returnVal(3)=c5percent14
    getStats=returnVal
end function

msgbox getStats(3)

where these values are all integers, or should be, and then I can index the return array for the stat that I want.

How can I return an array from a function?

Answer 1

Function getStats() As Variant
    getstats = Array(c2percent14, c3percent14, c4percent14, c5percent14)
End Function

Sub mysub()
    Dim myArray() As Variant
    myArray = getStats()
    msgbox myArray(3)
End Sub 

getStats is now an Array of type ´Variant´. The drawback of this method is that you effectively have no static typing anymore, since Variant could be anything.