VBA Dynamic Array

Excel VBA Dynamic Array

In this article, we will see an outline on Excel VBA Dynamic Array. Arrays are an important feature of VBA which are used to store multiple values in a variable. But there are two types of arrays. One is called static while another is called dynamic. In static arrays, the lower bound and upper bound of the variable is static which means it does not change. However, in dynamic arrays, we can change the size of the array. But how it is done? When we declare an array we do not provide the size of the array and thus making it dynamic. If we want to change the size of the array again we use the ReDim keyword.  The important fact to note is that the lower bound of the array does not change and only the upper bound of the array is changing.

How to Use Dynamic Array in Excel VBA?

First, before we re-size the dynamic array we need to declare the array without the size of the array. At the time of the run time, we can resize the array as per our requirement. Below are the examples of an excel VBA Dynamic Array:

Example #1

Let us begin with a basic example on how to declare a dynamic array in VBA. We know how we declare a static array.

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

Step 2: Define the subprocedure where we will declare our first dynamic array.

Code:

Sub Example1()

End Sub

Step 3: So declare an array for the dynamic array.

Code:

Sub Example1()

Dim numbers() As Integer

End Sub

Step 4: Currently the numbers are an array which can hold integer values and is a dynamic array if we want to resize the array we can do it with the Re Dim Keyword.

Code:

Sub Example1()

Dim numbers() As Integer
ReDim numbers(3)

End Sub

Step 5: Now the array has a size of three that can hold integer values.

Code:

Sub Example1()

Dim numbers() As Integer
ReDim numbers(3)
numbers(0) = 25
numbers(1) = 26
numbers(2) = 27
numbers(3) = 28

End Sub

Step 6: Now we can assign these values to the ranges in sheet 1.

Code:

Sub Example1()

Dim numbers() As Integer
ReDim numbers(3)
numbers(0) = 25
numbers(1) = 26
numbers(2) = 27
numbers(3) = 28
Worksheets("Sheet1").Range("A1").Value = numbers(0)
Worksheets("Sheet1").Range("A2").Value = numbers(1)
Worksheets("Sheet1").Range("A3").Value = numbers(2)
Worksheets("Sheet1").Range("A4").Value = numbers(3)

End Sub

Step 7: Run this code by hitting the F5 or Run button which is placed on the topmost ribbon of VBE. we can see the result on Sheet 1

Example #2

We will discuss this example in two steps in two examples. To explain the use of the Preserve statement in a dynamic array. For this example, I have values in sheet 3 column 1. I want to fetch the last value in the column and display it. But we can also add or delete the values for the column.

Step 1: In the same module let us start with another subprocedure.

Code:

Sub Example2()

End Sub

Step 2: Declare three values as integers.

Code:

Sub Example2()

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

End Sub

Step 3: Let us assign a size to the last value in the column.

Code:

Sub Example2()

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

End Sub

Step 4: Now we can resize the array with the value in variable size.

Code:

Sub Example2()

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

End Sub

Step 5: Now we will use the If statement to initialize all the elements of the loop.

Code:

Sub Example2()

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

End Sub

Step 6: Display the last value of the column.

Code:

Sub Example2()

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

End Sub

Step 7: Run this code by hitting the F5 key.

We can see the last value was 28 so we got result 28.

Step 8: Now add another value to sheet 3 in cell A5 and re-run the code.

Example #3

Now if we use the Redim statement at the time of execution let us just say before the Msgbox function in the above code we will not get the desired result.

Step 1: For example in the above code let us make the below changes.

Code:

Sub Example3()

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

End Sub

After the loop, I changed the size of the array again.

Step 2: When we run the above-modified code.

This was not the value we needed.

Step 3: The problem in the above code was that the ReDim keyword removes any of the existing data from the array and so in order to preserve the previous value in the array we use the preserve statement as follows,

Code:

Sub Example3()

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

End Sub

Step 4: Run the above code again.

So what changed is that using the preserve keyword the previous value of the array was preserved and not removed.

Explanation of VBA Dynamic Array:

As I said earlier Arrays are a very important part of our programming structure. It is used to store multiple values for the user the only condition is that the data type of those values remains the same as of the data type of the array. In a Dynamic Array, the size of the array is changed at the run time level.

Things to Remember

• VBA supports both dynamic as well as static arrays.
• Static arrays do not change the size.
• Dynamic arrays size can be changed any time between the runtime.
• The lower bound of the dynamic array remains unaffected.
• If we want to preserve the size of the previous array we use the Preserve Keyword.

Recommended Articles

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

1. VBA RGB
2. VBA Concatenate Strings
3. VBA SubString
4. VBA Resize