27

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?

Improve this question

3 Answers 3

Reset to default
56

Give the function the type as an array:

function getStats() as Integer()    
    dim returnVal(0 to 3) as integer

    returnVal(0) = c2percent14
    returnVal(1) = c3percent14
    returnVal(2) = c4percent14
    returnVal(3) = c5percent14

    getStats = returnVal
end function

Sub mysub()
   Dim myArray() As Integer

   myArray = getStats()

   msgbox myArray(3)
end sub
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Are both answers robust in their use? Or is one preferred over the other/do they have differing properties that lead to certain use cases in which one may be advantageous or disadavantageous?
This answer and mine are both correct. This answer originally had a bug that caused it to fail, but was later fixed.
13

I'm going to add an answer here because I'm happy to say, after hours of frustration and bad information, I finally know how to return arrays! Here is how you return an array from a function:

Sub mysub()
    Dim i As Integer, s As String
    Dim myArray() As Integer 'if you declare a size here you will get "Compile error, can't assign to array"
    myArray = getStats()

    s = "Array values returned:" & vbCrLf
    For i = 0 To UBound(myArray)
        s = (s & myArray(i) & " ")
    Next
    MsgBox s
End Sub

Function getStats() As Integer() 'The return type must be EXACTLY the same as the type declared in the calling sub.
    Dim returnVal(2) As Integer 'if you DON'T declare a size here you will get "Run-time error '9': Subscript out of range"

    returnVal(0) = 0
    returnVal(1) = 1
    returnVal(2) = 2
    'returnVal(3) = 3 This will throw an error. Remember that an array declared (2) will hold 3 values, 0-2.
    getStats = returnVal
End Function

Output: enter image description here

The comments I included here are very important. Although VBA is usually pretty lax, this particular thing is very picky. These are required for your function, assignment, and return to work:

  • The array declared in the calling sub has to be of undeclared length.
  • The function that returns an array has to be of EXACTLY same type. Even if you declare the array in the sub as a Variant and the function returns an Integer array that will not work either.
  • You have to use a temporary array in the function. You cannot assign values to the function name (getStats) like you normally would in a function; you can only assign the temporary array to the function name once you have assigned all the values to the temp array. Any attempt to ReDim getStats as an array will also throw an error.
  • The temp array has to be declared with length. In VBA you cannot assign values to an array at an index until you declare the length.
Improve this answer

6 Comments

Any attempt to ReDim getStats as an array will also throw an error - not really. True, you cannot assign getStats(2) = 1 from inside of getStats, but that's only because the compiler confuses it with recursively calling getStats (even though the arguments don't match). But you can do Redim getStats(2), and you then can pass getStats to some other function that accepts an integer array. So the name variable works, it's just you can't access its elements directly.
The temp array has to be declared with length - not really either; it has to have a length at the point of accessing its elements, but it doesn't have to be a fixed array (whose length cannot be changed). So you can declare Dim returnVal() As Integer, you just need to ReDim returnVal(2) before assigning the elements.
Remember that an array declared (2) will hold 3 values, 0-2 - the default array lower bound is controlled with Option Base which can be either 0 or 1, so a (2) array may contain two or three elements. I've always preferred to explicitly list the boundaries for this reason, Dim returnVal(0 To 2), or Dim returnVal(11 To 42).
@GSerg on your first comment that's what I meant, you can't assign values directly to the function name like you could with a function that returns a Boolean or String. On your second comment yes that's what I meant is you have to declare the length at some point, so might as well do it at the variable declaration right?
you have to declare the length at some point, so might as well do it at the variable declaration right? - the side effects differ, which may or may not matter. Dim returnVal(2) creates an array that cannot be resized. If you need to adjust it in your function later, you can't. Dim returnVal() : ReDim returnVal(2) creates an array that can be resized. It does not matter after returning the array, because then it can be resized anyway.
|
11 
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.

Improve this answer

1 Comment

This explanation is not correct. As coded, getStats is a function that takes no arguments and returns a variant, which may be an array but could just as well be an integer, a string, etc.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.