Tag Archives: data structure

VBA Collection

VBA Collection Tutorial

1 Star2 Stars3 Stars4 Stars5 Stars (8 votes, average: 3.88 out of 5)

The VBA Collection is a simple native data structure available in VBA to store (collect as you wish) objects. VBA Collections are more flexible than VBA Arrays as they are not limited in their size at any point in time and don’t require manual re-sizing. Collections are also useful when you don’t want to leverage there more complex (but quite similar) Data Structures like the VBA ArrayList or even a VBA Dictionary.

Adding items to a VBA Collection

Let’s start by creating a New VBA Collection and adding some items to it.

Notice that the VBA Collection is not typed in any way as we didn’t have to declare what types of objects it stores. This is because a VBA Collection stores object of type Variant.

By default the Add procedure will push items to the end of the VBA Collection. However, you can also choose to insert items before or after any index in the Collection like so:


If you want to be able to reference a particular item in your VBA Collection by a certain string/name you can also define a key for object added to your VBA Collection like so:

Removing items from a VBA Collection

Removing items from a VBA Collection is equally easy. However, items are removed by specifying their index.

Be careful when removing items from a VBA Collection – items start indexing at 1 (not 0 as in most common programming languages.

When removing items in a loop do remember that the index of the remaining items in the VBA Collection will decrease by 1.

Clearing a VBA Collection

To Clear all items (remove them) from a VBA Collection use the Clear function.

Clearing all items from a Collection is similar to recreating a VBA Collection:

Counting items in a VBA Collection

Similarly as with other VBA Data Structures the VBA Collection facilitates the Count function which returns the amount of items currently stored.

Getting items in a VBA Collection

To get a specific item in your VBA Collection you need to either specify it’s index or optionally it’s key (if it was defined).

Traversing a VBA Collection

As with VBA Arrays you can similarly traverse a VBA Collection using loops such as For, While etc. by indexing it’s items, or by traversing its items using the For Each loop (preferred).

Check if VBA Collection contains item

Unfortunately, the VBA Collection object does not facilitate a Contains function. We can however easily write a simple Function that will extend this functionality. Feel free to use the Function below:

Usage example:

Convert VBA Collection to VBA Array

In some case you may want to convert your VBA Collection to a VBA Array e.g. if you would want to paste items from your Collection to a VBA Range. The Code below does that exactly that:

Below a test of how it works:


The VBA Collection is an extremely useful data type in VBA. It is more versatile than the VBA Array allowing you to add and remove items more freely. The Key-Value store works also similarly as for the VBA Dictionary making it a useful alternative.

As an exercise – next time you will consider using a VBA Array consider replacing it with the Collection object. Hopefully it will guest in your VBA Code more often.

vba array

The VBA Array tutorial

1 Star2 Stars3 Stars4 Stars5 Stars (6 votes, average: 3.83 out of 5)

The VBA Array is a very convenient and efficient for storing multiple items of usually the same data type. The size of a VBA Array can be either fixed or dynamic depending on how it is declared. VBA Arrays can also be 1 or multi-dimensional.

one dimensional VBA array
multi dimensional vba arrays
vba fixed array
vba dynamic array
sizing and moving VBA array
vba array functions
vba array limits
vba other data structures

One-dimensional VBA Array

A one-dimensional VBA Array contains a sequence of elements within a single dimension. The default numbering sequence of these elements starts at 0. You can redefine the starting and ending index of the sequence of elements using the X to Y statement. See an example below:

One-dimensional VBA Array
One-dimensional VBA Array

Let’s start by introducing a simple one-dimensional VBA Array of 10 items:

Dim onedimArray(1 to 10) As Long '10 items of type Long
'Set the 2nd item value to 20
onedimArray(2) = 10

Notice that I have sized the array for indices 1 to 10. This is not a required statements as I can also declare the VBA Array size without the index range.

Dim onedimArray(9) As Long '10 items of type Long (0 to 9)

Another example of 10 item array indexed from 2 to 11.

Dim onedimArray(2 to 11) As Long '10 items of type Long
onedimArray(1) = 10 'ERROR! Index starts at 2!

Multi-dimensional VBA Array

A multi-dimensional VBA Array contains a sequence of elements within multiple dimensions (as many as defined). The default numbering sequence of these elements, within each dimension, starts at 0. You can redefine the starting and ending index of the sequence of elements using the X to Y statement. See an example below:

Two-dimensional VBA Array
Two-dimensional VBA Array

In some cases you might not want to limit yourself to just 1 dimension. VBA Arrays can have multiple dimensions. See a simple example below:

Dim twodimArray(5, 15) As Long
twodimArray(1,15) = 10
twodimArray(2,10) = 10

Dim threedimArray(5, 10, 15) As Long
threedimArray(2, 10, 12) = 3
threedimArray(5, 10, 15) = 9

In case of dynamic arrays (read on) multidimentional arrays in VBA have proven problems with using the Preserve statement to preserve the arrays contents when resizing it. For more information on how to workaround this problem see this post here.

Fixed VBA array

The VBA Arrays above were all of fixed size i.e. their size was defined when declaring the VBA Array using the Dim statement. Let’s quickly compare fixed and dynamic sized arrays.

Dim fixedArray(5) As String 'this is a fixed size array indexed 0 to 5
Redim fixedArray(10) 'ERROR! FORBIDDEN!

Dim dynamicArray() As String
Redim dynamicArray(5) 'OK. Declaring the size of a dynamic array

Fixed size arrays should be used when you know before execution how many items your VBA Array should contain. Fixed arrays are equivalent in usage to dynamic arrays although simply have the limit of being un-resizable.

Dynamic VBA array

Dynamic sized VBA Arrays are arrays that can be sized and re-sized even multiple times. This is useful when you are either not sure before execution what the required size of your VBA Array should be or you want to optimize memory by allocating a large sized array for only a short period of time. Let’s start with a simple example:

Dim dynamicArray() As Long 'Declare an array
'Set the size of the array to 10 items (0 to 9)
ReDim dynamicArray(9)
dynamicArray(1) = 20

If you want to set your own index range feel free to use the ReDim statement as such:

Dim dynamicArray() As Long 'Declare an array
'Set the size of the array to 10 items
ReDim dynamicArray(2 to 11)
dynamicArray(1) = 20

ReDim Statement – Resizing a Dynamic VBA Array

The ReDim statement is used to set the size of your VBA Array or to resize it if needed. See an example below.

Dim dynamicArray() As Long 'Declare an array
'Set the size of the array to 10 items
ReDim dynamicArray(9)
dynamicArray(2) = 5
'Resize the size of the array from 10 to 15 (erasing all items)
ReDim dynamicArray(14)
Debug.Print dynamicArray(2) 'Result: 0 - item not preserved

The problem with a regular ReDim is that your previous items will get erased. When you want to resize an VBA Array of existing items you need to use ReDim with Preserve. See an example below:

Dim dynamicArray() As Long 'Declare an array
'Set the size of the array to 10 items
ReDim dynamicArray(9)
dynamicArray(2) = 5
'Resize the array and preserve items
ReDim Preserve dynamicArray(14)
Debug.Print dynamicArray(2) 'Result: 5 - item preserved

Erasing Dynamic VBA Arrays

Dynamic VBA Arrays can contain large amounts of data that can use a lot memory space. You can erase (free memory from) a Dynamic VBA Array by using the Erase statement. It is important therefore to wipe out the Array in a proper manner. For this use the Erase procedure like so:

Dim arr() As Long
ReDim arr(100,100)
arr(0,0)= 0 * 0
arr(100,100) = 100 * 100
Erase arr 'Erase the array and Free memory!

Sizing and moving through VBA Arrays

Sizing VBA Arrays

VBA Arrays can be sized by either specifying the size of the array (dimension) or by specifying the index range of the VBA Array. I can definitely recommend the first approach as being more practical and less error prone.

VBA Array size

To get the size of your VBA Array use the following function:

UBound(array) - LBound(array) + 1

Getting the size of VBA Arrays has always caused some confusion. First let’s understand the UBound and LBound functions:

LBound vs. UBound

Dim dynamicArray() As Long
'Set the size of the array
ReDim dynamicArray(1 To 10)
Debug.Print UBound(dynamicArray) 'Result: 10
Debug.Print LBound(dynamicArray) 'Result: 1

'Resize the array
ReDim Preserve dynamicArray(2 To 20)
Debug.Print UBound(dynamicArray) 'Result: 20
Debug.Print LBound(dynamicArray) 'Result: 2

From the example above we see that the UBound function alone might not always provide correct results.
Let’s summarize this with an example

Dim dynamicArray() As Long
'Set the size of the array
ReDim dynamicArray(1 To 10)

'Print size of the Array
Debug.Print UBound(dynamicArray)-LBound(dynamicArray) +1 'Result: 10. CORRECT!

Traversing a VBA Array

To traverse (iterate) through a VBA Array you can either use the For or For Each loops.

For Each example

'Traverse and print array elements
Dim arr(10) As Long
For Each arrItem In arr
  Debug.Print arrItem
Next arrItem

For example

'Traverse and print array elements
Dim arr(10) As Long, i as Long
For i = LBound(arr)  to UBound(arr)
  Debug.Print arr(i)
Next i

VBA Array Functions

Below a list of various VBA Functions useful when dealing with VBA Arrays (click to expand):

Basic functions

  • LBound(Array, Rank) – return the lowest subscript (index range) of the VBA Array within a certain dimension (Rank). E.g.
    Dim arr(5,2 to 3) as Long
    Debug.Print LBound(arr) 'Result:0
    Debug.Print LBound(arr,2) 'Result:2
  • UBound(Array, Rank) – return the highest subscript (index range) of the VBA Array within a certain dimension (Rank). E.g.
    Dim arr(5,2 to 3) as Long
    Debug.Print UBound(arr) 'Result:5
    Debug.Print UBound(arr,2) 'Result:3
  • ReDim – reallocate storage space for the array (set new array size). Applies to dynamic sized arrays only
  • Erase – release array variable and deallocate the memory used. Applies to dynamic sized arrays only

Batch define VBA Array

VBA does not allow to define array elements in a batch like so:

Dim numbers = New Integer() {1, 2, 4, 8}

The only supported manner of defining arrays in VBA is one by one. Awful ain’t it? Wouldn’t it be easier to just provide an enumeration of values instead? Well you can do that. With my DefineArray procedure below. It works only for 1 dimensional arrays but with little effort you can modify it to work for any number of dimensions:

Sub DefineArray(ByRef arr, values As String, delimitator As String)
    Dim tmpArr() As String, index As Long, i As Long
    tmpArr = Split(values, delimitator)
    For index = LBound(tmpArr) To UBound(tmpArr)
        i = LBound(arr) - LBound(tmpArr) + index
        If VarType(arr(i)) = 2 Then
            arr(i) = CInt(tmpArr(index))
        ElseIf VarType(arr(i)) = 3 Then
            arr(i) = CLng(tmpArr(index))
        ElseIf VarType(arr(i)) = 4 Then
            arr(i) = CSng(tmpArr(index))
        ElseIf VarType(arr(i)) = 5 Then
            arr(i) = CDbl(tmpArr(index))
        ElseIf VarType(arr(i)) = 5 Then
            arr(i) = CCur(tmpArr(index))
        ElseIf VarType(arr(i)) = 6 Then
            arr(i) = CCur(tmpArr(index))
        ElseIf VarType(arr(i)) = 7 Then
            arr(i) = CDate(tmpArr(index))
        ElseIf VarType(arr(i)) = 8 Or VarType(arr(i)) = 12 Then
            arr(i) = tmpArr(index)
        ElseIf VarType(arr(i)) = 11 Then
            arr(i) = CBool(tmpArr(index))
        ElseIf VarType(arr(i)) = 14 Then
            arr(i) = CDec(tmpArr(index))
        ElseIf VarType(arr(i)) = 17 Then
            arr(i) = CByte(tmpArr(index))
            Call Err.Raise(vbObjectError + 1, "DefineArray", "Not supported var type")
        End If
    Next index
End Sub

How to use this procedure? Easy. See the examples below:

'Array of Integers separated by ;
Dim intArr(4) As Integer
Call DefineArray(intArr, "1;2;3;4;5", ";")

'Words separated by a space. Use Split function instead to create array
Dim strArr(1) As String
Call DefineArray(strArr, "Hello World!", " ")
'Dates separated by ;
Dim dateArr(2) As Date
Call DefineArray(dateArr, "2015-05-12;2015-05-13;2015-05-14", ";")

Other custom VBA Array functions

Merging 2 VBA Arrays

Function Merge(ByVal arr1 As Variant, ByVal arr2 As Variant) As Variant
    	Dim tmpArr As Variant, upper1 As Long, upper2 As Long
        Dim higherUpper As Long, i As Long, newIndex As Long
        upper1 = UBound(arr1) + 1 : upper2 = UBound(arr2) + 1
        higherUpper = IIf(upper1 >= upper2, upper1, upper2)
        ReDim tmpArr(upper1 + upper2 - 1)

        For i = 0 To higherUpper
            If i < upper1 Then
                tmpArr(newIndex) = arr1(i)
                newIndex = newIndex + 1
            End If

            If i < upper2 Then
                tmpArr(newIndex) = arr2(i)
                newIndex = newIndex + 1
            End If
        Next i
        Merge = tmpArr
End Function


Comparing two arrays (1 Dimensional)

Function Compare1DArrays(ByVal arr1 As Variant, ByVal arr2 As Variant) As Boolean
    Dim i As Long
    For i = LBound(arr1) To UBound(arr1)
       If arr1(i) <> arr2(i) Then
          Compare1DArrays = False
          Exit Function
       End If
    Next i
    Compare1DArrays = True
End Function


Sorting an array (1 Dimensional) – Quick Sort

Original source here.

Public Sub QuickSort(vArray As Variant, lowerBound As Long, upperBound As Long)

  Dim pivot   As Variant
  Dim tmpSwap As Variant
  Dim tmpLow  As Long
  Dim tmpHi   As Long

  tmpLow = lowerBound
  tmpHi = upperBound

  pivot = vArray((lowerBound + upperBound) \ 2)

  While (tmpLow <= tmpHi)

     While (vArray(tmpLow) < pivot And tmpLow < upperBound)
        tmpLow = tmpLow + 1

     While (pivot < vArray(tmpHi) And tmpHi > lowerBound)
        tmpHi = tmpHi - 1

     If (tmpLow <= tmpHi) Then
        tmpSwap = vArray(tmpLow)
        vArray(tmpLow) = vArray(tmpHi)
        vArray(tmpHi) = tmpSwap
        tmpLow = tmpLow + 1
        tmpHi = tmpHi - 1
     End If


  If (lowerBound < tmpHi) Then QuickSort vArray, lowerBound, tmpHi
  If (tmpLow < upperBound) Then QuickSort vArray, tmpLow, upperBound

End Sub


VBA Array Limits and Errors

VBA Array Limits

What are the limits of VBA Arrays? For one thing the maximum length of every dimension is defined by an Integer meaning that each dimension can have a maximum size of (2^31)-1 (equal to 2’147’483’647). Although, I would assume you could sooner reach the limit of the actual memory used by the array (about 500MB for 32-bit VBA and about 4GB for 64-bit VBA).

Typical VBA Array errors

Typical errors listed below:

  • Runtime Error 9: Subscript out of range – you are trying to access/set an item of the array outside its range e.g. if you array is of size 10 setting the item of index 11 will give you an out of range error

Other data structures

The VBA Array has its limits and is certainly not as versatile as you may need. In some cases it is better to replace a VBA Array with a VBA Dictionary, ArrayList, Stack, Queue or other data structure objects. Read more here:
The VBA Dictionary and other data structures


Below a list of Frequent Asked Questions on VBA Array usage:

I want to to store items in my VBA Array by Key name instead of index number. Is this possible?
No this is not possible using a VBA Array. Use a Dictionary Object.

Should I use the UBound function to get the size of an VBA Array?
No! This in case of some arrays will provide a correct result HOWEVER in some cases will provide incorrect. See this section for more.

My VBA Array will keep expanding. But I don’t want to keep using ReDim to correct the Array size. What to do?
Use the ArrayList object. It allows you to add items without needing to resize it either by upsizing or downsizing your VBA Array.

Can I pass an array as an argument to a Function or Sub?
Sure. See the example below where I pass a VBA Array by it’s reference to modify it within a separate procedure.

Sub Test()
    Dim arr() As Long
    ReDim arr(0) As Long
    arr(0) = 1
    SetArr arr
    Debug.Print arr(0) 'Result: 10
End Sub
Sub SetArr(ByRef arr)
    arr(0) = 10
End Sub
VBA Dictionary

The VBA Dictionary, Arrays, and other data structures

1 Star2 Stars3 Stars4 Stars5 Stars (11 votes, average: 4.73 out of 5)

Visual Basic for Applications (VBA) facilitates many useful data structures – its not just VBA Arrays you can use. COM libraries provide additional data structures such as the VBA Dictionary, ArrayList, Queue, Stack or SortedList.
But before we jump into explaining how these more complex data structures work and how to use them, let’s start with the basics – VBA array. Often you needn’t reach out for more complex structures, so it’s important to understand first the limits of VBA Arrays.

Data Structures

VBA Array

vba array
Arrays are basic data structures available in most programming languages. Their size can be either fixed or dynamic depending on how they are declared. Arrays are very convenient and efficient for storing multiple items of the same data type.

The VBA Dictionary object

vba dictionary
The VBA Dictionary is probably the second most often used data structure in VBA. Dictionaries are great at storing key-value pairs of data similarly as the VBA Collection object does.

The VBA Dictionary, however, offers some functionality that is not available with the VBA Collection object e.g. the option to specify a comparison method for Keys, which I will demonstrate below. This allows for a case-sensitive Key.

Code example

Ok, so let’s start with a simple example of how the VBA Dictionary can be used.

Traversing items and keys

Below methods for traversing either all keys or items in a VBA Dictionary

Removing items

The VBA ArrayList object

The VBA ArrayList is also a very useful data structure if you want to work with dynamic arrays but don’t want the hassle of having to constantly redefine (Redim) the size of the array. ArrayLists don’t have a fixed size so you can keep adding items to it. However, in VBA in can be better superseded by the Native VBA Collection.

Code example

On a regular basis use rather the VBA Collection instead of the VBA ArrayList

The VBA Queue

vba queue
The VBA Queue represents a first-in, first-out collection of objects (FIFO). You push items to the Queue, and then read them out at a later time in the same order. Queues are useful if you want to store items in the exact order you should process them, especially if you constantly are adding new items to the Queue.

Code sample

The VBA Stack

vba stack
Represents a simple last-in-first-out (LIFO) non-generic collection of objects. The VBA Stack is useful in situations when you want to first process items which were added last.

Other useful data structures

  • Hashtable — similar to the Dictionary class. Represents a collection of key/value pairs that are organized based on the hash code of the key. If you want to learn when it’s efficient to use Hashtables read here. More here.
  • SortedList — Similar to the Hashtable. Represents a collection of key/value pairs that are sorted by the keys and are accessible by key and by index. More here.

Creating your own structures (VBA Class)

If none of the above data structures are suitable for your needs you can always create your own VBA Class. A Class is a VBA Module that can be instantiated as an object (similarly as the objects above), have its own properties etc. Classes are a good way of encapsulating abstraction and complexity.

Alternatively, consider also a stack of any of the mentioned data structures. For example let’s say we want to manage a structure which would store words in different languages. Later we will want to quickly check if there is a word in a certain language in our multi-dictionary. How to do this? A Dictionary of Dictionaries should do the trick. The first Dictionary will have keywords mapping to different languages. The keyword will be e.g. “English” while the value will be an object of type Dictionary in which you can store the word in the “English” language. Cool right?