VBA: How can I check if an array is declaired with a dimension?

[M Shafaat]

Hi!
In VBA, how can I check if an array is declaired with a dimension?



Example:

Dim MyArray() As String



No one of the following works:



IsEmpty(MyArray) Then

Not IsNull(MyArray) Then

MyArray Is Nothing Then



Best regards
M Shafaat

 

[Steve Rindsberg]

Hi!
In VBA, how can I check if an array is declaired with a dimension?

Example:

Dim MyArray() As String

No one of the following works:

IsEmpty(MyArray) Then

Not IsNull(MyArray) Then

MyArray Is Nothing Then

Try using something like the ArrayEmpty function below.
You may want to add further tests because, if I recall correctly, some versions
of Office won't throw an error when you Lbound or Ubound check an empty array.



Sub TestMe()
Dim MyArray() As String
'ReDim MyArray(0 To 0) As String
If ArrayEmpty(MyArray) Then
Debug.Print "It's empty"
Else
Debug.Print "It's full"
End If

End Sub

Function ArrayEmpty(vArray As Variant)
On Error GoTo errorhandler
Dim x As Long

x = LBound(vArray)
x = UBound(vArray)

normalexit:
Exit Function
errorhandler:
ArrayEmpty = True

End Function