VBA ReDim

What is Excel VBA ReDim Statement?

The Excel VBA ReDim statement can be implemented for sizing or resizing a dynamic array in VBA. The Public, Private, or Dim keywords can define the array. Users can re-declare the array size by implementing the ReDim statement for redefining the dimensions or number of elements of a Dynamic VBA array. ReDim differs from the Dim keyword, which declares many types of variables in VBA.

Below is a quick example of how you can implement the Excel VBA ReDim statement to resize a single-dimension array:

We create an array, example_array, of length five.
We assign a value of 20 to its fifth element. When we execute the code, we get an output of 20.
We resize the array to 10 elements using the ReDim Statement.

When we run the code, we get an empty output as the elements have been erased.

Output:

What is Excel VBA ReDim Statement?

We implement the Excel VBA ReDim Statement for sizing or resizing a dynamic array in VBA. The array could be already declared with the Private, Public, or Dim keywords with empty parentheses.
When implementing the Preserve keyword while using the ReDim statement, no changes in the data type are allowed.
The Excel VBA ReDim statement is declarative when the declared variable is not present at the module or procedure level.

Explanation and Uses

Let us first look at the syntax of the Excel VBA ReDim Statement and its parameters.

Syntax

The syntax for the Excel VBA ReDim statement is as follows:

ReDim [ Preserve ] varname ( subscripts ) [ As type ], [ varname ( subscripts ) [ As type ]] . . .

Here,

Preserve: This is an ‘Optional’ parameter. If your existing dynamic array’s data needs to be preserved while changing the size of the array, you should always use this keyword.
varname: This is a ‘Required’ parameter. Use the standard variable naming conventions while you name the variable as part of this parameter.
subscripts: This is another ‘Required’ parameter. It represents the dimensions of an array variable, which can be up to 60.

The syntax for the subscript’s argument is shown below:

[lower To] upper [ , [lower To] upper ] . . .

For using the subscripts syntax, users need to mention it in lowercase explicitly. When no Option Base statement is mentioned, the lower bound is taken to be zero.
type: This is an ‘Optional’ parameter. Various data types like Long, Currency, Byte, Single, Double, Object, Variant, Boolean, Integer, etc., are supported.

Explanation

We use the VBA ReDim statement for sizing or resizing a dynamic array.

The Public, Private, or Dim keyword can declare a dynamic array. However, we should use empty parentheses with no dimension subscripts.

Users can implement the ReDim Statement any number of times to change the number of elements or dimensions of a dynamic array. Still, they cannot change its data type unless it is a Variant where the type of elements can be modified via an ‘As-type’ clause.
You cannot change the data type when using the Preserve keyword.
Users cannot modify the number of dimensions while implementing the ‘Preserve’ keyword in the Excel VBA ReDim statement.
Only the VBA ReDim ubound array (upper bound) can be modified while working the VBA ReDim Preserve keyword to change the dynamic array’s size.
ReDim is generally used to increase the size of an array. If you use it to decrease the size, the existing items in the array could get lost.

Uses

To size or resize dynamic array.
Creating a dynamic array with the ReDim statement.
Resize the Array Size While Remembering the Old Values.

Preserve Keyword

The Preserve keyword keeps the existing array items while resizing and ensures they are not wiped out. With the Preserve keyword for the VBA ReDim preserve array, you can resize the last array dimension, but you cannot change the number of dimensions.

How to Create a Dynamic Array with ReDim Statement?

In the below section, we will learn how to create a dynamic array with the ReDim Statement step-by-step. Here, we shall make a dynamic array and implement the VBA ReDim Statement to resize the array. Then, we will assign the days of the week to each array index. Finally, we will run this macro such that only the days of the week at the even index are shown.

Step 1 – Start with a new sub-procedure by naming the macro like sample_array() in the Visual Basic editor.

Step 2 – Now, define a dynamic array named test_array() using the VBA Dim Statement as a String. As seen, we omit the size of the dynamic array and only declare it.

Step 3 – We use VBA ReDim to set the dynamic array size to 6.

Step 4 – Next, assign the days of the week to each index of the test_array() as Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, and Saturday, starting from 0 to 6. As we assigned the array upper bound as 6, the size of the test_array() is 7, that is, 0 to 6. Each index gets a value equivalent to the days in the week.

Step 5 – As we want to display the values of the days in the week at the even positions, we use the even number indexes for the display separated by an arrow sign, as shown below.

Step 6 – After execution, the message box displays all the days of the week at the even positions of the test_array() array.

Code:

Sub sample_array()
Dim test_array() As String
ReDim test_array(6) As String
test_array(0) = “Sunday”
test_array(1) = “Monday”
test_array(2) = “Tuesday”
test_array(3) = “Wednesday”
test_array(4) = “Thursday”
test_array(5) = “Friday”
test_array(6) = “Saturday”
MsgBox test_array(0) & “-> ” & test_array(2) & “-> ” & test_array(4) & “-> ” & test_array(6)
End Sub

VBA Redim - dynamic array - Step 5 - Output

Examples

Let us look at some examples below to understand how to use the VBA ReDim Statement.

