return array from function in VBA return array from function in VBA vba vba

return array from function in VBA

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 = returnValend functionSub mysub()   Dim myArray() As Integer   myArray = getStats()   msgbox myArray(3)end sub 

Function getStats() As Variant

getStats is now an Array and not an Integer

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 sEnd SubFunction 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 = returnValEnd 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.