Returning an Array from a FunctionThis content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
The previous example assigned one array variable to another. Based on this example, you might guess that you can also call a procedure that returns an array and assign that to another array, as in the following code fragment:
Dim astr1() As String astr1 = ReturnArray
To return an array, a procedure must have a return value type of the array's data type, or of type Variant. The advantage to declaring a procedure to return a typed array versus a Variant value is that you are not required to use the IsArray function to ensure that the procedure indeed returned an array. If a procedure returns a value of type Variant, you might want to check its contents before performing array operations.
The ReturnArray procedure prompts the user for input and creates an array of the resulting values, resizing the array as required. Note that to return an array from a procedure, you simply assign the array to the name of the procedure.
Function ReturnArray() As String() ' This function fills an array with user input, then ' returns the array. Dim astrItems() As String Dim strInput As String Dim strMsg As String Dim lngIndex As Long On Error GoTo ReturnArray_Err strMsg = "Enter a value or press Cancel to end:" lngIndex = 0 ' Prompt user for first item to add to array. strInput = InputBox(strMsg) If Len(strInput) > 0 Then ' Estimate size of array. ReDim astrItems(0 To 2) astrItems(lngIndex) = strInput lngIndex = lngIndex + 1 Else ' If user cancels without adding item, ' don't resize array. ReturnArray = astrItems GoTo ReturnArray_End End If ' Prompt user for additional items and add to array. Do strInput = InputBox(strMsg) If Len(strInput) > 0 Then astrItems(lngIndex) = strInput lngIndex = lngIndex + 1 End If ' Loop until user cancels. Loop Until Len(strInput) = 0 ' Resize to current value of lngIndex - 1. ReDim Preserve astrItems(0 To lngIndex - 1) ReturnArray = astrItems ReturnArray_End: Exit Function ReturnArray_Err: ' If upper bound is exceeded, enlarge array. If Err = ERR_SUBSCRIPT Then ' Subscript out of range ' Double the size of the array. ReDim Preserve astrItems(lngIndex * 2) Resume Else MsgBox "An unexpected error has occurred!", vbExclamation Resume ReturnArray_End End If End Function
When you call a procedure that returns an array, you must take into account the case in which the returned array does not contain any elements. For example, in the preceding ReturnArray procedure, if you cancel the input box the first time that it appears, the array returned by the procedure contains no elements. The calling procedure must check for this condition. The best way to do this is to define a procedure such as the following one, which takes an array and checks the upper bound. If the array contains no elements, checking the upper bound causes a trappable error.
Function IsArrayEmpty(varArray As Variant) As Boolean ' Determines whether an array contains any elements. ' Returns False if it does contain elements, True ' if it does not. Dim lngUBound As Long On Error Resume Next ' If the array is empty, an error occurs when you ' check the array's bounds. lngUBound = UBound(varArray) If Err.Number <> 0 Then IsArrayEmpty = True Else IsArrayEmpty = False End If End Function
Note The VBA Split and Filter functions can also return an array that contains no elements. Checking the upper or lower bounds on an array returned by either of these procedures does not cause an error, however. When the Split or Filter function returns an array containing no elements, the lower bound of that array is 0, and the upper bound is -1. Therefore, to determine whether the returned array contains any elements, you can check for the condition where the upper bound of the array is less than the lower bound.
Understanding Arrays | Creating Arrays | Arrays and Variants | Assigning One Array to Another | Passing an Array to a Procedure | Sorting Arrays | Using the Filter Function to Search String Arrays | Using a Binary Search Function to Search Numeric Arrays | Searching a Dictionary