Number of Items in an Array.

[Craig & Co.]

Hi,

How do I find out the number of items in an array, so that I can loop check
the values in the array with the incoming data.

Dim Users as Variant
Dim NumValues, Counter as Integer
Users = Array("Bob","Fred")

NumValues = Users

For Counter = 0 to NumValues
CheckUser = Users(Counter)
If CheckUser = IncomingUser then
Print "Cool" - okay this is just pseudo code.
Else
Print "Not Cool"
End If
next

Cheers
Craig.

 

[Frank Kabel]

Hi
NumValues = ubound(Users)

 

[KL]

A small correction, you need to add 1 as the first Item is 0:

NumValues = UBound(Users) + 1

KL

 

[Leo Heuser]

Hi Craig

NumValues = Ubound(Users) - (Lbound(Users)=0)

The reason for subtracting is, that TRUE in VBA is -1,
so if Lbound(Users) is 0, 1 is added ( -(-1) = +1) to
Ubound(Users).
In contrast, TRUE in Excel is 1 (positive one)

Normally you would loop the array with:

For Counter = Lbound(Users) to Ubound(Users)
etc.

Please notice, that

Dim NumValues, Counter as Integer

will dimension NumValues as Variant. Each variable
must be dimensioned explicitly:

Dim NumValues as Integer, Counter as Integer

 

[Bob Phillips]

That depends upon the array lower bound. To be absolutely sure, you could
use

For Counter = LBound(Users,1) To UBound(Users,1)

caters for most options

 

[Tom Ogilvy]

since arrays are not limited to 0 or 1 for a lower bound it might be better
to use the old tried and true

Ubound(users) - Lbound(users) + 1

Sub Tester2()
Dim Users(6 To 12)
Debug.Print UBound(Users) - (LBound(Users) = 0)
Debug.Print UBound(Users) - LBound(Users) + 1
End Sub

Produced:
12
7

 

[Leo Heuser]

I gave my answer, because the OP's example used the
Array function, but of course you're correct, that
Ubound(users) - Lbound(users) + 1
is the general way of getting the number of elements in an array.
Most of the time, I use it myself