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

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.