| Microsoft Office 2000/Visual Basic Programmer's Guide | |
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.