VBA array length (not ubound or onerror!)

Question

Sorry to ask such a basic question but this is driving me mad...

What function in VBA returns the number of elements in an array... i.e when the array is empty it will return 0?

I cant do this with UBound because it throws an error when called on an empty array and i cant believe the way to do this by using OnError to first determine if its empty or not... as is suggested on forums! array.Length complains about a bad qualifier or something.

I really need to do this:

dim termAry() as String
populate termAry
...

private sub populate(terms() as String)
   redim preserve terms(terms.Length) ' Redim takes ubound for array size
   terms(ubound(terms)) = "something really annoying"
end sub

P.S any good links to a concise set of VBA language and function reference would be most useful... MSDN seems really obscure!!!

what are you trying to do? you need a source for your array - where does it come from — , May 08 '13 at 11:28
See http://stackoverflow.com/questions/4097021/vba-handle-empty-array-error or http://stackoverflow.com/questions/206324/how-to-check-for-empty-array-in-vba-macro — Skip Intro, May 08 '13 at 11:29
all you need to handle this its to say `if ubound(arr) > 1 then ...`, for initialization use `redim arr(MAX)` — , May 08 '13 at 11:35
mehow, ubound throws an exception if array has no elements, what would be nice is a Length(array) function that returns a value between between 0 and Ubound(array)+1. — user2361340, May 08 '13 at 12:17
see http://stackoverflow.com/questions/10559804/vba-checking-for-empty-array — collapsar, May 08 '13 at 12:51

Joe · Accepted Answer · 2013-05-08T13:17:44.043

I believe the only way to do this is to use On Error and handle the Subscript Out of Range error that will be raised if the array (or the dimension of the array you're interested in) isn't initialized.

E.g.

Public Function IsInitialized(arr() As String) As Boolean
    On Error GoTo ErrHandler
    Dim nUbound As Long
    nUbound = UBound(arr)
    IsInitialized = True
    Exit Function
ErrHandler:
    Exit Function
End Function

Dim a() As String
Dim b(0 To 10) As String

IsInitialized(a) ' returns False
IsInitialized(b) ' returns True

You can generalized this to test how many dimensions there are in an array, e.g.

Public Function HasAtLeastNDimensions(arr() As String, NoDimensions As Long) As Boolean
    On Error GoTo ErrHandler
    Dim nUbound As Long
    nUbound = UBound(arr, NoDimensions)
    HasAtLeastNDimensions = True
    Exit Function
ErrHandler:
    Exit Function
End Function

Dim a() As String
Dim b(0 To 10) As String
Dim c(0 To 10, 0 To 5) As String

HasAtLeastNDimensions(a, 1) ' False: a is not initialized
HasAtLeastNDimensions(b, 1) ' True: b has 1 dimension
HasAtLeastNDimensions(b, 2) ' False: b has only 1 dimension
HasAtLeastNDimensions(c, 2) ' True: c has 2 dimensions

UPDATE

In response to comment:

am i right in thinking that the function cannot be easily generalised to operate on any array type

It can be easily generalized by making the parameter a Variant, and checking it is an array in the body of the function using the IsArray function:

Public Function HasAtLeastNDimensions(arr As Variant, NoDimensions As Long) As Boolean
    On Error GoTo ErrHandler
    Dim nUbound As Long
    If Not IsArray(arr) Then Exit Function
    nUbound = UBound(arr, NoDimensions)
    HasAtLeastNDimensions = True
    Exit Function
ErrHandler:
    Exit Function
End Function

That works fine for string arrays, am i right in thinking that the function cannot be easily generalised to operate on any array type... it seems a deep copy is required with element wise type casting? — user2361340, May 08 '13 at 12:35

VBA array length (not ubound or onerror!)

1 Answers1