Here's an IsValidArray() function you could copy and paste.
Run the demo subprocedure to see how you can use the function.
Public Function IsValidArray(vntV As Variant) As Boolean
' This function returns TRUE if the incoming
' variant, vntV, contains an array with elements;
' otherwise, returns FALSE.
' This function works by examining whether
' an error is generated when the UBound function
' is used on vntV.
Dim lngUBound As Long
On Error Resume Next
lngUBound = UBound(vntV)
IsValidArray = (Err.Number = 0)
End Function
Private Sub DemoIsValidArray()
' Run this subprocedure to demonstrate the
' IsValidArray() function.
Dim aryA() As String
Dim lngL As Long
Dim objO As Object
Dim i As Long
Dim fRetVal As Boolean
Debug.Print
Debug.Print "RETURN VALUES FROM THE IsValidArray() FUNCTION:"
' Here aryA() doesn't yet have elements,
' so IsValidArray() returns FALSE:
fRetVal = IsValidArray(aryA)
Debug.Print "Uninitialised Array:", fRetVal
' This code won't run:
If fRetVal = True Then
For i = LBound(aryA) To UBound(aryA)
Debug.Print " " & aryA(i)
Next
End If
' Here aryA() is given elements (and data)
' so IsValidArray() returns TRUE and data
' is sent to Immediate window:
ReDim aryA(1 To 2)
aryA(1) = "A"
aryA(2) = "B"
fRetVal = IsValidArray(aryA)
Debug.Print "Initialised Array:", fRetVal
If fRetVal = True Then
For i = LBound(aryA) To UBound(aryA)
Debug.Print " " & aryA(i)
Next
End If
' These variables aren't arrays,
' so IsValidArray() returns FALSE:
fRetVal = IsValidArray(lngL)
Debug.Print "Long:", , fRetVal
fRetVal = IsValidArray(objO)
Debug.Print "Object:", , fRetVal
Debug.Print
MsgBox "Output has been sent to the Immediate window." _
& vbNewLine _
& "Press CTRL+G in the VBA editor to view."
End Sub
Regards
Geoff
