Resizing an array dynamically

[Rhodie72]

I currently have this code which is giving me an headache. I can't remember how to resize the array in VBA dynamically. Would appreciate any help in this regard please.

ReDim Preserve Directories(0 To UBound(Directories(n)))

It's part of a series of loops and the n increments sequentially to the next highest number.

I can see this is going to be one of Homer's "Doh!" moments when the answer comes, but it's late and I'm tired.

I do need to preserve the data already stored in the array for future use too.

 

[Norie]

Re: Need help pls -Resiszing an array dynamically

The posted code looks fine, what problems are you having with it?

 

[Tetra201]

Re: Need help pls -Resiszing an array dynamically

Try just

ReDim Preserve Directories(0 To n)

 

[hurgadion]

Re: Need help pls -Resiszing an array dynamically

Hi,
try to test an Example in an empty Work**** (read rather WorkSheet):

ReDim tbl(1 To 1)
tbl(1) = 1
x = 1
for i = 1 To 10
    x = x + 1
    ReDim Preserve tbl(1 To x)
    tbl(x) = i+1
Next i
Cells(1, 1).Resize(,x) = tbl

Best regards

 

[Rhodie72]

Re: Need help pls -Resiszing an array dynamically

Hi,
try to test an Example in an empty Work**** (read rather WorkSheet):

ReDim tbl(1 To 1)
tbl(1) = 1
x = 1
for i = 1 To 10
    x = x + 1
    ReDim Preserve tbl(1 To x)
    tbl(x) = i+1
Next i
Cells(1, 1).Resize(,x) = tbl

Best regards

Appreciate that idea however, it's going to create a multidimensional array which won't help at all.

 

[Rhodie72]

Re: Need help pls -Resiszing an array dynamically

The posted code looks fine, what problems are you having with it?

It produces an error. I'm now in bed so I will look again in the morning.

The array is a collection of folders and strings which is why I want to preserve the data between resizing. Maybe I need to post the entire module

 

[Rhodie72]

Re: Need help pls -Resiszing an array dynamically

Try just

ReDim Preserve Directories(0 To n)

I may be wrong but I'm sure that's how you create multi-dimension arrays?

 

[Norie]

Re: Need help pls -Resiszing an array dynamically

Might be a good idea as it's not to clear how you want to change the dimensions of the array.

In particular the use of UBound(Directories) seems a bit odd, to me anyway, but could be perfectly valid.

 

[Tetra201]

Re: Need help pls -Resiszing an array dynamically

I may be wrong but I'm sure that's how you create multi-dimension arrays?

No, that's a one-dimensional array. You resize it on every cycle of the loop:
n=0 --> ReDim Preserve Directories(0 To 0) --> it has 1 element;
n=1 --> ReDim Preserve Directories(0 To 1) --> it has 2 elements;
n=2 --> ReDim Preserve Directories(0 To 2) --> it has 3 elements, and so on.

 

[JoeMo]

Re: Need help pls -Resiszing an array dynamically

Using Redim Preserve on each cycle of a loop is inefficient. Much easier to over dimension the array at the start and redim preserve what you need of it once, at the end.

Here's an example. Suppose you have 25 random integers in A1:A25 and you want to store only the even numbers in an array.

Sub ReDimPreserveOnce()
Dim R As Range, N(), c As Range, ct As Long
Set R = Range("A1:A25")
ReDim N(1 To 25) 'No obligation to fill all 25 elements
For Each c In R
       If c Mod 2 = 0 Then
              ct = ct + 1
              N(ct) = c.Value
       End If
Next c
If ct > 0 Then ReDim Preserve N(1 To ct)  'ct can be less than 25
MsgBox Join(N, ", ")
End Sub