Array size

[ten]

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!

 

[Tom Ogilvy]

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

 

[ten]

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.

 

[Mike]

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.

 

[Tom Ogilvy]

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

 

[Alan Beban]

In order to have it dynamic

Dim tempArray()
Redim tempArray(0 to 1)

Alan Beban

 

[Keith Willshaw]

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