Working with arrays

As you probably know, an array is a set of sequentially indexed elements having the same data type. Each element of an array has a unique identifying index number. VBA Arrays are very fast, are therefore universally used. Arrays are flexible: they can have multiple dimensions and can be used to implement lists or matrices or more abstract data spaces.

As an example you could create the simple strWords array below. In this case, we have a simple fixed length or Static array (Dynamic arrays are more flexible, but Static is an easy starting point.) The first strWords array element gets the value "Hello" assigned, the second "World". So here we have the variable strWords representing a list. Finally, a For Each loop is used to write the elements in the list to the Immediate window.

Dim strWords(0 To 1) As String
strWords(0) = "Hello"
strWords(1) = "World"
Dim str As Variant
For Each str In strWords
    Debug.Print str
Next

Building on the basic functionality, users have written functions that perform all kind of operations on them. These are made available in the Code VBA add-in in module modArrayPreconditions which is a automatically added to your project when you use one of the functions included under VBA » Array » Fragments submenu. Note that you can extend the fragments collection with your own procedures and code snippets. This page introduces you to the array essentials. In addition, information is given concerning the most common used array functions, special procedures for lists, matrices, arrays with objects, testing preconditions and more are on separate pages.

• Declaring array variables and assigning values
• Using dynamic arrays
  • Array function
  • Split function
  • Set size of dynamic array ReDim Preserve
• Loop through an array, LBound and UBound
• Pass array between procedures
• Verify input array preconditions
  • Is variable an array
  • Is Array Dynamic
  • Is Array Allocated or Empty
  • Number of array dimensions
  • Number of elements in the array

Declaring array variables and assigning values

To be able to work with an array variable we first have to declare it. In addition to giving it a name and deciding on the type of the data it will store we may claim a fixed space - static or leave the size open relying on the process that assigns the data to handle that.

Static array

A static array is an array that is sized in the Dim statement that declares the array. E.g.

Dim arrNumbers(1 To 10) As Long
arrNumbers(1)=1
arrNumbers(2)=10 'etcetera'

You cannot change the size of a static array, which limits the use of this kind of array to situations where you know in advance how many items will be stored.

Using dynamic arrays

A dynamic array is an array that is not sized in the Dim statement. Dim arrNumbers() As Integer declares the name and specifies the type, the dimensions are determined either by the procedure filling the array such as the ones below, or using ReDim.

Filling a dynamic array using the Array function

A convenient way to fill a dynamic array with a given list of values is by using the Array function, e.g.

Dim varWeekDays() As Variant
varWeekDays = Array("Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun")

Notes

• In the above, varWeekDays(2) (assuming the default Option Base 0) contains "Wed" - the index starts with 0.
• This neat function does not work if you declared a static array such as Dim varWeekDays(1 To 7) As Variant. In such a case you get compile time error 'Can't assign to array'.
• The array is declared As Variant. You can also make a list of other types than string, such as numbers Array(1,2,3) or dates
• The items are separated using a comma.

Filling a dynamic String array using the Split function

A lot of input comes to us as String. The Split function can turn the string into an array like this:

Dim strWeekDays() As String
strWeekDays = Split(Expression:="Mon,Tue,Wed,Thu,Fri,Sat,Sun", Delimiter:=",")

More details on the use of the Split function can be found here.

Set size of dynamic array ReDim Preserve

A dynamic array is an array that is not sized in the Dim statement. Instead, it is sized with the ReDim statement. E.g.,

Dim DynamicArray() As Long
ReDim DynamicArray(1 To 10)

You can change the size of a dynamic array but not the data type. To keep the data already contained in the array you must use the Preserve keyword:

ReDim Reserve DynamicArray(1 To 12)    

Loop through an array

There are two ways to loop arrays. The most common practice is using For i = LBound to UBound of the array; the For Each element loop can only be used with one-dimensional arrays.

For i LBound to UBound of array

The common practice for looping arrays is using the lower and upper bound functions: LBound giving the lowest value of the index of the array, UBound the highest value. The starting (base) point of an array index is determined by multiple factors, which is resolved by using these functions.

Dim i As Integer
For i = LBound(strWords) To UBound(strWords)
    Debug.Print strWords(i)
Next

To traverse the list in reverse order, use

For i = UBound(strWords) To LBound(strWords) Step -1

For Each element in array

