VBAtoPython
Start Conversion

VBA Arrays & ReDim to Python Lists

Static arrays, dynamic arrays, ReDim Preserve — all mapped to Python lists with the 0-based vs 1-based indexing gotcha explained.

Static Arrays → Lists

VBA

' Fixed-size array (0 to 9 = 10 elements)
Dim scores(9) As Double

' Assign values
scores(0) = 95.5
scores(1) = 87.3
scores(9) = 72.1

Python

# List with 10 elements initialized to 0
scores = [0.0] * 10

# Assign values
scores[0] = 95.5
scores[1] = 87.3
scores[9] = 72.1

VBA Dim arr(9) creates an array with indices 0 through 9. Python [0.0] * 10 creates a list with the same 10 elements.

The Indexing Gotcha: 0-Based vs 1-Based

VBA arrays can start at 0 or 1 depending on Option Base or explicit bounds. Python lists always start at 0.

VBA (1-based)

Option Base 1
Dim names(5) As String
' Index 1 to 5

' Or explicit bounds:
Dim data(1 To 100) As Double

Python (0-based)

names = [""] * 5
# Index 0 to 4

# To mimic 1-based, pad index 0:
data = [0.0] * 101  # ignore index 0

When converting 1-based VBA arrays, you have two choices: shift all indices by -1 (cleaner) or pad the list with a dummy element at index 0 (preserves original index numbers).

Dynamic Arrays: ReDim → List Creation

VBA

Dim results() As Double

' Size determined at runtime
n = Range("A1").Value
ReDim results(1 To n)

Python

# Size determined at runtime
n = ws["A1"].value
results = [0.0] * n

VBA ReDim (without Preserve) creates a new array, discarding old data. In Python, simply create a new list.

ReDim Preserve → list.append()

ReDim Preserve resizes an array while keeping existing data. In Python, lists are dynamic by default — just use append().

VBA

Dim items() As String
Dim count As Long
count = 0

For i = 1 To 100
    If Cells(i, 1).Value <> "" Then
        count = count + 1
        ReDim Preserve items(1 To count)
        items(count) = Cells(i, 1).Value
    End If
Next i

Python

items = []

for i in range(1, 100 + 1):
    val = ws.cell(row=i, column=1).value
    if val != "":
        items.append(val)

Python lists don't need ReDim. The append() method handles dynamic growth automatically — no size management needed.

The converter flags ReDim Preserve for manual review because the exact refactoring depends on context. Usually the pattern simplifies to append(), but sometimes a list comprehension or other Python idiom is more appropriate.

Multi-Dimensional Arrays

VBA

Dim grid(1 To 10, 1 To 5) As Double

grid(3, 2) = 42.0

For r = 1 To 10
    For c = 1 To 5
        Debug.Print grid(r, c)
    Next c
Next r

Python

grid = [[0.0] * 5 for _ in range(10)]

grid[2][1] = 42.0  # 0-based indices

for r in range(10):
    for c in range(5):
        print(grid[r][c])

Python uses nested lists for multi-dimensional arrays. Remember to adjust for 0-based indexing: VBA grid(3, 2) becomes grid[2][1].

Common Pattern: Collecting Cell Values

The most common use of VBA arrays is collecting values from a worksheet range. In Python, this simplifies dramatically.

VBA (verbose)

Dim values() As Variant
Dim n As Long
n = 0
For i = 1 To 100
    If Cells(i, 1).Value > 0 Then
        n = n + 1
        ReDim Preserve values(1 To n)
        values(n) = Cells(i, 1).Value
    End If
Next i

Python (concise)

values = [
    ws.cell(row=i, column=1).value
    for i in range(1, 101)
    if ws.cell(row=i, column=1).value > 0
]

A Python list comprehension replaces the entire ReDim Preserve loop in a single expression.

Related Guides

Convert your VBA arrays

The converter handles array declarations and flags ReDim Preserve for manual review.

Open Converter