Tag Archives: list

VBA Dictionary

The VBA Dictionary, Arrays, and other data structures

1 Star2 Stars3 Stars4 Stars5 Stars (10 votes, average: 5.00 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?

excel dynamic named range

Creating a dynamic named range in Excel

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)

When making complex Excel solutions is often the case that you will need to manage many different lists of certain values e.g. for dropdowns, formulas etc. It is a real pain to manage lists that change very often. The clean solution is to define a dynamic named range which will adapt to the list of parameters within a certain column.

Defining a dynamic named range

Create a list of items

Some Excel Range list
Some Excel Range list
Create your list of items. It is often best to keep your lists on a separate Worksheet and each column topped with a header explaining what each list contains. Watch out for BLANK as they will not be supported by the dynamic named range.

Open the Excel Name Manager

Go to the FORMULAS ribbon and open the Name Manager within the Defined Names group.

Create a New Named Range

Hit the New.. button to create a new Named Range. Next provide the name for your Excel dynamic named range.

Create a new Excel Named Range
Create a new Excel Named Range

Remember that Excel range names cannot contain spaces and need to start with a letter character

Provide the dynamic named range formula

Provide the formula for your dynamic named range. Assuming your worksheet name is NameOfWorksheet the formula should look like this:


See an example below:

Dynamic Named Range
Dynamic Named Range

How does the dynamic named range formula work?

How does it work? Well the OFFSET function takes 5 arguments:

  1. the reference cell
  2. the offset number of rows to move
  3. the offset number of columns to move
  4. how many rows of data to return (optional)
  5. how many columns of data to return (optional)

See now that what our dynamic named range formula does is:

  • move 1 cell down from the “List of names” cell to the first cell of the list
  • return a range of unempty rows – for as many rows as there are un-empty cells in the entire column minus 1 (minus the first cell of the column)

Therefore to sum up, the formula returns a range of all unempty cells within the given column offset by 1 row (fro the header). The definition of the Named Range is a formula hence will recalculate automatically.

The Named Range formula recalculates based on the calculation settings. If you turn of Automatic Calculation be aware that the Named Range will need to be recalculated manual or else it might show an outdated range if you add/remove rows

Making an Excel dropdown with a dynamic populated list

Let’s now make a common use the list of names which we defined to create an Excel dropdown. This way the dropdown will only be populated with the items defined in the dynamic named range list.

Dynamic Excel Dropdown
Dynamic Excel Dropdown

Good practice to using dynamic named ranges

My personal experience is that lists should be in hidden worksheets (sometimes good to make it even “very” hidden) in which each column will represent a certain list of values which can be reference by a certain dynamic named range. This will make it easy for you to manage your lists and not worry about the number of their items increasing or decreasing.