generating a count of items in an array

[muyBN]

I have an array (shown below as "varArr") in which a macro searches for terms
in the array ("text1", etc.) with a while statement. I occasionally add or
delete a term from the array. Is there any way of using UBound or something
else, to pick up the quantity of terms to be used in the array, so that I can
use a variable for that quantity instead of hard-coding it each time I add or
delete a term from the list?

varArr = Array("text1", "text2", "text3", "text4")
While intCnt < 5
~~

 

[Jezebel]

For intCnt = lBound(varArr) to uBound(varArr)
...
or

intCnt = lBound(varArr)
Do
..
Loop until intCnt > uBound(varArr)

Don't use For ... Next if you add or remove items within the loop.


If you do a lot of this sort of thing, you might find it easier to work with
a collection.

 

[Michael Bednarek]

I have an array (shown below as "varArr") in which a macro searches for terms
in the array ("text1", etc.) with a while statement. I occasionally add or
delete a term from the array. Is there any way of using UBound or something
else, to pick up the quantity of terms to be used in the array, so that I can
use a variable for that quantity instead of hard-coding it each time I add or
delete a term from the list?

varArr = Array("text1", "text2", "text3", "text4")
While intCnt < 5
~~

Your code fragment is very terse to allow deduction of what you want to
do. However, in addtion to Jezebel's example of how to use
LBound/UBound, you can also try this construct:

Dim varElement as Variant
For Each varElement In varArray
Debug.Print varElement
Next varElement