Example #1 – Implementing the Excel VBA ReDim for single dimension array

In this example, let us learn how to work with VBA ReDim for single-dimensional arrays.

Here, we declare a dynamic array one_array and size the array to the upper bound 4. So now, the array size becomes 5, from index 0 to 4.

Step 2 – Next, we assign the value 10 to the fourth index of the array and print the same to validate that it works.

Step 3 – In the below code, we are resizing the array to size four from -> 1 to 4. As we didn’t explicitly use the Preserve keyword, all the elements in the array would be erased. Therefore, when you run this code, the output is empty.

Code:

Sub example_one_array()
Dim one_array() As Variant

‘using the dim statement to declare a variant array

ReDim one_array(4)

one_array(4) = 40

‘printing the value of the array
Debug.Print one_array(4)

MsgBox one_array(4)

‘Output: 40

‘Case 1: Without implementing the Preserve keyword to check if the existing item is kept or not.

ReDim one_array(1 To 4)

‘printing the value of the array
Debug.Print one_array(4)
MsgBox one_array(4)

‘Output: Empty

‘Case 2: Preserving the existing values by implementing the Preserve keyword.
Dim case_two_array() As Variant

ReDim case_two_array(6)

case_two_array(6) = 60

‘printing the value of the array
Debug.Print case_two_array(6)
MsgBox case_two_array(6)
‘Output: 60

ReDim Preserve case_two_array(1 To 6)
‘printing the value of the array
Debug.Print case_two_array(6)
MsgBox case_two_array(6)
‘Output: 60
End Sub

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

Here, you shall learn how to resize the array while remembering the old values using the Preserve keyword. We know that Preserve is an optional keyword that needs to be explicitly used with the ReDim Statement to preserve the existing items of the dynamic array.

Step 1 – We start by declaring a dynamic array, two_array. Here, we are sizing the array to upper bound 6. So now, the array size becomes 7, from index 0 to 6.

Step 2 – Now, we assign a value of 60 to index six and print the same.

Step 3 – Implement the Preserve keyword to preserve the existing items in the array. Then, resize the array to size 6.

Step 4: Now, execute the code and check the output.

Code:

Sub example_two_array()
Dim two_array() As Variant
‘using the dim statement to declare a variant array

ReDim two_array(6)

two_array(6) = 60

‘printing the value of the array
Debug.Print two_array(6)
MsgBox two_array(6)

‘Output: 60

ReDim Preserve two_array(1 To 6)

‘printing the value of the array
Debug.Print two_array(6)
MsgBox two_array(6)

‘Output: 60

End Sub

Example #3 – Implementing the Excel VBA ReDim multidimensional array

You shall learn to work with a multidimensional array using the code below. With the help of the Excel VBA ReDim Statement, you can easily modify the number of dimensions of a dynamic array and the size of each dimension.

It is important to note here that you can only change the upper bound of the last dimension when it comes to ReDim for the multi-dimensional array.

Step 1 –Let us start by declaring a dynamic array called three_array of dimension (4,4).

Step 2 – Now, we assign a value of 40 to three_array(4,4) and print the same to validate that the assignment works.

Step 3- Now, we implement the Preserve keyword in the Excel ReDim Statement to check if the existing items are kept. Here, we are resizing the multidimensional array to (4,10).

Step 4 – As mentioned earlier, we can only change the upper bound of the last dimension, that is, from -> 4 to 10. As we explicitly used the Preserve keyword, all the elements in the array would be preserved. Thus, you get the following output when you execute the code.

Now you can run the entire code on an excel sheet and validate if you get the output as shown below.

Code:

Sub example_three_array()
Dim three_array() As Variant
ReDim three_array(4, 4)

three_array(4, 4) = 40

‘printing the value of the array
Debug.Print three_array(4, 4)
MsgBox three_array(4, 4)

‘Output: 40

ReDim Preserve three_array(4, 10)

‘printing the value of the array
Debug.Print three_array(4, 4)
MsgBox three_array(4, 4)

‘Output: 40

End Sub

Important Things to Note

Below are a few essential points to note from the Excel VBA ReDim Statement article:

When the array size is defined inside the parenthesis, it becomes a static array, and you cannot use ReDim.
Once the array is assigned a data type, you cannot modify the data type of that array via the Excel VBA ReDim Statement.
It is recommended to implement a separate As-type clause for every variable that is being stated. For example, when an array is a Variant, the type represents the data type for every array element.

Frequently Asked Questions (FAQs)

1. What is the difference between ReDim and ReDim preserve in VBA?

With the ReDim Statement in Excel VBA you can size or resize an array, where the existing values will be cleared. In contrast, with the ReDim Preserve keyword, you can resize a dynamic array while preserving or keeping the existing values.

2. What is the difference between array resize and ReDim preserve in VBA?

When you work with the array resize, it allocates a new array and copies the items from the source to the destination array. In contrast, while working with the ReDim with the Preserve keyword, you resize a dynamic array while preserving the existing values.

3. What is the default value of ReDim in VBA?

Currently, the default value of an array of ReDim in VBA is zero. Thus, the array is always indexed at the beginning with zero, which makes the upper bound 364 rather than 365.