UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Arrays    
(Redirected from Array)

Related Content:
    Array Links (Link List)
    Functions for VBA Arrays (Chip Pearson)
Arrays vs Collections

Article Information
Related Procedures

- Join()
- Split()
- GetRows()

Associated Errors

- 9 - Subscript Out of Range

In VBA, an array is a collection of values grouped into a single variable. The standard definition of the term array applies in programming as well - "to arrange in a specified order". Perhaps we have a situation that calls for a list of files to be operated on or evaluated, or a list of attachments for an email. Any time we have a list of some sort, it can be put into an array.

We work with arrays based on a numbered element. We create an array that has a certain number of elements (items in the array), and we set and retreive these values from the array using a numeric index which points to the array element we require.


Elements & the Index

An array consists of a number of Elements - items placed in the array. Each array element has a numbered index that describes it's place within the array, and we use this numbered index to refer to the element of choice. The element index is by default a zero-based number. Consider the following example of setting the values of three different elements of a single array:

lngMyArray(0) = 150  'first element
lngMyArray(1) = 300  'second element
lngMyArray(2) = 600  'third element

Arrays vs Collections

See Also: Arrays vs Collections

You may be wondering if we can use some other method to refer to an element other than a numeric index, such as giving the elements meaningful names instead. The answer to this is no - array elements are always ordered and referred to only by their index. For different methods in storing like items and using a named indexer, take a look at using a collection instead.

Data Types with Arrays

An array can be used for any data type or object. Basic types such as Integer, Long and Boolean can be put into an array, and complex types such as Instantiated Class Objects and User-Defined Types can also be loaded into an array. The restriction is that all elements of an array must consist of the same data type that the array was defined with. The exception to this rule is to use an array declared with the Variant data type, but this can be an expensive operation concerning memory and performance and should be used carefully and only at need.

Declaring an Array

Declaring an array is easy enough - simply put a set of parentheses after the variable name in the Dim statement:

Dim lngMyNumberArray() As Long

The presence of the parentheses at the end of the variable declaration tells VBA that this will be an array of items of the specifed data type.

Typically, we declare the array at a set number of elements. While the above example is perfectly legal, it leaves us with the chore of re-defining the array size dynamically from code, which may more complicated than required in many cases. We have a few methods of declaring the size of an array, as demonstrated in the following code:

Dim lngMyArray1(5) As Long
Dim lngMyArray2(0 To 9) As Long

When we supply a size, or upper index, for our array, by default the lower index is set as 0. In the first example, we have declared a variable lngMyArray1 that will hold 6 elements, indexed 0 through 5. In the second example, we have declared an array that holds 10 elements, 0 indexed 0 through 9.

Array Bounds

Every array has a set of Bounds - the set of bounds describes the upper and lower index numbers of an array. Any attempt to refer to elements beyond the scope of the upper and lower bound will result in a "Subscript Out of Range" error.

We can use two functions, LBound() and UBound() to determine the upper and lower bounds of an array at runtime. Passing the array to either of these functions will return the array's lower or upper bound.

Iterating Through an Array

The most common method of iterating through an array is a for/next loop. Consider an example of an array of file paths that we will want to pass into a function that loads them into a mail item. In this example, the function takes a single string to load at a time, so we will iterate through the loop adding the next element every time:


' strFiles is our array
' i is our counter variable
For i = 0 To UBound(strFiles)
 'pass the element "i" to the function
Next i

Iteration can also be used to load an array with values. Consider an array with 10 elements (a lower bound of 0 and an upper bound of 9) which takes numbers, each number being twice the index value:


Dim myNumbers(0 To 9) As Long
Dim i As Integer

For i = 0 To 9
 myNumbers(i) = i * 2

We can print these to the immediate window to see the output:


For i = 0 To 9
 Debug.Print myNumbers(i)

Managing Array Sizes

Likely one of the more difficult aspects of working with arrays is the management of their size. Not all array's upper and lower bounds will be known at the time the variable is declared, and even if it is there are a few points to keep in mind.

