Variant array length

How can I determine the length of a variant array. If it contains data then this is no problem (Ubound - Lbound) but if it  has not been initialized then ubound returns subscript out of range. A work around is :

i = 0
On Error Resume Next
i = UBound(vIDs) - LBound(vIDs)
On Error GoTo 0
If i > 0 Then

but this seems quite inelegant. Is there
some better way ?
Who is Participating?
YellowSnowConnect With a Mentor Commented:
caraf_g is right but you can at least wrap the code into a function so you don't have to worry about it all the time:

Option Explicit
Public Enum ArrayIsEmptyReturnCodes
    NotAnArray = 1
    ArrayEmpty = 2
    ArrayNotEmpty = 3
End Enum
Private Sub Command1_Click()

Dim arr1 As String
Dim arr2() As String
Dim arr3(1) As String
Dim arr4() As String

MsgBox ArrayIsEmpty(arr1)
MsgBox ArrayIsEmpty(arr2)
MsgBox ArrayIsEmpty(arr3)

ReDim arr4(1 To 5)

MsgBox ArrayIsEmpty(arr4)

End Sub
Public Function ArrayIsEmpty(arr As Variant) As ArrayIsEmptyReturnCodes

Dim intLBound As Integer

If Not IsArray(arr) Then
    ArrayIsEmpty = NotAnArray
    Exit Function
End If

On Error Resume Next
intLBound = LBound(arr)
If Err.Number = 9 Then
    ArrayIsEmpty = ArrayEmpty
    ArrayIsEmpty = ArrayNotEmpty
End If

End Function

You are quite right, doing UBound on an empty array will cause an error 9: subscript out of range.

This is a VB limitation, and there is nothing that can be done about it. Sorry!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.