阵列vba的最高填充维度。-Highestpopulateddimensionofanarrayvba

3  

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.

如果数组被初始化，您可以计算默认值并从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.

还有其他的想法，但你需要更清楚地定义这个问题，才能给我们更好的回答的机会。最好的一个例子。

2  

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.

我建议在填充数组时使用ReDim保存。这样上行线对你来说会很好。只有多维数组中的最后一个维度可以重命名。

1  

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.

您可以考虑使用包含数组变量的自定义类，代码向数组添加项，该数组还会修改另一个变量以跟踪“使用”的索引号。然后可以包含一个函数来返回存储的“最高索引”号。

1  

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

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

要获得数组中填充率最高的元素，我将使用a For.。使用IsEmpty函数执行下一个循环。

(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:

关于@jtolle的答案，Count和CountA工作表函数都不能工作:

    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.

另一方面，如果我的“实际”目标是对数组中的每个元素做一些事情，我将会使用。每个带有IsEmpty函数的循环。

    '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
• IsEmpty函数
• UBound Function
• UBound函数
• For...Next Statement
• 为…下一个语句

-1  

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.

请注意:这在excel365中是错误的，在excel2000中可能是错误的。看到评论。但是我把它作为使用Application.WorksheetFunction()的一个示例，它来自VBA，它并不总是做与在工作表中使用公式完全相同的事情。

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.

它将输出'2' [*No，它将输出3 -请参见注释**]，因为第三个元素的默认值为'Empty'。(注意，必须将数组声明为类型变体，这样才能工作，但它可以包含类型String的元素。如果你的数组是字符串类型的，那么没有空的!当然，这个假设你知道你所有的空结点都在最后，因为它只是计数，而不是返回一个索引。出于同样的原因，您必须知道在二维数组中值的分布，才能使其有意义。

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

我想这就是你想要的，基于你的编辑。