CODEWITHSUNDEEP
excelvba
IntegrateThis
IntegrateThis

Reputation: 962

Number of elements in a single dimension variant array in excel

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)

Upvotes: 19

Views: 102002

Answers (2)

NDM
NDM

Reputation: 547

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

Upvotes: 0

tigeravatar
tigeravatar

Reputation: 26660

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

Upvotes: 36

Related Questions