Array Operations

VBA has variables and data types, among which the Variant type is a versatile data type.

The Variant type can store various data types such as strings, numbers, dates, and booleans. Additionally, the Variant type can store multiple pieces of data in a single variable as an array.

This page explains how to define and manipulate arrays in VBA.

Defining Arrays

To define an array, declare a variable of the Variant type and assign an array to that variable.

Dim fruits As Variant

fruits = Array("apple", "banana", "orange")

In the example above, the variable fruits is assigned an array with three elements: "apple", "banana", and "orange" using the Array function.

To retrieve an element at a specific position from the array, specify the index of the array. The index starts from 0.

fruits(0) _ ' => "apple"

Arrays can store not only strings but also numbers and booleans.

Dim numbers As Variant

numbers = Array(1, 2, 3)

numbers(1) _ ' => 2

Two-Dimensional Arrays

By storing arrays as elements of another array, you can define two-dimensional arrays.

Dim matrix As Variant

matrix = Array(Array(1, 2, 3), Array(4, 5, 6), Array(7, 8, 9))

In the example above, the variable matrix is assigned an array with three elements. Each element is an array with three elements.

matrix(0)(0) _ ' => 1
matrix(1)(1) _ ' => 5

Manipulating Arrays

Arrays can be manipulated to add or remove elements, but these operations require some complexity.

Adding Elements

To add an element to an array, use the ReDim statement to change the size of the array and add the new element.

ReDim Preserve fruits(3)
fruits(3) = "grape"

You cannot add elements without using ReDim. Also, when changing the size of the array with ReDim, the original array data will be lost, so use the Preserve keyword to retain the original data.

Removing Elements

To remove an element from an array, use the ReDim statement to change the size of the array and remove the desired element.

ReDim Preserve fruits(2)

If you need to remove an element at a specific position, you must redefine the array without the removed element.

Using the For Each Statement to Manipulate Arrays

When performing operations on each element of an array, the For Each statement is convenient.

Dim fruit As Variant

For Each fruit In fruits
  Debug.Print fruit
Next fruit

In the example above, each element of the fruits array is assigned to the variable fruit, and the Debug.Print statement outputs it.

When executed, the output will be as follows:

apple
banana
orange