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

--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

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

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

--
The enclosed questions or comments are entirely mine and don't represent
the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

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)


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

--
The enclosed questions or comments are entirely mine and don't represent
the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

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

"KR" wrote:

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

--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.