VBAsics

Elements of Arrays

By Kevin Carmichael

Putting the Power of Arrays to Work in Your VBA Application

Most new developers easily grasp the idea of variables and their use. However, some often overlook, or have difficulty with, another useful type of variable: the array. For some reason, arrays illicit feelings ranging from apprehension to fear in some programmers. Instead of tapping into the power and ease that arrays can provide, programmers who aren't familiar with them will write lines and lines of unnecessary code to avoid them.

In the right circumstances, arrays will allow you to write tighter, more efficient code. Arrays give you the power to create intricate matrixes that group related information, eliminating the need for numerous individual variables. Depending on your experience, this article will introduce or review the basics of creating and using VBA arrays.

What Is an Array?

First, a definition: an array is a variable with one or more dimensions, to a maximum of 60 (in VBA), that binds data items together. The individual data items are then referenced by their location, known as an index, within the array variable. An array has lower and upper bounds, specified by the programmer, that indicate the range of available elements.

By default, VBA marks the lower bound as zero; however, this can be controlled. The lower bound of an array can be modified by using the Option Base statement. You can change the value of the Option Base however, only at the module level. Another way to control the lower bound is by using the To clause, which is discussed later. By default, the base is zero.

Instead of having numerous individual variables containing related information, you can create a single array with a number of elements to handle the information. This array can then be referenced by one name and each element by its index. This may have been a mouthful, but as the cliché goes, a picture is worth a thousand words (see FIGURE 1).

FIGURE 1: A representation of a one-dimensional array with eight elements. By default, VBA sets the lower bound as zero. The elements are inclusive (0-7).

Maybe the diagram in FIGURE 1 doesn't speak one thousand words, but it's a good way to demonstrate how a single array can contain many individual elements. Array elements are similar to single variables. As with single variables, array elements have a data type and can be assigned values. So why bother using arrays?

Remember, an array groups data items. Rather than handling a large number of variables as you code, associated data can be packaged into one variable that can be referenced by a single name and its index. Less variables to handle while coding means less frustration, right? Well, hopefully.

Creating an Array

Creating an array is straightforward, and can be done a number of ways. How you create an array may depend on your preference and/or the purpose of the code. As with single variables, not specifying a type will result in a Variant. When creating an array, place parentheses at the end of the variable name. This indicates to VBA that it's an array. Within the parentheses you may or may not specify the upper and lower bounds. Not specifying the bounds of an array creates a dynamic array. These types of arrays will be discussed later in this article.

Common ways of creating an array include:

' Creates an array with 11 elements (0-10 inclusive).
Dim MyNewArray(10) As String

' Creates a two dimensional, 10-by-100 array
' using the To Clause discussed later.
Dim MyNewArray(1 To 10, 1 To 100) As Integer

' Creates a dynamic array, discussed later.
Dim MyNewArray() As Double

Note: The elements of an array are initialized depending on the type of the array. Elements of a numeric array will contain zero, a variable-length string (String) will contain a zero-length string, a fixed-length string (String * n) will contain zeros, and a Variant will be Empty.

Another way of creating an array is with the VBA Array function. As previously mentioned, you can specify the data type when creating an array. However, the Array function will only create an array as a Variant. When you use the Array function, you also assign the values of the elements by passing them as comma-separated arguments within the parentheses. For example:

Dim MyNewArray As Variant

MyNewArray = Array("January", "February", "March")
Debug.Print MyNewArray(0)  ' January

Passing the values of the elements when creating the array is required. Otherwise, you'll have an array of zero length. A zero-length array is an array variable with no available elements. As mentioned, the Option Base setting can affect the lower bound of an array, but not with the Array function. Using the Array function will always give an array a lower bound of zero.

Another way of affecting the lower bound setting is to use a To clause within the parentheses. The To clause cannot be used with the Array function, and will be discussed later.

Using Arrays

Assigning or accessing data in an array is done by referring to the array name and one of its valid indexes. An array can contain many elements, so referring just to the name of the variable will not specify which element to use. The following is a comparison of using single and array variables:

