Finding Upper Bound Of Array

[Guest]

Silly question of the day....

I was wondering how you programatically find the "upper bound" of an array
(i.e, count of elements in an array). I've tried to find examples but
everywhere I look the number of elements is defined in the code by the
programmer.

Here is my sample code:

Dim varData As Variant
varData = Array("apples", "oranges", "loquats")
vLimit = <<<upper bound>>>
For vCount = 0 To vLimit
'' processing
Next

I would like to plug the right code into <<<upper bound>>> to find the
number of elements I need to loop through.

The only other thing I can think of is to loop through the array until I get
an error.

Either way, any tips?

Thanks!

Janet Panighetti

 

[Rick Rothstein \(MVP - VB\)]

See two inline comments below...

I was wondering how you programatically find the "upper bound" of an array
(i.e, count of elements in an array). I've tried to find examples but
everywhere I look the number of elements is defined in the code by the
programmer.

Here is my sample code:

Dim varData As Variant
varData = Array("apples", "oranges", "loquats")
vLimit = <<<upper bound>>>

vLimit = UBound(varData)

For vCount = 0 To vLimit

However, note that UBound is not a count of elements. Being that the LBound
(another function that can be used on arrays), lower bound, is 0, vLimit
will be one greater than the count of elements. So, change the above line
to...

For vCount = 0 To vLimit - 1


Rick

 

[JE McGimpsey]

One way:

Dim varData As Variant
Dim vCount As Long

varData = Array("apples", "oranges", "loquats")

For vCount = LBound(varData) To UBound(varData)
'' processing
Next vCount

 

[Rick Rothstein \(MVP - VB\)]

One way:

Dim varData As Variant
Dim vCount As Long

varData = Array("apples", "oranges", "loquats")

For vCount = LBound(varData) To UBound(varData)
'' processing
Next vCount

Or do away with counting altogether...

Dim V As Variant
For Each V In Array("apples", "oranges", "loquats")
MsgBox V
Next

Rick

 

[Guest]

Thanks!

If I do vLimit-1, I loose my loquats.

This works!!!!!!!!!!

 

[Guest]

Great! Thanks!

One way:

Dim varData As Variant
Dim vCount As Long

varData = Array("apples", "oranges", "loquats")

For vCount = LBound(varData) To UBound(varData)
'' processing
Next vCount

 

[Rick Rothstein \(MVP - VB\)]

If I do vLimit-1, I loose my loquats.

Do you have Option Base set to 1?

This works!!!!!!!!!!

Works? I thought you just said it removes your last entry. In any event, see
JE McGimpsey's post in this thread and my response to it, both of which give
you a way to process the array elements without having to worry about
element counts or Option Base settings.

Rick

 

[Guest]

Even Better!!!!

Or do away with counting altogether...

Dim V As Variant
For Each V In Array("apples", "oranges", "loquats")
MsgBox V
Next

Rick

 

[Guest]

It works = I get loquats if I don't -1.

Do you have Option Base set to 1?


Works? I thought you just said it removes your last entry. In any event, see
JE McGimpsey's post in this thread and my response to it, both of which give
you a way to process the array elements without having to worry about
element counts or Option Base settings.

Rick

 

[Rick Rothstein \(MVP - VB\)]

This works!!!!!!!!!!
My fault... yes, ignore the suggestion to put -1 against the vLimit... I was
thinking of a different possible problem (dealing with the difference
between zero-based and one-based counting) which did not apply to your
situation as you set it up. Sorry for any confusion and I am glad you saw
your way around my mis-advice on that one issue.

Rick

 

[Harlan Grove]

...
....

Or do away with counting altogether...

Dim V As Variant
For Each V In Array("apples", "oranges", "loquats")
MsgBox V
Next

Fine if you're only dereferencing the array. Not so good when you need
to modify the array's entries.

 

[Rick Rothstein \(MVP - VB\)]

Or do away with counting altogether...

Fine if you're only dereferencing the array. Not so good when
you need to modify the array's entries.

Yes, I probably should have mentioned that. You are seeing my compiled VB
roots showing here... I only use the Array function for constant values,
similar to a replacement for the old BASIC's READ/DATA combinations, because
of the Variant data type underpinnings for this function. In the compiled VB
world, Variants are frowned on for heavy duty work because they tend to be
slow and are memory hogs compared to declared non-Variant Data type
variables. How are Variants viewed here in the VBA world of Excel?

Rick

 

[Harlan Grove]

...
....

How are Variants viewed here in the VBA world of Excel?

All udfs should be declared as returning variant type since that
allows returning numbers, strings, booleans, dates/times, AND error
values. UDF arguments could be of specific type, but there are few
situations in which ONLY an array or ONLY a range argument would be
acceptable but not the other. If an argument could be either a range
or an array, then it must be passed as a variant and its actual type
resolved within the udf.

Then there are numeric strings, e.g., "123". Since many if not most
built-in functions accept scalar text arguments and convert them to
numbers when possible, an argument could be made that udfs should do
the same.

Besides, the Excel-to-VBA-and-back interface is so slow that using
variants rather than specific types adds negligible additional
processing time.

 

[Rick Rothstein \(MVP - VB\)]

Thank you very much for that explanation... much appreciated.

Rick