VBA ReDim

The VBA ReDim statement is similar to the Dim statement. But, the difference is that one may use it to store or allocate more storage space or decrease the storage space a variable or an array has. Now, there are two important aspects used with the statement: Preserve. If one uses Preserve with this statement, it creates a new array with different sizes. But, on the other hand, if one does not use Preserve with this statement, it just changes the array size of the current variable.

Arrays are an important part of VBA coding. Using arrays, we can store more than one value in the same variable we defined. Like how we declare the variable using the word "Dim," we need to declare the array name by using "Dim" as well.

To declare the array name, we need first to identify the kind of array we are going to define. In arrays, we have 5 types:

1. Static Array
2. Dynamic Array
3. One Dimensional Array
4. Two Dimensional Array
5. Multi-Dimensional Array

In the static array in excel, we will decide the lower value and upper value of the array well in advance while declaring the variable. For example, look at the below example.

Code:

Sub ReDim_Example1()
  
  Dim MyArray(1 To 5) As String

End Sub

MyArray is the array's name, which can hold the value from 1 to 5. So, for example, MyArray can hold 5 different results, like the one below.

Code:

Sub ReDim_Example1()
  Dim MyArray(1 To 5) As String

  MyArray(1) = "Hi"
  MyArray(2) = "Good"
  MyArray(3) = "Morning"
  MyArray(4) = "Have a"
  MyArray(5) = "Nice Day"

End Sub

But in the dynamic array, this is not the case. Therefore, we will not decide on the lower and upper values well in advance. Rather, we define the array name and assign data type.

Sub ReDim_Example1()

Dim MyArray() As String

End Sub

To make the array name dynamic, we need to declare it with the word "Dim" first, but don't decide the array size well in advance. Instead, we name an array with empty values inside the parenthesis (). When the array does not include size, it treats it as a dynamic array.

Dim MyArray() As String

When you mention the array size inside the parenthesis, it becomes a static array. Dim MyArray(1 to 5) As String.

In the dynamic array, we always resize the array size by using the word “ReDim” in the next line of the code.

ReDim MyArray(1 to 6) As String

Any value stored to the array name in the previous steps, i.e., using the "Dim" statement, stands null, and the size we declared using "ReDim" becomes the new size of the array.

Example #1

Look at the example of using the "ReDim" statement practically. Then, follow the below steps to apply "ReDim."

Step 1: Create a macro name first.

Step 2: Declare an array name as a string.

Code:

Sub ReDim_Example1()

  Dim MyArray() As String

End Sub

Step 3: Now, use "Redim" and assign the array size.

Code:

Sub ReDim_Example1()

  Dim MyArray() As String
  ReDim MyArray(1 To 3)

End Sub

Step 4: The array name "MyArray" can hold up to 3 values here. Assign the value to these 3 arrays like the below one.

Code:

Sub ReDim_Example1()

  Dim MyArray() As String
  ReDim MyArray(1 To 3)

  MyArray(1) = "Welcome"
  MyArray(2) = "to"
  MyArray(3) = "VBA"

End Sub

So, the first array is equal to the word "Welcome," and the second is equal to the word "to." Finally, the third array is equal to the word "VBA."

Step 5: Now, store these array values in cells.

Code:

Sub ReDim_Example1()

  Dim MyArray() As String
  ReDim MyArray(1 To 3)

  MyArray(1) = "Welcome"
  MyArray(2) = "to"
  MyArray(3) = "VBA"

  Range("A1").Value = MyArray(1)
  Range("B1").Value = MyArray(2)
  Range("C1").Value = MyArray(3)

End Sub

Step 6: If you run this code, we should have these values in A1, B1, and C1 cells, respectively.

Example #2 - Resize the Array Size While Remembering the Old Values.

Once the array name is assigned values, we can resize it at any point in the procedure by using the word "ReDim Preserve."

Assume you have already declared an array name and assigned values to those array names like the below one.

Now, you would like to increase the array length by 2, i.e., 5. In this case, we can use the word VBA "ReDim Preserve" to resize the array length and remember the old values.

Code:

Sub ReDim_Example2()
  
  Dim MyArray() As String
  ReDim MyArray(3)

  MyArray(1) = "Welcome"
  MyArray(2) = "to"
  MyArray(3) = "VBA"

  ReDim Preserve MyArray(4)
  MyArray(4) = "Character 1"

  Range("A1").Value = MyArray(1)
  Range("B1").Value = MyArray(2)
  Range("C1").Value = MyArray(3)
  Range("D1").Value = MyArray(4)

End Sub

Now, we can assign two more values to the array.

Code:

Sub ReDim_Example2()

  Dim MyArray() As String
  ReDim MyArray(3)

  MyArray(1) = "Welcome"
  MyArray(2) = "to"
  MyArray(3) = "VBA"

  ReDim Preserve MyArray(4)
  MyArray(4) = "Character 1"

  Range("A1").Value = MyArray(1)
  Range("B1").Value = MyArray(2)
  Range("C1").Value = MyArray(3)
  Range("D1").Value = MyArray(4)

End Sub

Now, store these values in cells.

Code:

Sub ReDim_Example2()

  Dim MyArray() As String
  ReDim MyArray(3)

  MyArray(1) = "Welcome"
  MyArray(2) = "to"
  MyArray(3) = "VBA"

  ReDim Preserve MyArray(4)
  MyArray(4) = "Character 1"

  Range("A1").Value = MyArray(1)
  Range("B1").Value = MyArray(2)
  Range("C1").Value = MyArray(3)
  Range("D1").Value = MyArray(4)

End Sub

Now, run the Macro and see what happens.

So, we got the new word in the D1 cell.

We need to use the word "Preserve" because the array should remember the old array values in the procedure.

When you ignore the word "Preserve," it will not remember old values.

• The ReDim can only hold the last value of the array, not the many values. So, for example, we cannot use the code "ReDim Preserve MyArray(4 to 5)". Instead, it will throw an error.
• We cannot ReDim static arrays. When you assign the array size inside the parenthesis, it becomes a static array.
• Using ReDim, we cannot change the data type. The array can hold whatever data type we have assigned while declaring the array.

This article has been a guide to VBA ReDim. Here, we discuss handling dynamic arrays using ReDim Preserve, examples, and a downloadable Excel template. Below are some useful Excel articles related to VBA: -

• VBA ISNULL
• VBA Format
• Delete Row in VBA
• Excel VBA Subscript Out of Range
• VBA Max