VBA ReDim

Excel VBA ReDim Statement

VBA Redim statement is similar to the dim statement but the difference is that it is used to store or allocate more storage space or decrease the storage space a variable or an array has with it, now there are two important aspects used with statement is Preserve, if preserve is used with this statement then it creates a new array with different size and if preserve is not used with this statement then it just changes the array size of the current variable.

Arrays are an important part of the 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.

In order to declare the array name, we need to first 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

Here MyArray is the name of the array, which can hold the value from 1 to 5. MyArray can hold 5 different results in it like the below one.

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

Dynamic Array with ReDim Statement

But in Dynamic array, this isn’t the case. We will not decide the lower value and upper value well in advance; rather, we just define the array name and assign data type.

Sub ReDim_Example1()

Dim MyArray() As String

End Sub

In order to make the array name dynamic, we need to declare it with the word “Dim” first, but don’t decide the size of the array well in advance. We just name an array with empty values inside the parenthesis (). When the array does not include size, then it is treated as a dynamic array.

Dim MyArray() As String

The moment you mention the size of the array 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.

Examples to use VBA Redim Statement

You can download this VBA ReDim Excel Template here – VBA ReDim Excel Template

Example #1

Take a look at the example of using the “ReDim” statement practically. 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
Example 1

Step 3: Now use the word “Redim” and assign the size of the array.

Code:

Sub ReDim_Example1()

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

End Sub
Redim VBA Example 1-1

Step 4: So now 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
Redim VBA Example 1-2

So, the first array is equal to the word “Welcome” the second array is equal to the word “to,” and 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
Example 1-3

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

Redim Visual basic Application Example 1-4

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

Once the array name is assigned values, we can also resize at any point in time in the procedure by using the word “ReDim Preserve.”

Assume you have already declared an array name and assigned values to those array name 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 to remember the old values as well.

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.

ReDim Preserve 1

So we got the new word in the D1 cell.

The reason why we need to use the word “preserve” because array should remember the old array values in the procedure.

The moment you ignore the word “preserve,” it will not remember old values.

Things to Remember Here

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

Recommended Articles

This has been a guide to VBA ReDim. Here we discuss how to handle dynamic array using ReDim Preserve along with examples & a downloadable excel template. Below are some useful excel articles related to VBA –

  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>