EDUCBA

Home VBA VBA Resources VBA Array Functions VBA ReDim Array

VBA ReDim Array

Madhuri Thakur
Article byMadhuri Thakur

VBA ReDim Array

Excel VBA ReDim Array

In this article, we will see an outline on the Excel VBA ReDim Array. Normally when we give declare an array we provide a size to the array for the number of elements the array can hold, such type of arrays are known as static arrays. Then there is a different type of array where we do not provide the size of the array and such types of arrays are known as dynamic arrays. But the question arises that once the size of the array has been defined can we change the size of the array again? The answer is yes we can again change the size of the array once it is defined by using the ReDim statement. Thus making it a dynamic array. However, there are some limitations to its users using the ReDim statement alone such as the value stored in the previous array is lost and the new value is stored. Now let us see the mechanism of this ReDim statement with some examples.

 

 

How to Use ReDim Array in Excel VBA?

The following examples will teach us how to use ReDim Array in Excel by using the VBA Code.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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

Example #1

For the first example, we will see how do we use the ReDim statement in the VBA.

Step 1: Insert a new module inside Visual Basic Editor (VBE). Click on Insert tab > select Module.

Insert Module

Step 2: Once the module is inserted we can see it in the project on the left-hand side.

Code:

Sub Example1()

End Sub

VBA ReDim Array Example1-2

Step 3: Declare an array as an integer data type.

Code:

Sub Example1()

Dim A() As Integer

End Sub

Integer Data Type Example1-3

Step 4: Now let us change the dimension of the array using the ReDim statement assign the array with some values.

Code:

Sub Example1()

Dim A() As Integer
ReDim A(2)
A(0) = 2
A(1) = 1
A(2) = 3

End Sub

Dimension Example1-4

Step 5: For the sake of this example let us see what does the value A(2) has and then we resize the array by ReDim statement to A(3) and then again we will see the value of A(2).

Code:

Sub Example1()

Dim A() As Integer
ReDim A(2)
A(0) = 2
A(1) = 1
A(2) = 3
MsgBox A(2)
ReDim A(3)
MsgBox A(2)

End Sub

Resize Example 1-5

Step 6: When we execute the above code we will first see the result of the first array.

Msgbox Example 1-6

When we press OK we should again get a result of three i.e. the same result supposedly.

Msgbox Example1-7

The value turns to 0 because the previous array has lost its value.

Example #2

Let us make a real-life example where we will use ReDim statement to count the number of the last row filled in the column. At first, we will start with this example where we will change the size of the array and in the next example, we will see what happens if we use the preserve statement with the ReDim statement. For this, follow the below steps:

Step 1: For this example declare another subprocedure.

Code:

Sub Example2()

End Sub

Subprocedure Example 2-1

Step 2: Now declare an array and two variables as an integer, note to not give dimension to the array.

Code:

Sub Example2()

Dim numbers() As Integer, size As Integer, i As Integer

End Sub

VBA ReDim Array Example 2-2

Step 3: Now let us calculate the size of the array and store the size in the variable named size.

Code:

Sub Example2()

Dim numbers() As Integer, size As Integer, i As Integer
size = WorksheetFunction.CountA(Worksheets(2).Columns(1))

End Sub

VBA ReDim Array Example2-3

Step 4: Once we have the size of the array now we can resize the array using the ReDim keyword.

Code:

Sub Example2()

Dim numbers() As Integer, size As Integer, i As Integer
size = WorksheetFunction.CountA(Worksheets(2).Columns(1))
ReDim numbers(size)

End Sub

VBA ReDim Array Example 2-4

Step 5: Now let us loop through the array to initialize each element.

Code:

Sub Example2()

Dim numbers() As Integer, size As Integer, i As Integer
size = WorksheetFunction.CountA(Worksheets(2).Columns(1))
ReDim numbers(size)
For i = 1 To size
numbers(i) = Cells(i, 1).Value
Next i

End Sub

VBA ReDim Array Example2-5

Step 6: Now let us display the last element in the array with the Msgbox function.

Code:

Sub Example2()

Dim numbers() As Integer, size As Integer, i As Integer
size = WorksheetFunction.CountA(Worksheets(2).Columns(1))
ReDim numbers(size)
For i = 1 To size
numbers(i) = Cells(i, 1).Value
Next i
MsgBox numbers(size)

End Sub

VBA ReDim Array Example 2-6

Step 7: Before running the code, let us see what we have in column A of sheet 2.

VBA ReDim Array Example2-7

Step 8: Now we can run the code.

VBA ReDim Array Example2-8

Example #3

In addition to the above example when we resized the array with the number of values in the column. What if we again resize the array and then check a specific value in the array. For this, follow the below steps:

Step 1: We will again use the ReDim statement after the last line of the code.

Code:

Sub Example3()

Dim numbers() As Integer, size As Integer, i As Integer
size = WorksheetFunction.CountA(Worksheets(2).Columns(1))
ReDim numbers(size)
For i = 1 To size
numbers(i) = Cells(i, 1).Value
Next i
MsgBox numbers(size)
ReDim numbers(3)

End Sub

VBA ReDim Array Example3-1

Step 2: Now we can check what is the value in numbers(1).

Code:

Sub Example3()

Dim numbers() As Integer, size As Integer, i As Integer
size = WorksheetFunction.CountA(Worksheets(2).Columns(1))
ReDim numbers(size)
For i = 1 To size
numbers(i) = Cells(i, 1).Value
Next i
MsgBox numbers(size)
ReDim numbers(3)
MsgBox numbers(1)

End Sub

VBA ReDim Array Example3-2

Step 3: Run the code by hitting the F5 or Run button.

VBA ReDim Array Example3-3

This value was expected to let us see another value.

VBA ReDim Array Example3-4

It should be 2 but we got 0 because the previous value is lost.

Step 4: Now let us use the preserve statement after the REDIM keyword,

Code:

Sub Example3()

Dim numbers() As Integer, size As Integer, i As Integer
size = WorksheetFunction.CountA(Worksheets(1).Columns(1))
ReDim numbers(size)
For i = 1 To size
numbers(i) = Cells(i, 1).Value
Next i
MsgBox numbers(size)
ReDim Preserve numbers(3)
MsgBox numbers(1)

End Sub

VBA ReDim Array Example3-5

Step 5: Now when we run the code by hitting the F5 or Run button. we will get the desired result.

VBA ReDim Array Example3-3

VBA ReDim Array Example3-7

When we press OK we will see the following.

Explanation:

ReDim is a statement or a keyword used to resize the array as we saw in the above examples. To resize an array we must keep this in mind that the array must be dynamic and must not have a dimension at the declaration.

Things to Remember

There are a few things we need to remember about ReDim:

  • ReDim statement is used after the declaration of the array.
  • ReDim statement does not store the previous array values.
  • To store the previous values we need to use the preserve keyword.
  • The array must not have dimensions at the time of declaration.

Recommended Articles

This is a guide to the VBA ReDim Array. Here we discuss how to use ReDim Array in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA SendKeys
  2. VBA CInt
  3. VBA SubString
  4. VBA Dynamic Array