[Excel VBA] How to Use Dynamic Arrays: Differences Between ReDim and ReDim Preserve

Introduction

When you declare an array in VBA like Dim myArray(1 To 5), its size is fixed. However, in many cases, you do not know how many elements you need to store until you run the program. For example, you might want to reserve array size only for the number of data items that match a specific condition.

In such cases, we use “Dynamic Arrays.” With dynamic arrays, you do not decide the size at the time of declaration but change it freely later using the ReDim command.

This article explains the basic usage of dynamic arrays and how to use the crucial keyword Preserve to expand the array size while keeping the original data.

Basic Sample Code for Dynamic Arrays

This macro first creates a dynamic array with 2 elements. Then, it expands the array to 4 elements while keeping the original data.

' Change the size of a dynamic array
Sub DynamicArrayExample()
    
    '--- 1. Declare dynamic array without specifying size ---
    Dim userList() As String
    
    '--- 2. Determine initial size with ReDim (2 elements) ---
    ReDim userList(1 To 2)
    userList(1) = "Sato"
    userList(2) = "Suzuki"
    
    Debug.Print "--- After first ReDim ---"
    Debug.Print userList(1) ' -> Sato
    Debug.Print userList(2) ' -> Suzuki
    
    '--- 3. Expand size while keeping original values with ReDim Preserve ---
    ReDim Preserve userList(1 To 4)
    userList(3) = "Takahashi"
    userList(4) = "Tanaka"
    
    Debug.Print vbCrLf & "--- After ReDim Preserve ---"
    Debug.Print userList(1) ' -> Sato (Value is preserved)
    Debug.Print userList(2) ' -> Suzuki (Value is preserved)
    Debug.Print userList(3) ' -> Takahashi
    Debug.Print userList(4) ' -> Tanaka
    
    '--- 4. Write the final array to cells at once ---
    Worksheets("Sheet1").Range("B2").Resize(1, 4).Value = userList

End Sub

Explanation of the Code

1. Declaring a Dynamic Array

Dim userList() As String To declare a dynamic array, leave the parentheses () empty when declaring the array name with Dim. This tells VBA that the size of this array will be changed later.

2. Determining Size with ReDim

ReDim userList(1 To 2) ReDim (short for Re-Dimension) is a command to actually determine (or redefine) the size of a dynamic array. At this point, userList becomes an array with 2 elements.

Note: If this array already contains data, executing ReDim without Preserve will erase (initialize) all the contents of the array.

3. Retaining Data with ReDim Preserve

ReDim Preserve userList(1 To 4) This is the most important point when handling dynamic arrays.

By adding the Preserve keyword after ReDim, VBA changes the size while keeping all existing values in the array. In this example, the size is expanded to 4 while leaving the original values “Sato” and “Suzuki”. Without Preserve, these two values would disappear.

Constraint of ReDim Preserve: When using Preserve, you can only change the size of the last dimension of the array. For example, you can change ReDim Preserve myArray(1 To 5, 1 To 10) to ReDim Preserve myArray(1 To 5, 1 To 20). However, if you try to change the first dimension (row count) like ReDim Preserve myArray(1 To 10, 1 To 10), an error will occur.

Summary

In this article, I explained VBA dynamic arrays and how to use ReDim / ReDim Preserve.

• Declare an array with empty parentheses () if the size is undecided (Dynamic Array).
• Use ReDim to determine or change the array size later.
• Always use ReDim Preserve if you want to resize the array while keeping existing data.

Mastering dynamic arrays allows you to make your programs flexible according to the amount of data being processed. ReDim Preserve is an important technique frequently used in practical work, so I highly recommend mastering it.