VBA array length (not ubound or onerror!)

Question

Sorry to ask such a basic question, but it drives me crazy ...

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

I cannot do this with UBound because it throws an error when called in an empty array, and I cannot believe that this can be done using OnError to first determine if it is empty or not ... as suggested on forums! array.Length is complaining about a bad qualifier or something like that.

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 

PS any useful links to a compressed set of VBA and function links would be most useful ... MSDN seems really obscure !!!

Answer 1

I believe that the only way to do this is to use On Error and handle the Subscript Out of Range error, which will be raised if the array (or the size of the array of interest to you) is not initialized.

eg.

 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 generalize this to check how many dimensions exist in the 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 the comment:

It seems to me that a function cannot be easily generalized to work with any type of array

It can be easily generalized by specifying a Variant parameter and checking it as an array in the function body 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