Return to VBA Code Examples

VBA Dynamic Array (Redim & Redim Preserve)

This tutorial will demonstrate how to use dynamic arrays (redim and redim preserve) in VBA.

Dynamic Array

Dynamic Arrays are arrays that can change sizes (as opposed to static arrays, which are static).

To declare a dynamic array, you declare the array, but omit the array size:

Then, before you can assign values to your array, you must use the ReDim Statement to set the array to your desired size:

Now, anytime you want to change your array size, simply use ReDim (or ReDim Preserve as we’ll learn about below).

Dynamic Variant Arrays

Note: Variant Arrays are a little different. With variant arrays, you don’t need to set the array size with ReDim before assigning values.

Redim vs. Redim Preserve

The ReDim statement resizes an array, clearing all existing values.

The ReDim Preserve statement resizes an array, keeping (“preserving”) all existing values.

Using ReDim

In practice, resizing an array with ReDim looks like this:

vba redim output 1

Using ReDim Preserve

In this example, we will use ReDim to set the initial dynamic array and then ReDim Preserve to resize the array, keeping the original values:

vba redim output 2

If you do not use the PRESERVE statement, you would lose the data that had been in the array previously.

vba redim output 3

In the  immediate window above, the array populated Mel, Steve and Bob.  When it was re-declared, it remove those values and instead returned 3 blank values and then the value ‘Fred’.   This is due to the PRESERVE statement being omitted.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! vba save as

Learn More!