Excel VBA ReDim
We can adjust the number of elements in an array through VBA Redim. Redim is not a function, it is Dynamic memory allocation program through Redim statement. When we do use Redim, a specific amount of memory is kept aside to store the data.
How to Use VBA ReDim Statement?
We will discuss how to use VBA ReDim Statement by using some examples.
VBA ReDim – Example #1
When we use Dim for an array, we cannot change the value of data store in an array. But when we use Redim, we can change the stored value in an array. This process of changing the array is called re-dimensioning the array.
Suppose we have an array A of 4 cell value then it will be defined with;
- Dim A(3) As Double
As we can see all the cells has “0” as stored value. Now suppose if we Redim the Dim A with 3 cell data and store the value in it then it will look like as below.
- ReDim B(2) As Double
B(0) = 1; B(1) = 3, B(2) = -6
It will look like as;
Now if we want to add cells in it, we can do it. Then it will automatically assign the “0” to that cells. Now for implementing ReDim, press Alt + F11 to open VBA Window. And from the Insert menu select Module option to open a new window as shown below.
Now open Subcategory and add any name. As we are using ReDim so we have named as UsingReDim.
Code:
Sub UsingReDim() End Sub
Now before defining ReDim, first define any Integer. Here we have defined it as “A” with array length 3. And store some values in a created array of 3 cells as shown below.
Code:
Sub UsingReDim() Dim A(2) As Integer ReDim A(2) A(0) = 1 A(1) = 2 A(2) = 3 End Sub
Now printing the values stored in Integer A we need to create message box with the help of command MsgBox as shown below.
Code:
Sub UsingReDim() Dim A(2) As Integer A(0) = 1 A(1) = 2 A(2) = 3 MsgBox A(0) MsgBox A(1) MsgBox A(2) End Sub
Now run the code by clicking on the play button or F5 key as shown below.
Now we will use ReDim and it will store more than defined characters in the array. For this use ReDim for defined Integer A. Firstly, we will remove the defined cell count from Dim. The use of Redim function is as follows:
- ReDim A(2) is used for A(0), A(1), A(2) cells array.
- ReDim A(4) is used for A(0), A(1), A(2), A(3), A(4) cells with two extra dummy cells.
Code:
Sub UsingReDim() Dim A() As Integer ReDim A(2) A(0) = 1 A(1) = 2 A(2) = 3 ReDim A(4) MsgBox A(0) MsgBox A(1) MsgBox A(2) End Sub
Once done then run the complete code by clicking on the play button or using F5 key. Once we run the complete code, we will 3 message boxes but with a message containing only “0” as shown below.
Which is because ReDim actually has only 3 values but it did not carry stored values under defined integers. Using Redim only does not store any values and carry forward the previously stored values into it. To apply the same we need to preserve the values of defined integer A, with the help of ReDim. By using Preserve in VBA, we can store the value in ReDim. For this, we will add Preserve after ReDim in VBA Codeas shown below.
Code:
Sub UsingReDim() Dim A() As Integer ReDim A(2) A(0) = 1 A(1) = 2 A(2) = 3 ReDim Preserve A(4) MsgBox A(0) MsgBox A(1) MsgBox A(2) End Sub
Now run the complete code again. We will see 3 message boxes with all the stored values in it as show below. 
Now, you must be wondering why we used A(4) with ReDim Preserve?
Below is the explanation of it. Where 1st, 2nd and 3rd position of array is occupied with values 1, 2 and 3 respectively. And 4th and 5th position which represents 3 and 4 count of array A are dummy cells. Which means, if there is not data stored 4th and 5th position then it will automatically consider the value “0” and message will not get populated.
If we try to see the values stored in 4th and 5th position of ReDim array, then we need to insert a message box for these 2 positions as well.
Code:
Sub UsingReDim() Dim A() As Integer ReDim A(3) A(0) = 1 A(1) = 2 A(2) = 3 ReDim Preserve A(4) MsgBox A(0) MsgBox A(1) MsgBox A(2) MsgBox A(3) MsgBox A(4) End Sub
As we have added message boxed for 4th and 5th position without storing any value. Now run the code again.
As we can see in the above screenshots, for a 4th and 5th position we got message boxes but the value is showing as”0”. Which means explains the above statement. If there is no value defined then ReDim Preserve will automatically consider and fill “0” in the rest of the cells.
VBA ReDim – Example #2
Now let’s see how to use ReDim Statement with String instead of Integer.
Code:
Sub UsingReDim() Dim A() As String ReDim A(3) A(0) = "Customer" A(1) = "Product" A(2) = "Product Id" ReDim Preserve A(4) MsgBox A(0) MsgBox A(1) MsgBox A(2) MsgBox A(3) MsgBox A(4) End Sub
Once done, then run the complete code using F5 key or manually as shown below. 
As we can see in the above screenshot we are getting all the defined and stored characters in first 3 message boxes. And in 4th and 5th box we are getting nothing. As ReDim stores no value if not defined. For Integers it will reflect as “0” but with String, it will show only blank messages.
Pros Of Excel VBA ReDim
- VBA ReDim allows us to redefine the dimension values.
- We can define as many as dimensional arrays with no or zero value and store the values later.
- ReDim meaning Re-Dimensioning gives use the allowances of adding any numbers of data arrays without increasing the size of stored data.
Things To Remember
- Always remember to add Preserve after ReDim in VBA, so that it will store the previously used dimensions.
- If a dimension is defined as String, then any position left blank in assigning values, will carry blank and once we print the same with the help of message box will give blank message box as well.
- If we have defined the dimensions Dim with 3 characters or cell values, then in ReDim we can use any number of dimensions carrying already defined and stored values in Dim.
Recommended Articles
This has been a guide to Excel VBA ReDim. Here we discussed how to use VBA ReDim Statement to remove spaces along with some practical examples and downloadable excel template. You can also go through our other suggested articles–
