| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining array size when it is dynamic
Hi,
At the beginning when you define an array do you have to specify a size or can you just leave the () empty and then it will hold however many items it holds (or can you redim it when you know). A bit rusty on arrays, help appreciatyed, regards, Mark |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Defining array size when it is dynamic
You can use:
dim myArr() as long 'variant/string... and redim it when you the dimensions. redim myArr(1 to 5, 7 to 9, -1 to 1) (as a weird example) Mark Stephens wrote: Hi, At the beginning when you define an array do you have to specify a size or can you just leave the () empty and then it will hold however many items it holds (or can you redim it when you know). A bit rusty on arrays, help appreciatyed, regards, Mark -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Defining array size when it is dynamic
Hi,
In this example, the array (saItems --- sa=string array in my shorthand) is redimensioned dynamically and only adds data from the cells in column three that are not blank : Dim saItems() as String Dim rCell as Range Dim lX as Long For Each rCell in ActiveSheet.UsedRange.Columns(3).Rows If rCell.FormulaR1C1 < "" Then lX = lX + 1 Redim Preserve saItems(lX) saItems(lX) = rCell.FormulaR1C1 End If Next "Preserve" causes the array to hold all items previously loaded into the array; without this keyword, the array would only hold one item, i.e. the last one loaded. HTH "Mark Stephens" wrote: Hi, At the beginning when you define an array do you have to specify a size or can you just leave the () empty and then it will hold however many items it holds (or can you redim it when you know). A bit rusty on arrays, help appreciatyed, regards, Mark |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Defining array size when it is dynamic
If rCell.FormulaR1C1 < "" Then
lX = lX + 1 Redim Preserve saItems(lX) saItems(lX) = rCell.FormulaR1C1 A ReDim Preserve operation is expensive. A better way is to ReDim the array to the largest possible size before doing anything with the array, fill the array with the apporpriate values, and then do a single ReDim Preserve at the end to reduce the size of the array to the actual used size. -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) "XP" wrote in message ... Hi, In this example, the array (saItems --- sa=string array in my shorthand) is redimensioned dynamically and only adds data from the cells in column three that are not blank : Dim saItems() as String Dim rCell as Range Dim lX as Long For Each rCell in ActiveSheet.UsedRange.Columns(3).Rows If rCell.FormulaR1C1 < "" Then lX = lX + 1 Redim Preserve saItems(lX) saItems(lX) = rCell.FormulaR1C1 End If Next "Preserve" causes the array to hold all items previously loaded into the array; without this keyword, the array would only hold one item, i.e. the last one loaded. HTH "Mark Stephens" wrote: Hi, At the beginning when you define an array do you have to specify a size or can you just leave the () empty and then it will hold however many items it holds (or can you redim it when you know). A bit rusty on arrays, help appreciatyed, regards, Mark |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Defining array size when it is dynamic
Thanks for the tip. I didn't know about the cost... Relatively speaking, how costly is it? Going further and given the example, is it less costly to dim the array to 65536 right up front, or to redim on the fly (as I did), or to count the number of blanks first, then dim the array once (even though it would require a little more code)? "Chip Pearson" wrote: If rCell.FormulaR1C1 < "" Then lX = lX + 1 Redim Preserve saItems(lX) saItems(lX) = rCell.FormulaR1C1 A ReDim Preserve operation is expensive. A better way is to ReDim the array to the largest possible size before doing anything with the array, fill the array with the apporpriate values, and then do a single ReDim Preserve at the end to reduce the size of the array to the actual used size. -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) "XP" wrote in message ... Hi, In this example, the array (saItems --- sa=string array in my shorthand) is redimensioned dynamically and only adds data from the cells in column three that are not blank : Dim saItems() as String Dim rCell as Range Dim lX as Long For Each rCell in ActiveSheet.UsedRange.Columns(3).Rows If rCell.FormulaR1C1 < "" Then lX = lX + 1 Redim Preserve saItems(lX) saItems(lX) = rCell.FormulaR1C1 End If Next "Preserve" causes the array to hold all items previously loaded into the array; without this keyword, the array would only hold one item, i.e. the last one loaded. HTH "Mark Stephens" wrote: Hi, At the beginning when you define an array do you have to specify a size or can you just leave the () empty and then it will hold however many items it holds (or can you redim it when you know). A bit rusty on arrays, help appreciatyed, regards, Mark |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Defining array size when it is dynamic
Dear Chip, Dave and HTH,
Thank you all for your help, Iit has all come folooding back and I now feel competent with arrays again. FYI I am going to adopt a combination of all your suggestions and count the number of cells in the column (which is the number of items in my single dimension array) and then do one redim of the array, thank you all once more, kind regards, Mark "XP" wrote in message ... Thanks for the tip. I didn't know about the cost... Relatively speaking, how costly is it? Going further and given the example, is it less costly to dim the array to 65536 right up front, or to redim on the fly (as I did), or to count the number of blanks first, then dim the array once (even though it would require a little more code)? "Chip Pearson" wrote: If rCell.FormulaR1C1 < "" Then lX = lX + 1 Redim Preserve saItems(lX) saItems(lX) = rCell.FormulaR1C1 A ReDim Preserve operation is expensive. A better way is to ReDim the array to the largest possible size before doing anything with the array, fill the array with the apporpriate values, and then do a single ReDim Preserve at the end to reduce the size of the array to the actual used size. -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) "XP" wrote in message ... Hi, In this example, the array (saItems --- sa=string array in my shorthand) is redimensioned dynamically and only adds data from the cells in column three that are not blank : Dim saItems() as String Dim rCell as Range Dim lX as Long For Each rCell in ActiveSheet.UsedRange.Columns(3).Rows If rCell.FormulaR1C1 < "" Then lX = lX + 1 Redim Preserve saItems(lX) saItems(lX) = rCell.FormulaR1C1 End If Next "Preserve" causes the array to hold all items previously loaded into the array; without this keyword, the array would only hold one item, i.e. the last one loaded. HTH "Mark Stephens" wrote: Hi, At the beginning when you define an array do you have to specify a size or can you just leave the () empty and then it will hold however many items it holds (or can you redim it when you know). A bit rusty on arrays, help appreciatyed, regards, Mark |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Defining Font Size in VBA | Excel Discussion (Misc queries) | |||
| Creating a Dynamic Array from list that may change in size | Excel Programming | |||
| help defining dynamic range | Charts and Charting in Excel | |||
| Defining Dynamic Range | Excel Programming | |||
| size of multidimensional dynamic array | Excel Programming | |||
Linear Mode
