Arrays

An Array is used to store sets of data, and can store multiple variables of the same data type. It can be seen as a ‘Range’ of data. The picture below shows the Range(“A1:A12”) with the months of the calendar year. If we want to store that data set we would use an Array. We can name arrays following the same rules that apply to variables.

Array Declaration

Arrays must be declared and defined as we do with variables. Array declaration requires the number of array elements in parenthesis. As we will see later, we can also declare an array as Variant. 

Every array has a lower bound (LBound) and an upper bound (UBound) implicit in the array declaration parenthesis. We could use either of the following ways to declare an array for the example above.

 Dim MonthArray(11) As String

 Dim MonthArray(0 To 11) As String

 Dim MonthArray(1 To 12) As String

In all three cases the array is declared with 12 elements (string values). In the first two the lower-bound is 0, while in the last is 1. If only the upper bound is given, Excel sets the lower bound to 0. In order to set the lower bound to 1 we can use the statement ‘Option Base 1’ in the declarations section at the top of the module. 

We can use the LBound and UBound functions to get the lower and upper bound numbers of an array respectively as follows (note we are using variables to store those numbers):

 MyArrayUpper = UBound(MonthArray)

 MyArrayTotalElements = UBound(MonthArray) - LBound(MonthArray)

To define or assign values to an array we need to specify the index of item within the dimension in parenthesis. Here’s how we add month names to an array.

 Dim MonthArray(11) As String

 MonthArray(0) = "January"

 MonthArray(1) = "February"

 MonthArray(2) = "March"

 '…

As we declared the array with the upper bound number of elements only, the lower bound is set to 0, and therefore, the first month (January) is assigned to the 0 index of the array: MonthArray(0). But if we want to start with January as index 1 we can use both lower and upper bounds in the declaration (or use Option Base 1 as explained earlier). 

 Dim MonthArray(1 To 12) As String

 MonthArray(1) = "January"

 MonthArray(2) = "February"

 MonthArray(3) = "March"

 '…

Considering that data is in the range “A1:A12” (as shown earlier), we would rather assign values to the array by looping through the range as follows:

 Dim irow As Integer

 Dim MonthArray(1 To 12) As String

 For irow = 1 to 12

     MonthArray(irow) = Range("A" & irow).Value

 Next irow

There is another more efficient way sometimes to assign values to an array and it is using the ‘Array’ function. This function returns a Variant containing an array, so it requires to declare the array as Variant instead. 

 Dim WeekDayArray As Variant

 WeekDayArray = Array("Sunday", "Monday", "Tuesday", "Wednesday", _

 "Thursday", "Friday", "Saturday")

Then we can retrieve or display the data in the array with the index of the target element in parenthesis.

 MsgBox WeekDayArray(4)

Multidimensional Arrays

In the example above we have seen an array with just one dimension. But arrays can have more than one dimension, thus having more than one index in parenthesis separated by a comma. Let’s have a look at an array with two dimensions that stores the numbers of a Sudoku puzzle. 

 Dim SudokuPuzzle(1 To 9, 1 To 9) As Integer

We can manually assign the values to the array for the solved Sudoku example below by specifying the row (dimension 1) and column (dimension 2) numbers in the layout. Here’s how that would look like:

 SudokuPuzzle(1, 1) = 5

 SudokuPuzzle(1, 2) = 2

 SudokuPuzzle(1, 3) = 9

 '…

 SudokuPuzzle(2, 1) = 7

 SudokuPuzzle(2, 2) = 4

 '…

But we would rather loop through the range to fill the array as we did earlier, but with two For/Next loops in this case. To get a particular number location in the Sudoku puzzle grid we would then use the row and column numbers in the array. The following displays a message box with the number in the center of the Sudoku puzzle.

 MsgBox SudokuPuzzle(5, 5)

Dynamic Arrays

A dynamic array is an array that does not have a set size. It is declared without the upper-bound or range number of array elements by leaving the parenthesis empty. 

 Dim myArray()

Then, it can be declared with a given value during program execution with ReDim. That could be useful when having arrays that depend on some of the macro’s outcome. For example, let’s imagine you want to store data in arrays as a result of applying the filter to some columns. Initially, you do not know how’s the result going to look like, but then when you filter you’ll know the exact number of items. ReDim declaration works in the same way Dim does.

 ReDim myArray(1 To 10)

If the array already contains some entries and you want to keep that information, you need to use the ‘Preserve’ statement.

 ReDim Preserve myArray(1 To 10)

<<< Previous | Next >>>