The number of elements in an array of one-dimensional options in excel

Question

Is this code correct for determining the number of elements in an array of one-dimensional options in Excel VBA? Suppose I have an array option called Array1 with k elements.

Dim n as Integer n = UBound(Array1) 

Answer 1

To get an accurate score, you need to do UBound - LBound + 1 . This is due to the fact that arrays should not go from index 1 to n, they can start at any desired index. Here's an example where it goes from 3 to 7, which makes 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 

Answer 2

Try "CountA"

 Dim myArray(1 to 10) as String Dim arrayCount as String arrayCount = Application.CountA(myArray) Debug.Print arrayCount