ReDim Preserve Array - VBA [duplicate]

Question

Initializing an array - Empty
ReDim Preserve when i m in the first loop iteration
When i use ReDim the code works fine - increase the size of the array, but the previous part of the array get empty.

BUT in the second iteration i get an error in the line ReDim Preserve arrTips(Counterarr, 5) when i m trying to increase the size of the array.

Code:

Option Explicit

Sub test()

    Dim arrTips() As Variant
    Dim i As Long, Counterarr As Long

    'Set counter to start array
    Counterarr = 0

    For i = 1 To 10

        'Increase array lenght
        ReDim Preserve arrTips(Counterarr, 5)

        arrTips(Counterarr, 0) = ""
        arrTips(Counterarr, 1) = ""
        arrTips(Counterarr, 2) = ""
        arrTips(Counterarr, 3) = ""
        arrTips(Counterarr, 4) = ""
        arrTips(Counterarr, 5) = ""

        Counterarr = Counterarr + 1

    Next i

End Sub

You can only increase the Y on a 2D array, being Y the column. If you want to increase that, you need to transpose arrays and such... Is better to define the array from the beginning (ReDim taxes the execution time). — Damian
– Damian, Commented Sep 6, 2019 at 7:08
You don't say much about the project in which this array issue arises. But looking at the limited information you would be possibly better served by making the Y dimension a separate object and then storing these objects in a collection. This will make your code much more flexible and easier to modify in the future. And importantly, completely avoid the redim issue. — freeflow
– freeflow, Commented Sep 6, 2019 at 10:46

Pᴇʜ · Accepted Answer · 2019-09-06 07:09:34Z

3

ReDim Preserve can only resize the last parameter but not the other ones when using Preserve.

From the offical documenation of the ReDim statement:

If you use the Preserve keyword, you can resize only the last array dimension and you can't change the number of dimensions at all. For example, if your array has only one dimension, you can resize that dimension because it is the last and only dimension. However, if your array has two or more dimensions, you can change the size of only the last dimension and still preserve the contents of the array.

answered Sep 6, 2019 at 7:09

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

ReDim Preserve Array - VBA [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related