VBAtoPython
Start Conversion

ReDim Preserve Uninitialized Array

Why ReDim Preserve crashes on an uninitialized VBA array, the canonical (Not arr) = -1 guard, and the Python list pattern that never has the problem.

What goes wrong

ReDim Preserve resizes a dynamic array while keeping its current contents. The keyword Preserve tells VBA to copy the existing buffer into the new one. If there is no existing buffer — because the array was declared but never ReDim'd — VBA throws Subscript out of range (Run-time error 9) or, worse, silently corrupts state in some Office hosts.

VBA — crashes

Dim items() As String
' (never ReDim'd)

For i = 1 To 5
    ReDim Preserve items(1 To i)
    items(i) = "row " & i
    ' First iteration: Subscript
    ' out of range — no buffer
    ' to preserve
Next i

Why it crashes

' Step-through:
' i = 1
'   ReDim Preserve items(1 To 1)
'   → "preserve from where?"
'   → Run-time error 9
'
' Plain ReDim (no Preserve)
' would have worked here.

The canonical fix: (Not arr) = -1

The reliable, undocumented way to detect an uninitialized dynamic array in VBA: apply the Not operator to it and compare with -1. If it matches, the array has never been ReDim'd and you must use plain ReDim first.

VBA — fixed

Dim items() As String

For i = 1 To 5
    If (Not items) = -1 Then
        ReDim items(1 To 1)
    Else
        ReDim Preserve items(1 To i)
    End If
    items(i) = "row " & i
Next i

VBA — cleaner

Dim items() As String
ReDim items(1 To 0)
' Some hosts allow zero-length;
' then Preserve grows from there.

' Or: just always plain ReDim
' on the first pass, Preserve
' on subsequent passes.

The (Not arr) = -1 idiom works because VBA stores a descriptor for uninitialized arrays whose bitwise NOT happens to evaluate to -1. It's undocumented but stable across all Office versions since 2003.

Why the whole pattern is brittle

ReDim Preserve in a loop is O(n²) — every iteration copies the entire current buffer into a new one. For a 10,000-row collection that's ~50 million copy operations. VBA tolerates it; performant code shouldn't.

Even in pure VBA, the idiomatic fix is to size the array once (or use a Collection) and skip the per-iteration Preserve entirely. In Python, list.append()is amortized O(1) and the problem disappears.

The Python equivalent: list.append()

Python lists have no separate uninitialized state. items = []is a fully-formed empty list — you can append immediately, no guard needed.

VBA (with guard)

Dim items() As String
For i = 1 To 5
    If (Not items) = -1 Then
        ReDim items(1 To 1)
    Else
        ReDim Preserve items(1 To i)
    End If
    items(i) = "row " & i
Next i

Python (no guard)

items = []
for i in range(1, 6):
    items.append(f"row {i}")

# That's it. No guard.
# No O(n²) copying.
# No "uninitialized array" state.

Where the VBA pattern needs ~9 lines of error-prone bookkeeping, the Python equivalent is 3 lines that can't fail.

How the converter handles it

VBAtoPython recognizes the ReDim Preserve arr(1 To n) + index assignment pattern inside loops and rewrites it to arr.append(value). The (Not arr) = -1 guard becomes implicit because Python doesn't need it.

Edge case: if your VBA accesses UBound(arr) as part of the preserve logic, the converter flags it for manual review — Python's len(arr) is the equivalent but the index math may differ depending on the lower bound you originally chose.

Related

Convert your ReDim Preserve loops

The free converter rewrites ReDim Preserve patterns to list.append() automatically — no guards, no O(n²) copies.

Open Converter