Adding to an array

[Otto Moehrbach]

Excel 2002, WinXP
Let's say I have an array, MyArray, consisting of A, B, C. (Strings only)
Now lets say that I want to build another array, OtherArray, consisting of
the same A, B, C, but having D as well. I know that I can build the
OtherArray as simply A, B, C, D but I don't want to do that. I want to
build the OtherArray by referencing MyArray and tacking D on to it.
How do I code the building of the OtherArray by using MyArray and D?
Thanks for your help. Otto

 

[Myrna Larson]

If you are sure you need both arrays:

Sub Test()
Dim Array1() As String
Dim Array2() As String

ReDim Array1(1 To 3)
For i = 1 To 3
Array1(i) = Format$(i)
Next i

Array2 = Array1
ReDim Preserve Array2(1 To UBound(Array2) + 1)
Array2(4) = "4"

For i = 1 To UBound(Array2())
Debug.Print Array2(i)
Next i
End Sub

You can also copy the values from Array1 to Array2, one element at a time, in
a For/Next loop, i.e.

For i = 1 to Ubound(Array1())
Array2(i) = Array1(i)
Next i

 

[Myrna Larson]

PS: DO you need a 2nd array? As long as the first was defined as a dynamic
array (no subscripts in the DIM statement, just in a REDIM statement), you can
always change the size of the right-most dimension, up or down, without losing
data, with the REDIM PRESERVE statement.

 

[Bob Phillips]

Hi Otto,

here is one way

myArray1 = Array("A", "B", "C")
myArray2 = myArray1
ReDim Preserve myArray2(LBound(myArray2) To UBound(myArray2) + 1)
myArray2(UBound(myArray2)) = "D"

 

[Otto Moehrbach]

Myrna, Bob
Thanks for your help. Otto

 

[Alan Beban]

Excel 2002, WinXP
Let's say I have an array, MyArray, consisting of A, B, C. (Strings only)
Now lets say that I want to build another array, OtherArray, consisting of
the same A, B, C, but having D as well. I know that I can build the
OtherArray as simply A, B, C, D but I don't want to do that. I want to
build the OtherArray by referencing MyArray and tacking D on to it.
How do I code the building of the OtherArray by using MyArray and D?
Thanks for your help. Otto

For another approach, if the functions in the freely downloadable file
at http://home.pacbell.net/beban are available to your workbook

MyArray2 = MakeArray(MyArray,"D",1)

Alan Beban