19

Is This code correct for determining the number of elements in a single dimension variant array in Excel VBA. Supposing I have a variant array named Array1 with k elements.

Dim n as Integer
n = UBound(Array1)
Community
  • 1
  • 1
IntegrateThis
  • 853
  • 2
  • 16
  • 39
  • 2
    I think you need to +1 assuming base 0. – findwindow May 09 '16 at 20:18
  • 3
    No, `UBound` returns only the upper bound, meaning the highest index of the selected dimension. It says nothing of the number of elements unless you're sure the array has `Base 1`. See tigeravatar's answer for how to count the elements. – Vegard May 10 '16 at 11:29
  • Does this answer your question? [Get length of array?](https://stackoverflow.com/questions/30574814/get-length-of-array) – chaotic3quilibrium Aug 02 '21 at 14:25

2 Answers2

36

To get an accurate count, you need to do UBound - LBound + 1. This is because arrays don't have to go from index 1 to n, they can start at basically any index you want. Here's an example where it goes from 3 to 7, which is a total of 5 elements (3, 4, 5, 6, and 7):

Sub tgr()

    Dim Array1(3 To 7) As Variant
    Dim lNumElements As Long

    lNumElements = UBound(Array1) - LBound(Array1) + 1
    MsgBox lNumElements

End Sub
tigeravatar
  • 26,199
  • 5
  • 30
  • 38
  • 1
    This solution will fail when a dynamic `Array` has not yet be `Redim`ed to a non-zero size. Here's how to robustly get the size: https://stackoverflow.com/a/68614881/501113 – chaotic3quilibrium Aug 02 '21 at 14:25
0

Typically, you need the number of elements when looping through them using a For loop. In this case, the most straight forward way is to write

For i = LBound(A) To UBound(A)
    Debug.Print "A(" & i & ") = " & A(i)
Next i
NDM
  • 509
  • 5
  • 4