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.

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.

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.

Highest populated element of an array

You could step backwards through a loop until you find the first array element that isn't empty.
This is the equivalent of the "last" populated element.

Short answer:

Function HighestPopIdx(ByRef arr) As Long
    For HighestPopIdx = UBound(arr) To LBound(arr) Step -1
        If Not IsEmpty(arr(HighestPopIdx)) Then Exit Function
    Next
End Function

Longer answer:

To get the Highest populated element of an array, I would use a For..Next loop with the IsEmpty function.

(This example is a tad over-complicated, for the sake of demonstration.)

Sub demo() '(Hit CTRL+G in the VBE to view the output in the Immediate window.)

    'Make a test ARRAY of 5 elements:
    Dim arr(1 To 5)

    Erase arr '(just to make sure the test is starting 'fresh')

    arr(1) = "X"
    arr(2) = 99
    arr(4) = "Y" '3 of the 5 elements have data (highest one is 4)

    Dim elIdx As Long, highestPopEl As Long
    For elIdx = LBound(arr) To UBound(arr)
        If Not IsEmpty(arr(elIdx)) Then highestPopEl = elIdx
    Next elIdx

    Debug.Print "The highest populated element index is " & highestPopEl 'Returns 4

End Sub

...or in reverse (first example could be quicker depending on your situation.

Other notes:

Further to @jtolle's answer, neither Count nor CountA worksheet functions will work:

    With Application.WorksheetFunction
        Debug.Print "Array WS CountA", .CountA(arr) 'returns 5 (Counts cells)
        Debug.Print "Array WS Count", .Count(arr) 'returns 1 (Counts numbers)
    End With

...and further to the comments, showing that arrays & cell ranges function similarly but not the same:

    'Make a test RANGE of 5 cells
    Dim rge As Range
    Set rge = Range("A1:A5")

    rge.Clear '(maker sure we're starting fresh)

    Range("A1") = "X"
    Range("A2") = 99
    Range("A4") = "Y" '3 of the 5 cells have data

    With Application.WorksheetFunction        
        Debug.Print "Range WS CountA", .CountA(rge) 'returns 3 (Counts values)
        Debug.Print "Range WS Count", .Count(rge)   'returns 1 (Counts numbers)
    End With

   '... and the VBA [Range.Count] method behaves differently:
    Debug.Print "Range VBA .Count", rge.Count 'returns 5 (Counts cells)

On the other hand if my "actual" goal was to do something to each element in an array, I would use For..Each loop with the IsEmpty function.

    'if my actual purpose is to ***do something*** to an ARRAY...
    Debug.Print: Debug.Print "Array [For..Next] :"
    Dim elCnt As Long, el

    For Each el In arr
        If Not IsEmpty(el) Then _
            elCnt = elCnt + 1: Debug.Print "#" & elCnt & ": Element value =", el
    Next el    ' (returns "X", 99, "Y")

    '...and the identical method can be used on a RANGE of cells
    Debug.Print: Debug.Print "Range [For..Next] :"
    Dim clCnt As Long, cl

    For Each cl In rge
        If Not IsEmpty(cl) Then _
            clCnt = clCnt + 1: Debug.Print "#" & clCnt & ": Cell value =", cl
    Next cl    ' (returns "X", 99, "Y")


End Sub

Complete Output:

The highest populated element index is 4.

Array WS CountA              5 
Array WS Count               1 
Range WS CountA              3 
Range WS Count               1 
Range VBA .Count             5 

Array [For..Next] :
#1: Element value =         X
#2: Element value =          99 
#3: Element value =         Y

Range [For..Next] :
#1: Cell value =            X
#2: Cell value =             99 
#3: Cell value =            Y

More Info:

• IsEmpty Function
• UBound Function
• For...Next Statement

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.

Note from 2018: This is wrong in Excel 365, and was probably wrong in Excel 2000. See the comments. But I'm leaving it up as an example of how using Application.WorksheetFunction() from VBA isn't always exactly doing the same thing as using a formula in the worksheet.

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' [*No, it prints 3 - See Comment***], 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.