The topic you requested is included in another documentation set. For convenience, it's displayed below. Choose Switch to see the topic in its original location.

Returning an Array from a Function

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

   Exit Function
   ' 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)
      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
      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.

See Also

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