Collections of Collections in VBA

In many situations using collections of one kind or another is very useful. Once in a while, you even want to go another step and use collections of collections. Just as there are several types of collections in VBA, there are several ways you can use collections of collections. All of them have benefits and drawbacks. I'll provide a summary of them here.

Nested collection types

The three types of nested collections I'll cover in this article are as follows:

Read more

VBA Internals: Array Variables and Pointers in Depth

This is the next installment of a series of deep-dives into the structure and implementation of variables in Visual Basic for Applications. For the previous posts, see the following:

In this post, I will cover the details of array variables and pointers. See Scalar Variables and Pointers in Depth for additional background and for the code for the utility functions HexPtr and Mem_ReadHex.

Pointers and memory for array variables

Like strings, arrays in VBA are treated semantically like value types but are implemented as reference types. Also like strings, arrays in VBA are implemented using a COM automation structure. For arrays the supporting COM type is the safe array, which comes with a large group of utility functions.

Read more

VBA Internals: Performance of Fixed vs. Dynamic Arrays

Arrays in VBA can have their dimensions either fixed at compile time or modifiable at runtime. Fixed-length arrays are declared by specifying the bounds in the declaration, while dynamic arrays must be initialized using the ReDim statement before they can be used. (See Types of Arrays for more details).

Given the flexibility of dynamic arrays I have sometimes wondered why anyone would use a fixed-length array. Sometimes it is required for byte buffers or in user defined types for passing to and from API methods. But for straight VB code, is there any advantage?

In particular, what is the performance cost of using a dynamic array? I set to measure exactly that, and I present my results here. I'll give it away from the start: There is essentially no statistical difference in how long it takes to initialize or copy fixed-length arrays vs. dynamic-length arrays. My recommendation, then: Only use fixed-length arrays when an API function or other external limitation requires it!


To compare the speed of initializing and copying fixed and dynamic arrays, I used the high-resolution performance timer described in Accurate Performance Timers in VBA. I executed each test 1,000 times and recorded each measurement. I then calculated the overall average and standard deviations of the measurements, and also graphed the histograms of the actual measurements. The code used to actually perform the tests is included at the end of this post.

Read more

Arrays in VBA – Part 2: Array Assignment Rules

Arrays are powerful tools in VBA. However, the many types of arrays and the fact that in VBA arrays are value types means that assigning array variables is tricky business. In this post I'll explain all the rules involved. In a future post I'll dive into the implications of value-type semantics for arrays. This post refers to reference and value types a lot. If you need a refresher on what that means, start with Values and References in VBA.

Read more

Arrays in VBA - Part 1: Types of Arrays

There are many kinds of arrays in Visual Basic for Applications. Specifically, there are three fundamental attributes of each array, determined at compile time by how the array is declared:

  • Element data type
  • Number of dimensions
  • Fixed or Dynamic Length

Each of these attributes will be discussed in detail in this article. Read more

Collections in VBA - Overview

In VBA programming collections of all types are indispensable. For both the new and experienced VBA programmer there is a lot to know about collection types in VBA, so I'm going to cover them one by one. In this post I'll provide a quick survey of the available collection types.

Types of Collections in VBA

There are three fundamental collection types in VBA

  • Array
  • Collection
  • Dictionary

Read more