Highest populated dimension of an array vba

Question

Say i have an single dimension array (to keep it simple). Is there a simple way to tell what the highest index of an element that was explicitly assigned a value? other than to loop through and count? I know Ubound finds the highest dimension of the array but that's not what I need. Is there something like Ubound but it only counts populated elements of an array, or the highest index populated?

My array contains strings. Also, what if the array is multidimensional.

I'm working in excel vba.

Answer 1

I'd suggest using ReDim Preserve as you populate the array. Thus UBound will work quite nicely for you. Only the last dimension in a multi dimensional array can be ReDimmed.

Answer 2

In a short word, no there is no in built mechanism to do this, but if you are looking for a way to hack it, then read on.

It depends on how you are filling up the array. If it is sequential, the easiest would be having a counter.

If it is randomized then you have a bigger challenge of finding the exact index of the last element. One idea, is to start from the maximum and start counting down.

If the array is initialized, you can count the default values and subtract from the ubound.

There are other ideas, but you need to more clearly defined the problem to give us a better chance of answering. Preferably with an example.

Answer 3

Of course, there's really nothing wrong with looping, but...

Say you have this:

Public Sub test()
    Dim a(1 To 3)

    a(1) = 1
    a(2) = "stuff"

    Debug.Print Application.WorksheetFunction.CountA(a)
End Sub

That will print '2', since the third element has a default value of 'Empty'. (Note that your array has to be declared as type Variant for this to work, but it can contain elements of type String. If your array is of type String, there aren't any empties!) And of course, this assumes that you know all your empties are at the end, since it's just counting, not actually returning an index. For the same reason, you'd have to know the distribution of your values in a 2-D array for this to be meaningful.

I think this is what you're looking for, based on your edit.

Answer 4

You might consider using a custom class that contains an array variable, code to add items to the array which would also modify another variable to track the "used" index number. Then you could include a function to return the stored "highest index" number.