Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining number of values in an array (2 related questions)
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining number of values in an array (2 related questions)
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining number of values in an array (2 related questions)
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determining number of values in an array (2 related questions)
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete row formula changes array range on related sheet | Excel Worksheet Functions | |||
2 questions! Related to combobox and time function. HELP!! | Excel Discussion (Misc queries) | |||
Summing Related Values | Excel Discussion (Misc queries) | |||
Determining Array Limit | Excel Programming | |||
2 questions related to Excel under Windows 98/2000 | Excel Programming |