Option Base 0                ' At the Module level.

Dim MyString As String       ' Single string variable.
Dim MyNewArray(4) As String  ' Dimensioned string array.

MyString = "Using VBA Arrays"
MyNewArray(0) = "January"
MyNewArray(1) = "February"
MyNewArray(2) = "March"
MyNewArray(3) = "April"

Debug.Print MyString         ' Using VBA Arrays.
Debug.Print MyNewArray(2)    ' March.

Referring to an index of an array that doesn't exist (outside the available bounds) will result in an error. The bounds of an array are inclusive. Therefore, the previous array contains four elements (zero to three, inclusive).

Where Do We Start?

By default, VBA declares the lower bound of arrays as zero. As mentioned previously, however, you can control this setting a number of ways. The Option Base statement allows you to set the lower bound setting to zero or one:

Option Base 1

Dim MyNewArray(1)

MyNewArray(0) = "Monday"   ' Will generate an error.
MyNewArray(1) = "Monday"

Debug.Print MyNewArray(1)  ' Monday.

If you don't change the default lower bound, it is good practice to specify the setting in your code so other programmers viewing your code don't make false assumptions. Also, decide what you want the lower bound setting to be in your application and stick with it; changing the setting midstream will only create confusion.

Another way of indicating the lower bound of an array is using a To clause. The To clause doesn't limit you to zero or one, and is not affected by the Option Base setting. For example, an array containing information related to years can have an index equal to the years themselves:

Option Base 0

' Three-element array, ignores Option Base setting.
Dim MyNewArray(1998 To 2000)

' Force lower bound to 1, ignores Option Base setting.
Dim MyNewArray(1 To 12) As String

All Mixed Up

Assigning mixed data types to an array is no different; simply ensure the array is a Variant data type. Assigning mixed data types to an array created as a specific data type would obviously cause a run-time error:

Dim MyNewArray(1 To 4) As Variant

MyNewArray(1) = "January"
MyNewArray(2) = 2
MyNewArray(3) = Month(Date)
MyNewArray(4) = "April"

An easier method would be:

Dim MyNewArray As Variant

MyNewArray = Array("January", 2, Month(Date), "April")

Multidimensional Arrays

Now that we're getting comfortable with arrays, let's move on and take a look at multidimensional arrays.

Using single dimensioned arrays is common and useful. However, you can take arrays a step further by adding more dimensions (see FIGURE 2). VBA arrays can be declared with up to 60 dimensions. (Note: Keep in mind 60 dimensions is a technical limitation, and even though you could create up to 60 dimensions, doing so would be difficult to grasp and handle programmatically. Also, an empty array takes up as much memory as filled arrays, and if you run out of memory, data will be swapped to/from your disk. Swapping will compromise performance and will have an angry mob of townspeople, also known as users, immediately at your door.) A common analogy of a multidimensional array is a spreadsheet (see FIGURE 3). As a spreadsheet contains rows, columns, and cells (intersections of a row and column), a multidimensional array creates a similar matrix. The first dimension represents the rows and the second dimension represents the columns.

FIGURE 2: A representation of a two-dimensional array. The array elements are referenced by the dimension, then the index.

FIGURE 3: A representation of a two-dimensional array.

For example, consider the scenario of handling information relating to revenue broken down by month and week, as shown in FIGURE 4. A multidimensional array could handle it.

' 4 weeks, 12 Months
' Ignores Option Base setting.
Dim MyNewArray(1 To 4, 1 To 12) 

' Assign January's weekly sales
MyNewArray(1,1) = 20000
MyNewArray(2,1) = 25000
MyNewArray(3,1) = 19000
MyNewArray(4,1) = 22500

' February's sales are 7% increase from
' January's weekly sales.
MyNewArray(1,2) = MyNewArray(1,1) * 1.07
MyNewArray(2,2) = MyNewArray(2,1) * 1.07
MyNewArray(3,2) = MyNewArray(3,1) * 1.07
MyNewArray(4,2) = MyNewArray(4,1) * 1.07

