Determining number of values in an array (2 related questions)

[KR]

Using XL2003 on Win2000

I'm setting up a workbook for other folks to use; they may edit the number
of elements in several arrays (which I've hardcoded at the top of the module
so they can find them):

MyArrayOfStudentNames =Array("Name1", "Name2", "Name3")
MyArrayOfStudentGradeLevel =Array("7", "3", "12")

Here's my first problem; I want to allow them to declare/fill a
one-dimensional array of _numbers_. I /could/ have them put each number in
quotes, then pull the value out in code- but that wastes processing power.
Is there any way to declare a one-dimensional array by populating it with
numbers? The following _doesn't_ work because it appears to create a
multidimensional array with three dimensions of (0 to 7, 0 to 3, and 0 to
12):

MyArrayOfStudentGradeLevel =Array(7, 3, 12)

Then my second problem comes into play;

Since I don't know how many items will be in the one-dimensional array (once
I learn how to dimension it), my code needs to use each value then stop
after the last one without erroring. I was going to use a loop:

For each StudentGradeLevel = 1 to 10
'do stuff
Next

but in the above example, only 3 items exist, so the array is actually
dimensioned as a (1 to 3) or (0 to 2) array; my loop will error out because
my array isn't that large. To code defensively, how do I determine how many
items are in the array first, so I can make that the maximum of my loop?
Items may not be sequential, so what I really need is the maximum array
value regardless of whether it is filled (e.g. could be Array(3,5, ,12,6, )
and should return that there are 6 items to be looped through)

Thanks bunches and bunches in advance,
Keith

 

[Rob Bovey]

Hi Keith,

<<The following _doesn't_ work because it appears to create a
multidimensional array with three dimensions of (0 to 7, 0 to 3, and 0 to
12):
MyArrayOfStudentGradeLevel =Array(7, 3, 12)>>

That code creates a 1D Variant array (0 to 2) when I run it here. Not
sure why you're seeing different behavior.

<<my code needs to use each value then stop after the last one without
erroring. I was going to use a loop:
For each StudentGradeLevel = 1 to 10
'do stuff
Next>>

This is the best way to handle looping arrays of any size:

Dim lIndex As Long
For lIndex = LBound(MyArray) To UBound(MyArray)
'do stuff
Next lIndex

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[Bob Phillips]

MyArrayOfStudentGradeLevel =Array(7, 3, 12) creates a single dimensioned
array with 3 elements of 7, 3 and 12, I don't see how you get what you see.

You can do

For StudentGradeLevel = LBound(MyArrayOfStudentGradeLevel) To
UBound(MyArrayOfStudentGradeLevel )

to dynamically work through an array.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Look up the Redim function. It should cover everything that you want here. Be
sure to take note of the Preserve key word with a Redim...

HTH