ReDim Preserve Query

[2016LM]

Hi,

I have the following vba code, which populates an array with the contents of the first 5 rows of column A.

Sub populate_array()
Dim parameters_2DArray as Variant
parameter_2DArray = Worksheets("Sheet1").Range("A1:A5").Value

[rest of code]

End Sub
I'm assuming the parameter_2DArray comprises 5 rows and 1 column. I want to increase the number of columns later in code by one, and have tried doing so using the following line;

ReDim Preserve parameter_2DArray(ubound(parameter_2DArray,1), ubound(parameter_2DArray,2)+1)

...but to no avail. I have also tried the following, but again, to no avail;
ReDim Preserve pipework_2DArray(5, 2)

Is what I'm trying to do possible this way? Any help etc. would be much appreciated.

Kind regards,
2016LM

 

[Fluff]

Try

ReDim Preserve parameter_2DArray(1 To UBound(parameter_2DArray, 1), 1 To UBound(parameter_2DArray, 2) + 1)

Also check the spelling of you variable

 

[JoeMo]

In addition to Fluff's comment, recognize that changing the upper bound of the 2nd dimension will not, by itself, capture the data in the second column. In your example, the initial array holds the values of A1:A5. After the redim, those values remain, but, for example, element parameter_2DArray(1,2) does not hold B1. That element is empty until you fill it in some manner.

 

[2016LM]

Brilliant, thanks Fluff; I didn't think I would have to include the '1 to UBbound(...)' for the rows. But alas, it's required, and my code now works as desired.

Hi JoeMo, my intention is to hopefully use the empty column to store results that are derived using the numbers in column A.

Thanks again to you both (Fluff and JoeMo) for the quick replys.

Kind regards,
2016LM

 

[Fluff]

Glad we could help & thanks for the feedback