Copy or Export this Code to VBA
Use these verified developer utilities to run this lesson directly inside your Microsoft Excel Visual Basic Editor.

In this article
- Static vs Dynamic Array Declaration
- Sizing Arrays Dynamically with ReDim
- Expanding Arrays While Keeping Existing Data: ReDim Preserve
- Frequently Asked Questions
- Can ReDim Preserve resize both dimensions of a 2D array?
- How do you erase or free dynamic array memory when done?
- What is the fastest way to populate an array from a worksheet?
- 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.
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.
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.
📖 Explore the complete Excel VBA Macro Tutorial Catalog.
Stuck on a step? Ask a question and the AI answers using this article.