Basics of VBA / Macros Chapter – 60 (Arrays Part‑3: Dynamic & Multi‑Dimensional)

⚡ Macro Developer Toolkit

Copy or Export this Code to VBA

Use these verified developer utilities to run this lesson directly inside your Microsoft Excel Visual Basic Editor.

🖇 View Code Example
Basics of VBA / Macros Chapter – 60 (Arrays Part-3: Dynamic & Multi-Dimensional)
⏱ 2 min readUpdated 12 November 2026

Excel VBA SeriesChapter: 60Topic: Dynamic Arrays & ReDim Preserve

In this article
  1. Static vs Dynamic Array Declaration
  2. Sizing Arrays Dynamically with ReDim
  3. Expanding Arrays While Keeping Existing Data: ReDim Preserve
  4. Frequently Asked Questions
  5. Can ReDim Preserve resize both dimensions of a 2D array?
  6. How do you erase or free dynamic array memory when done?
  7. What is the fastest way to populate an array from a worksheet?
  8. Continue learning

When the number of data points is unknown at compile time, static arrays cannot accommodate incoming records without wasting memory or crashing with an out-of-bounds error. Dynamic arrays in Excel VBA solve this by allowing you to allocate and expand array memory during runtime using the ReDim and ReDim Preserve statements.

Advertisement

Static vs Dynamic Array Declaration

' Static Array: Fixed size of 10 items (cannot change)
Dim staticArr(1 To 10) As String

' Dynamic Array: Declared with empty parentheses
Dim dynamicArr() As String

Sizing Arrays Dynamically with ReDim

Once you determine how many records you need to store (e.g. counting populated rows), use ReDim to set the bounds:

Sub PopulateDynamicArray()
    Dim recordCount As Long
    Dim employeeNames() As String
    
    recordCount = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row - 1
    
    ' Allocate exact memory required
    ReDim employeeNames(1 To recordCount)
    
    ' Populate array from worksheet
    Dim i As Long
    For i = 1 To recordCount
        employeeNames(i) = Cells(i + 1, 1).Value
    Next i
End Sub

Expanding Arrays While Keeping Existing Data: ReDim Preserve

Standard ReDim wipes all existing data from memory. Adding the Preserve keyword expands the array while keeping all existing values intact:

Sub AppendToArray()
    Dim logs() As String
    Dim count As Long
    count = 0
    
    ' Example logging loop
    Do While SomeCondition()
        count = count + 1
        ReDim Preserve logs(1 To count)
        logs(count) = "Log Entry at " & Now
    Loop
End Sub

Performance Tip: Resizing an array repeatedly with ReDim Preserve in a tight loop forces memory reallocations. For massive datasets, double the array size in chunks or read entire cell ranges directly into a 2D Variant array at once.

Advertisement

Frequently Asked Questions

Can ReDim Preserve resize both dimensions of a 2D array?

No, in multi-dimensional arrays, ReDim Preserve can only resize the last dimension. Attempting to resize the first dimension will cause a subscript out of range runtime error.

How do you erase or free dynamic array memory when done?

Use the Erase statement (e.g. Erase dynamicArr). For dynamic arrays, this releases allocated heap memory back to the operating system immediately.

What is the fastest way to populate an array from a worksheet?

Direct assignment: arr = Range("A1:D1000").Value transfers 4,000 cells into a 2D 1-based array in a single millisecond without looping.

Advertisement

📖 Explore the complete Excel VBA Macro Tutorial Catalog.

✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Free · AI can be wrong