Microsoft Office 2000/Visual Basic Programmer's Guide   

Returning an Array from a Function

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 don't need to use the IsArray function to ensure that the procedure indeed returned an array. If a procedure returns a value of type Variant, you may need 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 needed. 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

To test the ReturnArray procedure, you can run the GetArray procedure, available in the modArrays module in VBA.mdb in the ODETools\V9\Samples\OPG\Samples\CH07 subfolder on the Office 2000 Developer CD-ROM.

When you call a procedure that returns an array, you need to 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 needs to 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

The IsArrayEmpty procedure is available in the modArrays module in VBA.mdb in the ODETools\V9\Samples\OPG\Samples\CH07 subfolder on the Office 2000 Developer CD-ROM.

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. For an example, see the ConvertToProperCase procedure in the "Converting Strings" section earlier in this chapter.