Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array size
Could anyone please advice me with some Array-help (or
where to find the information): I need to have an array I got this far... Dim tempArray tempArray = Array() '(or tempArray = Array("") tempArray(0) = "someValue" tempArray(1) = "someOtherValue" And it crash on the tempArray(0) - or on tempArray(1) if I use tempArray = Array("") - because it's not defined. However, I don't want to specify the length of my Array, but have it dynamic. How can I achieve this? Thx! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array size
Sub tester3()
Dim tempArray(0 To 10) tempArray(0) = "someValue" tempArray(1) = "someOtherValue" MsgBox "TempArray, 0 = " & tempArray(0) & _ vbNewLine & "TempArray, 1 = " & tempArray(1) End Sub -- Regards, Tom Ogilvy "ten" wrote in message ... Could anyone please advice me with some Array-help (or where to find the information): I need to have an array I got this far... Dim tempArray tempArray = Array() '(or tempArray = Array("") tempArray(0) = "someValue" tempArray(1) = "someOtherValue" And it crash on the tempArray(0) - or on tempArray(1) if I use tempArray = Array("") - because it's not defined. However, I don't want to specify the length of my Array, but have it dynamic. How can I achieve this? Thx! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array size
Thx
However, I still need to have more dynamically size on this. I am doing a loop through a lot of data, and sometime sI need an array of only 1, sometimes I need 100. I guess that for time concideration to build an array with size 100 (which I use only a few times, and even not at all) would take a lot of memory. Are there really no way of dynamically changing the Array size during the program in VBA? Correct me if I am wrong. And/or please add more solutions if you have. -----Original Message----- Sub tester3() Dim tempArray(0 To 10) tempArray(0) = "someValue" tempArray(1) = "someOtherValue" MsgBox "TempArray, 0 = " & tempArray(0) & _ vbNewLine & "TempArray, 1 = " & tempArray(1) End Sub -- Regards, Tom Ogilvy "ten" wrote in message ... Could anyone please advice me with some Array-help (or where to find the information): I need to have an array I got this far... Dim tempArray tempArray = Array() '(or tempArray = Array("") tempArray(0) = "someValue" tempArray(1) = "someOtherValue" And it crash on the tempArray(0) - or on tempArray(1) if I use tempArray = Array("") - because it's not defined. However, I don't want to specify the length of my Array, but have it dynamic. How can I achieve this? Thx! . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array size
copied & pasted from the VBA help manual
Declaring a Dynamic Array By declaring a dynamic array, you can size the array while the code is running. Use a Static, Dim, Private, or Public statement to declare an array, leaving the parentheses empty, as shown in the following example. Dim sngArray() As Single Note You can use the ReDim statement to declare an array implicitly within a procedure. Be careful not to misspell the name of the array when you use the ReDim statement. Even if the Option Explicit statement is included in the module, a second array will be created. In a procedure within the array's scope, use the ReDim statement to change the number of dimensions, to define the number of elements, and to define the upper and lower bounds for each dimension. You can use the ReDim statement to change the dynamic array as often as necessary. However, each time you do this, the existing values in the array are lost. Use ReDim Preserve to expand an array while preserving existing values in the array. For example, the following statement enlarges the array varArray by 10 elements without losing the current values of the original elements. ReDim Preserve varArray(UBound(varArray) + 10) Note When you use the Preserve keyword with a dynamic array, you can change only the upper bound of the last dimension, but you can't change the number of dimensions. "ten" wrote in message ... Thx However, I still need to have more dynamically size on this. I am doing a loop through a lot of data, and sometime sI need an array of only 1, sometimes I need 100. I guess that for time concideration to build an array with size 100 (which I use only a few times, and even not at all) would take a lot of memory. Are there really no way of dynamically changing the Array size during the program in VBA? Correct me if I am wrong. And/or please add more solutions if you have. -----Original Message----- Sub tester3() Dim tempArray(0 To 10) tempArray(0) = "someValue" tempArray(1) = "someOtherValue" MsgBox "TempArray, 0 = " & tempArray(0) & _ vbNewLine & "TempArray, 1 = " & tempArray(1) End Sub -- Regards, Tom Ogilvy "ten" wrote in message ... Could anyone please advice me with some Array-help (or where to find the information): I need to have an array I got this far... Dim tempArray tempArray = Array() '(or tempArray = Array("") tempArray(0) = "someValue" tempArray(1) = "someOtherValue" And it crash on the tempArray(0) - or on tempArray(1) if I use tempArray = Array("") - because it's not defined. However, I don't want to specify the length of my Array, but have it dynamic. How can I achieve this? Thx! . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array size
dim temparray()
redim temparray(1 to 5) for i = 1 to 5 temparray(1) = i^2 Next redim preserve temparray(1 to 15) for i = 6 to 15 temparray = i^0.5 next -- Regards, Tom Ogilvy "ten" wrote in message ... Thx However, I still need to have more dynamically size on this. I am doing a loop through a lot of data, and sometime sI need an array of only 1, sometimes I need 100. I guess that for time concideration to build an array with size 100 (which I use only a few times, and even not at all) would take a lot of memory. Are there really no way of dynamically changing the Array size during the program in VBA? Correct me if I am wrong. And/or please add more solutions if you have. -----Original Message----- Sub tester3() Dim tempArray(0 To 10) tempArray(0) = "someValue" tempArray(1) = "someOtherValue" MsgBox "TempArray, 0 = " & tempArray(0) & _ vbNewLine & "TempArray, 1 = " & tempArray(1) End Sub -- Regards, Tom Ogilvy "ten" wrote in message ... Could anyone please advice me with some Array-help (or where to find the information): I need to have an array I got this far... Dim tempArray tempArray = Array() '(or tempArray = Array("") tempArray(0) = "someValue" tempArray(1) = "someOtherValue" And it crash on the tempArray(0) - or on tempArray(1) if I use tempArray = Array("") - because it's not defined. However, I don't want to specify the length of my Array, but have it dynamic. How can I achieve this? Thx! . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array size
In order to have it dynamic
Dim tempArray() Redim tempArray(0 to 1) Alan Beban ten wrote: Could anyone please advice me with some Array-help (or where to find the information): I need to have an array I got this far... Dim tempArray tempArray = Array() '(or tempArray = Array("") tempArray(0) = "someValue" tempArray(1) = "someOtherValue" And it crash on the tempArray(0) - or on tempArray(1) if I use tempArray = Array("") - because it's not defined. However, I don't want to specify the length of my Array, but have it dynamic. How can I achieve this? Thx! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array size
"ten" wrote in message ... Thx However, I still need to have more dynamically size on this. I am doing a loop through a lot of data, and sometime sI need an array of only 1, sometimes I need 100. I guess that for time concideration to build an array with size 100 (which I use only a few times, and even not at all) would take a lot of memory. Are there really no way of dynamically changing the Array size during the program in VBA? Correct me if I am wrong. And/or please add more solutions if you have. There is, you can used Redim Preserve BUT be aware there's an overhead. Each time you do this the system has to effectively make a new copy of the array and throw away the old one. With a small array, and 100 subscripts IS small I'd suggest you initially dimension it to 100 and then redim it at the end of the iteration Keith |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet Function to Create Array of Size n with values x Through | Excel Discussion (Misc queries) | |||
Is there an array size limit for MMULT or MINVERSE in excel 2007? | Excel Worksheet Functions | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
Can we write VBA code to set all column/row's size back to default size? | Excel Programming | |||
Can we write VBA code to set all column/row's size back to default size? | Excel Programming |