An alternative for the common practice of looping arrays using For i LBound to UBound is using For Each. This has the advantage of being more intuitive; when looping a list we don't care about indexes and bounds. For Each however also has some disadvantages: firstly, you can't walk through the list in reverse direction, secondly, the index number is not explicitly available while in some cases you may need it in your process, finally, you are not allowed to specify the type of the selected element, you must use As Variant which is less informative.

Dim str As Variant
For Each str In strWords
    Debug.Print str
Next

Pass array between procedures

Arrays passed between procedures using arguments generally are typed As Variant, for example:

Public Sub ReverseArrayInPlace(InputArray As Variant)

The reason is that the case that you know the exact type or size will occur hardly ever.

Verify input array preconditions

You generally know the details of a variable type Variant, e.g. whether id=it is an array, if it is dynamic, etcetera -if you program is providing the variable content. If however either you have created generic procedures which are being used by others, or you are dependent on another process as the provider of the value, then it is good practice to add some basic checks to your procedure. Such checks are often called 'preconditions', and for arrays these are included in module modArrayPreconditions which is a automatically added to your project when you select one of the functions included in the VBA » Array » Fragments submenu which displays the Array related stuf in the Code VBA add-in fragments

Is variable an array

Public Sub TestVariantIsArray(Variable As Variant, Optional VariableName As String, Optional Procedure As String, Optional Module As String)
    If IsArray(Variable) = False Then Err.Raise mclngErrNumberNotAnArray, Module & IIf(IsMissing(Module), "", ".") & Procedure, VariableName & " is not an array"
End Sub

Is Array Dynamic

If a procedure is intended to determine or change the number of elements of an error, we need to make sure the array is dynamic. For this an adapted version of the IsArrayDynamic procedure by Chip Pearson has been added to the Array fragments collection.

Is Array Allocated or Empty

An array is allocated if it is either a static array or a dynamic array that has been sized with the ReDim statement. Static arrays are always allocated and never empty. You can test if a variable is allocated with the IsArrayAllocated included under Array Fragments. The reverse, the function IsEmpty can also be found there. Again, both procedures included are adapted from Chip Pearson.

Number of array dimensions

Some procedures such as the above, work on any array, regardless of the number of dimensions. Some only work for list arrays or matrix arrays.

Public Function NumberOfArrayDimensions(Arr As Variant) As Integer
' This function returns the number of dimensions of an array.
'An unallocated dynamic array has 0 dimensions.
'This condition can also be tested with IsArrayEmpty.
'Adapted from Chip Pearson's modArraySupport

Dim Ndx As Integer
Dim Res As Integer
On Error Resume Next
' Loop, increasing the dimension index Ndx, until an error occurs.
' An error will occur when Ndx exceeds the number of dimension
' in the array. Return Ndx - 1.
Do
    Ndx = Ndx + 1
    Res = UBound(Arr, Ndx)
Loop Until Err.Number <> 0

NumberOfArrayDimensions = Ndx - 1

End Function

Number of elements in the array

Public Function NumElements(Arr As Variant, Optional Dimension = 1) As Long
    ' Returns the number of elements in the specified dimension (Dimension) of the array in
    ' Arr. If you omit Dimension, the first dimension is used. The function will return
    ' 0 under the following circumstances:
    '     Arr is not an array, or
    '     Arr is an unallocated array, or
    '     Dimension is greater than the number of dimension of Arr, or
    '     Dimension is less than 1.
    '
    ' This function does not support arrays of user-defined Type variables.
    'Adapted from Chip Pearson's modArraySupport
    
    Dim NumDimensions As Long
    
    'Preconditions - these functions raise an error when condition is not met
    TestVariantIsArray Variable:=Arr, VariableName:="Arr", Procedure:="NumElements", Module:=mcstrThisModule
    
    ' if the array is unallocated, return 0 and get out.
    If IsArrayEmpty(Arr) = True Then
        NumElements = 0
        Exit Function
    End If
    
    ' ensure that Dimension is at least 1.
    If Dimension < 1 Then
        NumElements = 0
        Exit Function
    End If
    
    ' get the number of dimensions
    NumDimensions = NumberOfArrayDimensions(Arr)
    If NumDimensions < Dimension Then
        NumElements = 0
        Exit Function
    End If
    
    ' returns the number of elements in the array
    NumElements = UBound(Arr, Dimension) - LBound(Arr, Dimension) + 1
    
End Function