Number of elements in an array

[Neal Zimm]

I have some arrays dim'd in the declarations procedure that are accessed by
different macros.

at times I'm lazy and get tired of looking back to see the (1 to ??) value.

when I loop thru string vars, I often use: for x = 1 to len(varname)

Is there a similar statement or property for fixed length arrays?
I looked thru help on array, dim, etc. and could not find it.
maybe there's a way to use .count ?

Thanks.

 

[Jim Thomlinson]

You are looking for UBound(myArray) to find the upper bound of the array.
There is also LBound which finds the lower bound. usually you can just use
zero but sometimes it is handy...

 

[Tom Ogilvy]

for i = lbound(vArr,1) to ubound(varr,1)


the 1 means the first dimension.

 

[Alan Beban]

Application.Count(arr), Application.CountA(arr) etc. work.

If you mean to count all the possible elements of a one-dimensional
array, without regard to the value (or emptiness) of the elements

numElements = LBound(arr) + UBound(arr) - 1

Alan Beban

 

[Neal Zimm]

Thanks Jim,
It gets me nuts to not be able to find items like this, and I looked thru
a lot of array stuff and Ubound was not mentioned, at least in the Excel help
sections that I looked at.

 

[Neal Zimm]

Thanks Tom,
I look thru a lot of array stuff, but never saw Ubound.
Neal Z

 

[Neal Zimm]

Thanks Alan,
I looked thru a lot of array stuff, but never saw Ubound.
Neal Z

 

[Giovanni D via OfficeKB.com]

Hi Sir this is Gio from Philippines...... it's my first time here in
this site... i am wondering how can i solve this problem in excel and please
help me.. I used excel 2000 in creating an inventory program in the hospital.
I used this excel inventorry program in our suppply room, i used one
worksheet per item. and i have almost 300 items in the supply room or almost
300 worksheets. I saved it as a template for all i know it is safer to save
it as template rather than saving it as ordinary excel files. The program was
working well, but not when i started linking(hyperlink) it from a certain
file that i always used. Then i have save it several times as a template but
i notice that the program malfuncitons, it doesnt compute the formulas i
created and some formulas are gone. Why is this happening. when i add some
items in the inventory it wouldnt add to the current balance, why is this
happening? Will you please help me, you wer the only people who can only
help me with this kind of problem......please....