Understanding Dynamic Arrays - VBA Tutorial

A dynamic array is an array that can change size on the fly, unlike a static array that has a fixed number of elements.

Contents:

1. What are Dynamic Arrays?

1.1. How to Declare a Dynamic Array

2. Example Dynamic Array

2.1. Macro to Writing and Recall From a Dynamic Array

2.2. Example Macro Explained

What Are Dynamic Arrays in VBA?

Declaring an array using a ReDim statement is what distinguishes dynamic arrays from static arrays, and allows you to create arrays that can grow or shrink as needed at runtime.

This allows an array to change size while a macro is running. Meaning that you can add or remove elements from the array as needed, and are useful when you don't know how many elements you will need to store in the array.

How to Declare a Dynamic Array

To declare a dynamic array, you can use the Dim statement followed by the name of the array, but leave out the size of the array. For example:

Dim my_array() As Integer

This would create an empty array called "my_array" that can hold integer values.

You can then use the "ReDim" statement to resize the array and add elements to it:

ReDim my_array(1 to 10) As Integer

This would resize the array "my_array" to have 10 elements.

Values can then be modified using the array name and the index of the element you want to change. For example:

my_array(1) = 10 
my_array(2) = 20
my_array(3) = 30

It is important to note that when resizing a dynamic array, the values that have previously been stored in the array will be lost. To avoid this, you can also use the "ReDim Preserve" statement to preserve the data in the array while resizing it. For example:

ReDim Preserve my_array(1 to 15) As Integer

This would resize the array "my_array" to have 15 elements, while preserving the data that was previously stored in the array.

Example Dynamic Array

Here is an example macro that demonstrates how to use a dynamic array in VBA:

Sub DynamicArrayExample()

    ' Declare a dynamic array to hold a list of names
    Dim names() As String
    
    ' Prompt the user to enter the number of names
    Dim num_names As Integer
    num_names = InputBox("Enter the number of names:")
    
    ' Resize the array to hold the specified number of names
    ReDim names(1 to num_names) As String
    
    ' Loop through the array and prompt the user to enter each name
    Dim i As Integer
    For i = 1 To num_names
        names(i) = InputBox("Enter name " & i & ":")
    Next i
    
    ' Display the names in a message box
    Dim message As String
    message = "The names you entered are:" & vbNewLine
    
    For i = 1 To num_names
        message = message & names(i) & vbNewLine
    Next i
    
    MsgBox message
    
End Sub

This macro prompts the user to enter the number of names they want to enter, and then creates a dynamic array to hold those names. It then uses a loop to prompt the user to enter each name, and stores the names in the array. Finally, it displays the names in a message box.

Example Macro Breakdown

The first line of the macro declares a dynamic array called "names" that can hold string values.

Dim names() As String

The next line prompts the user to enter the number of names they want to enter, and stores the value in a variable called "num_names".

Dim num_names As Integer
num_names = InputBox("Enter the number of names:")

Next we use the "ReDim" statement to resize the "names" array to hold the specified number of names.

ReDim names(1 to num_names) As String

Then the macro uses a loop to prompt the user to enter each name, and stores the names in the "names" array.

Dim i As Integer
For i = 1 To num_names
    names(i) = InputBox("Enter name " & i & ":")
Next i

Finally, the macro builds a message containing the names that were entered, and displays the message in a message box.

Dim message As String
message = "The names you entered are:" & vbNewLine

For i = 1 To num_names
    message = message & names(i) & vbNewLine
Next i

MsgBox message