Basics of VBA / Macros Chapter – 59 (Arrays Part‑2)

⚡ 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 – 59 (Arrays Part-2)
⏱ 2 min readUpdated 12 November 2026

Dynamic Array

If the size of your array increases and you don’t want to fix the size of the array, you can use the ReDim keyword. Excel VBA then changes the size of the array automatically. Add some numbers to column A.

Dynamic Array in Excel VBA

Place a command button on your worksheet and add the following code lines:

1. First, we declare the array named numbers. Also declare two variables of type Integer. One named size and one named i.

Dim numbers() As Integer, size As Integer, i As Integer

The array has no size yet. numbers, size and i are randomly chosen here, you can use any names. Remember to refer to these names in the rest of your code.

2. Next, we determine the size of the array and store it into the variable size. You can use the worksheet function CountA for this. Add the following code line:

Advertisement
size = WorksheetFunction.CountA(Worksheets(1).Columns(1))

3. We now know the size of the array and we can redimension it. Add the following code line:

ReDim numbers(size)

4. Next, we initialize each element of the array. We use a loop.

For i = 1 To size
numbers(i) = Cells(i, 1).Value
Next i

5. We display the last element of the array using a MsgBox.

MsgBox numbers(size)

6. Exit the Visual Basic Editor and click the command button on the sheet.

Result:

Last Element of the Array

7. Now to clearly see why this is called a dynamic array, add a number to column A.

Advertisement

Add Number

8. Click the command button again.

Dynamic Array Result

Excel VBA has automatically changed the size of this dynamic array.

9. When you use the ReDim keyword, you erase any existing data currently stored in the array. For example, add the following code lines to the previously created code:

ReDim numbers(3)
MsgBox numbers(1)

Result:

Advertisement

Without Preserve

The array is empty.

10. When you want to preserve the data in the existing array when you redimension it, use the Preserve keyword.

ReDim Preserve numbers(3)
MsgBox numbers(1)

Result:

With Preserve

✨ Ask AI about this article

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

Free · AI can be wrong