Base 0 vs Base 1

By default, VBA uses Base 0 for arrays. This means that all indexes to an array start at 0 unless otherwise specified. This is different from the default for many collections, which is Base 1, so be wary of this when using arrays.

We can specify the lower bound to be used in array declaration in two ways. First, we can declare the lower bound explicitly when the variable is dimensioned:

'creates a 1-based array with 10 elements
Dim MyArray(1 To 10)

Alternatively, we can specify the module level Base to use by putting Base 0 or Base 1 in the Options section of the module header:

Option Compare Database
Option Exlicit
Option Base 1

All VBA modules default to Option Base 0, so don't be alarmed if you haven't yet seen this - it is not widely used.

Resizing an Existing Array

Often, we need to increase an array's dimension dynamically through code as an undetermined amount of elements are added to the array. In this case the array needs to be re-dimensioned to accept either more or less elements than it currently does:

'increase array size by one
Redim MyArray(UBound(MyArray) + 1)

Only Arrays that are declared without boundaries can be ReDim'd. This is useful when an indeterminable amount of elements will be required. An example case would be reading a list of files from a directory and increasing the array's upper bound with each file returned by the Dir() function.

It should be noted that performing a ReDim operation will delete the existing data, unless you specify the Preserve keyword discussed in the next section.

Preserving Existing Data when Redimensioning

In order to ensure that exiting element data is retained when resizing an array using the Redim statement, we must also include the Preserve keyword:


Redim Preserve MyArray(UBound(MyArray) + 1)

When using the Preserve keyword on a multi-dimensional Array, please note that you can only resize the last dimension of the array.

Checking for Empty Arrays

An array is said to be empty or unallocated when it has been dimmed with no bounds specified, such as in our very first example:

Dim MyArray() As Long

Creating an unallocated array is useful in cases where we intend to handle it's sizing dynamically, but we will need some method to determine whether the array has been allocated or not. A common approach to this problem is by creating a utility function that accepts an array argument and attempts to retrieve an upper bound reading on it. If we have an error thrown because there are not yet bounds to the array, we can discern that the array is unallocated and should be redimensioned at it's base index. If a bound reading is successful, we can then redimension an array at the next number past the upper bound. Here is an example:


Dim MyArray() As Long

If IsAllocated(MyArray) Then
 Redim Preserve MyArray(UBound(MyArray) + 1)
 Redim MyArray(0)
End If
Private Function IsAllocated(v As Variant) As Boolean
 On Error Resume Next
 Dim x
 x = UBound(v)
 IsAllocated = Not Err.Number
End Function

Chip Pearson offers an unparalleled collection of functions for managing arrays. It can be found here: http://www.cpearson.com/excel/vbaarrays.htm

Multi-Dimensional Arrays

Arrays need not be limited to a single dimension either - they can take multiple dimensions for storing information. For instance, one can put two dimensions into the array to represent a flat column/row style layout, or three dimensions to physically store a conceptual implementation of a cube-based storage system.

Create a multidimensional array by specifying the bounds of the subsequent dimensions, separated by a comma:

Dim MyTwoDimArray(5, 5) As Long
Dim MyThreeDimArray(5, 8, 10) As Double

The first example could be used to store a layout of numbers, where the second may be used to store three-dimensional point coordinate information. We can define arrays with as many dimensions as we would like, though in practicality it is very rare for more than three to be required. Also, we should be aware that added dimensions to arrays significantly increases the memory requirement of the variable and therefore overall performance.

Sorting Arrays

This Section could use expansion. Please help us by adding to this section if you have knowledge of the topic.

Search web for algorithms: QuickSort, BubbleSort, SelectionSort, ShellSort


Edit Discussion
Custom Search

Thank you for your support!
This page has been accessed 33,618 times.  This page was last modified 14:34, 11 March 2015 by mbizup. Contributions by Jack Leach and Brent Spaulding  Disclaimers