FIGURE 4: The scenario of handling information relating to revenue, broken down by month and week.

As with single-dimensioned arrays, each dimension in a multi-dimensioned array has lower and upper bounds. When specifying bounds for each dimension, you are not limited to one style:

Dim MyNewArray(1 To 5, 10, 1 To 20)

This specifies the upper and lower bounds of the first dimension, upper bound of the second, and upper and lower bounds of the third. The lower bound of the second dimension will have the setting of the Option Base statement.

Resizing Arrays

The title of this section is a little misleading, because you can't resize arrays in which you have declared the upper and/or lower bounds (as in the type of arrays so far in this article). However, to size and/or resize an existing array, declare the array as dynamic. To do so, use the Static, Dim, Private, or Public statement and the leave the parentheses of the array empty.

The ReDim statement will allow you to change the bounds and dimensions of an array. However, existing data in the array will be lost. To maintain existing data in an array when re-sizing, use the Preserve keyword.

The Preserve keyword of the ReDim statement allows you to maintain existing data when altering the size of an array. However, when using the Preserve keyword you can only change the elements of the last dimension and you cannot change the number of dimensions. This means that a one-dimensional array's elements can be altered with no problem, but in a multidimensional array, only the last dimension's elements can be altered while maintaining existing data. For example:

' Dynamic array, can be resized.
Dim MyNewArray() As Integer
ReDim MyNewArray(1 To 12)

' Add additional elements maintaining any existing data.
ReDim Preserve MyNewArray(1 To 20)

' Resize the array clearing existing data.
ReDim MyNewArray(1 To 5)

' Can change the size of the last dimension only.
ReDim MyNewArray(1 To 12, 1 To 5)
ReDim Preserve MyNewArray(1 To 12, 1 To 6)

When using the ReDim statement, ensure the correct spelling of the declared array. If misspelled, a second array will be created.

Related Functions

Knowing the first and last elements of an array is often necessary. For example, you will often process an array within a For loop. You can check the values of the lower and upper bounds of an array using the VBA LBound and UBound functions (see FIGURE 5).

Option Base 0

Dim MyNewArray(5)

Debug.Print Lbound(MyNewArray)     ' 0
Debug.Print Ubound(MyNewArray)     ' 4

Option Base 1

Dim MyNewArray(5)
Debug.Print Lbound(MyNewArray)     ' 1
Debug.Print Ubound(MyNewArray)     ' 5

Dim MyNewArray(10 To 100)

Debug.Print Lbound(MyNewArray)     ' 10
Debug.Print Ubound(MyNewArray)     ' 100

Dim MyNewArray(1 To 10, 1 To 20, 1 To 30)

Debug.Print Ubound(MyNewArray)     ' 10
Debug.Print Ubound(MyNewArray, 2)  ' 20
Debug.Print Ubound(MyNewArray, 3)  ' 30

ReDim MyNewArray(Ubound(MyNewArray) + 5)

For x = Lbound(MyNewArray) To Ubound(MyNewArray)
  Debug.Print MyNewArray(x)
Next x

FIGURE 5: Use the VBA Lbound and Ubound functions to check the values of the lower and upper bounds of an array.

You should now have a basic understanding of creating and using VBA arrays. When handling related information, arrays allow you to package data items together in one variable.

This article provides you with the basics to start using arrays in your application. Use arrays when they are needed. This may seem obvious, but as a former software instructor, I have seen too many people grasp a new concept and then overuse it in their applications. Arrays can be resource-intensive, so just as with any other programming concept or methodology, use them efficiently and effectively.

For further information, use another item that is often overlooked by new users of VBA: The online Help included with VBA. It provides valuable and extensive documentation.

Kevin Carmichael is a Programmer/Analyst with the Standards Council of Canada, where he develops and supports custom solutions using SQL Server, Visual Basic, and Microsoft Office. As a former software course developer and instructor, Kevin has extensive experience instructing and assisting people applying many software packages, including VBA, in a variety of industries. He can be reached at kcarmichael@scc.ca.