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.

Hi
NumValues = ubound(Users)

--
Regards
Frank Kabel
Frankfurt, Germany


Craig & Co. wrote:
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.

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

NumValues = UBound(Users) + 1

KL

"Frank Kabel" wrote in message
...
Hi
NumValues = ubound(Users)

--
Regards
Frank Kabel
Frankfurt, Germany


Craig & Co. wrote:
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.

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


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Craig & Co." skrev i en meddelelse
...
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.

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

--

HTH

RP

"KL" wrote in message
...
A small correction, you need to add 1 as the first Item is 0:

NumValues = UBound(Users) + 1

KL

"Frank Kabel" wrote in message
...
Hi
NumValues = ubound(Users)

--
Regards
Frank Kabel
Frankfurt, Germany


Craig & Co. wrote:
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.

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
--
Regards,
Tom Ogilvy

"Leo Heuser" wrote in message
...
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


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Craig & Co." skrev i en meddelelse
...
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.

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

--
Best Regards
Leo Heuser

"Tom Ogilvy" skrev i en meddelelse
...
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
--
Regards,
Tom Ogilvy

"Leo Heuser" wrote in message
...
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


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Craig & Co." skrev i en meddelelse
...
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.