declare dynamic array with 3 columns

[kinetic]

hello

i am trying to declare a dynamic array with 3 columns. within a for loop i would like to add to the array so it is unknown how many record elements will be in the array.

here is what i have so far which is not working

'declare array
Dim final_array() As String

'add records to the array
ReDim final_array(1 To 3, 1 To UBound(final_array) + 1)
                            
final_array(0, UBound(final_array) - 1) = Current_Sheet
final_array(1, UBound(final_array) - 1) = Current_Ticker
final_array(2, UBound(final_array) - 1) = Current_PCT_Change

'test array
MsgBox (UBound(final_array))
MsgBox final_array(1, 1)

I never get a message box

Any help is GREATLY appreciated!

 

[Eric W]

'declare array
Dim final_array() As String

'add records to the array
ReDim final_array(1 To 3, 1 To UBound(final_array) + 1)
                            
final_array(0, UBound(final_array) - 1) = Current_Sheet
final_array(1, UBound(final_array) - 1) = Current_Ticker
final_array(2, UBound(final_array) - 1) = Current_PCT_Change

'test array
MsgBox (UBound(final_array))
MsgBox final_array(1, 1)

One thing I noticed is that you're defining the first dimension as 1 to 3, then using 0 as an index. This will cause an error. So it will jump to the error handler, explaining why you don't get a message box.

 

[kinetic]

nice catch

i changed it but same results - not working

[COLOR=#555555][FONT=Roboto]‘declare array
Dim final_array() As String[/FONT][/COLOR]
[COLOR=#555555][FONT=Roboto]‘add records to the array
ReDim final_array(0 To 2, 0 To UBound(final_array) + 1)[/FONT][/COLOR]
[COLOR=#555555][FONT=Roboto]final_array(0, UBound(final_array) – 1) = Current_Sheet
final_array(1, UBound(final_array) – 1) = Current_Ticker
final_array(2, UBound(final_array) – 1) = Current_PCT_Change[/FONT][/COLOR]
[COLOR=#555555][FONT=Roboto]‘test array
MsgBox (UBound(final_array))
MsgBox final_array(1, 1)[/FONT][/COLOR]

 

[Eric W]

Two other thoughts:

ReDim Preserve final_array(0 To 2, 1 To UBound(final_array, 2) + 1)

First, if you want to keep the values already in the array, you need the Preserve option. Second, you need the ,2 in the UBound function to get the size of the second dimension.

Also, what are Current_Sheet, Current_Ticker and Current_PCT_Change defined as? Your array is defined as String. VBA should be able to coerce most data types into String, but not all of them. If Current_Sheet is defined as Worksheet, that will cause an error too.

Finally, this may work for you for small size arrays, but for larger arrays it's pretty inefficient. For a Redim Preserve, VBA has to create another array, then copy the values over, then dispose of the first array. If you have enough memory on your PC (and modern PCs usually have plenty), it's best to just define the array initially as big as you think it'll ever get.

 

[kinetic]

i think im going to go with declaring a large record number of the array - thanks for your help!