What are Arrays and How to Use Them - VBA Tutorial

An array is a set of values that are stored in a structured, linear way, allowing you to store and manipulate a large number of values in a single variable.

Arrays can be of any data type, including numeric, string, and Boolean, and they can have any number of dimensions, from a simple one-dimensional list to a multi-dimensional grid. You can use arrays to store a list of items, such as a list of names or a list of numbers, or you can use them to store data in a more structured way, such as a table or a matrix.

To declare an array in VBA, you can use the Dim statement followed by the name of the array and the size of the array. For example:

Dim myArray(1 to 10) As Integer

This would create an array called "myArray" that can hold 10 integer values, with the array elements numbered from 1 to 10. You can then access and modify the values in the array using the array name and the index of the element you want to access. For example, to access the fifth element of the array, you would use "myArray(5)".

There are also several functions available in VBA that allow you to manipulate arrays, such as the "UBound" function, which returns the upper bound of an array, and the "ReDim" statement, which allows you to resize an array.

Full List of VBA Array Functions:

UBound - Returns the upper bound of an array

LBound - Returns the lower bound of an array

ReDim - Resizes an array and allocates new memory for it

ReDim Preserve - Resizes an array and preserves the data it contains

Erase - Deletes all elements from an array and frees the memory used by the array

Split - Splits a string into an array of substrings based on a specified delimiter

Join - Concatenates the elements of an array into a single string, using a specified delimiter

Filter - Returns a subset of an array based on specified criteria

Sort - Sorts the elements of an array in ascending or descending order

What are Array Boundaries?

In an array, the bounds refer to the range of indices that can be used to access the elements of the array. In VBA, the lower bound of an array is the first index that can be used to access the elements of the array, and the upper bound is the last index that can be used.

For example, consider the following array:

Dim myArray(1 to 10) As Integer

In this case, the lower bound of the array is 1 and the upper bound is 10, meaning that the elements of the array can be accessed using indices ranging from 1 to 10. This means that the first element of the array is "myArray(1)", the second element is "myArray(2)", and so on.

You can use the "UBound" function to determine the upper bound of an array, and the "LBound" function to determine the lower bound. For example:

upperBound = UBound(myArray)
lowerBound = LBound(myArray)

This would assign the upper bound of "myArray" to the variable "upperBound" and the lower bound to the variable "lowerBound".

Example Array Declarations

How to Declare a One-Dimensional Array

To declare a one-dimensional array of integers with 10 elements, you can use the following code:

Dim myArray(1 to 10) As Integer

How to use ReDim to Resize Array

You can also use the "ReDim" statement to resize an array that has already been declared. For example:

ReDim myArray(1 to 20) As Integer

This would resize the array "myArray" to have 20 elements, discarding any data that was previously stored in the array.

How to Declare Array of Employee Names

To declare an array of employee names, you could use the following code:

Dim employeeNames(1 to 50) As String

How to Declare Array of Stock Prices

To declare an array of stock prices, you could use the following code:

Dim stockPrices(1 to 100) As Double

How to Declare Array of Customer Orders

To declare an array of customer orders, you could use the following code:

Dim orders(1 to 1000) As Integer

How to Declare Array of Boolean Values

To declare an array of Boolean values indicating whether a product is in stock or not, you could use the following code:

Dim inStock(1 to 500) As Boolean