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
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
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.