THE-VBA-DATA-STRUCTURES

Prévia do material em texto

1 
 
THE	VBA	DATA	STRUCTURES	
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. 
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 
 
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. 
Dim dict as Object 'Declare the Dictionary object 
Set dict = CreateObject("Scripting.Dictionary") 'Create the Dictionary 
 
Dim key, val 
key = "SomeKey": val = "SomeValue" 
'Add item to VBA Dictionary 
If Not dict.Exists(key) Then 
 dict.Add key, val 
End If 
 
Debug.Print dict.Count 'Result: 1 
 
'Dispose of VBA Dictionary 
Set dict = Nothing 
Traversing items and keys 
2 
 
Below methods for traversing either all keys or items in a VBA Dictionary 
 
Dim dict as Object, key, val 
Set dict = CreateObject("Scripting.Dictionary") 
key = "Key1": val = "Val1" 
dict.Add key, val 
key = "Key2": val = "Val2" 
dict.Add key, val 
 
'Print all keys 
For Each key In dict.Keys 
 Debug.Print key 
Next key 
 
'Print all items 
For Each item In dict.Items 
 Debug.Print item 
Next item 
 
'Dispose of VBA Dictionary 
Set dict = Nothing 
Removing items 
Dim dict as Object, key, val 
Set dict = CreateObject("Scripting.Dictionary") 
key = "Key1": val = "Val1" 
dict.Add key, val 
key = "Key2": val = "Val2" 
dict.Add key, val 
 
'Remove Key2 from VBA Dictionary 
dict.Remove "Key2" 
 
'Remove all remaining items / empty the dictionary 
dict.RemoveAll 
 
'Dispose of VBA Dictionary 
Set dict = Nothing 
 
 
3 
 
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 
 
Dim arrList as Object 
Set arrList = CreateObject("System.Collections.ArrayList") 'Create the ArrayList 
arrList.Add "Hello" 
arrList.Add "You" 
arrList.Add "There" 
arrList.Add "Man" 
arrList.Remove "Man" 
 
'Get number of items 
Debug.Print arrList.Count 'Result: 3 
 
For Each item In arrList 
 Debug.Print item 
Next Item 
On a regular basis use rather the VBA Collection instead of the VBA ArrayList 
The 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 
 
Dim queue as Object 
Set queue = CreateObject("System.Collections.Queue") 'Create the Queue 
 
queue.Enqueue "Hello" 
queue.Enqueue "There" 
queue.Enqueue "Mr" 
queue.Enqueue "Smith" 
 
peekAtFirst = queue.Peek() 'Result" "Hello" 
 
doesContain = queue.Contains("htrh") 'Result: False 
4 
 
doesContain = queue.Contains("Hello") 'Result: True 
 
'Get first item in Queue and remove it from the Queue 
firstInQueue = queue.Dequeue() '"Hello" 
 
'Count items 
Debug.Print queue.Count 'Result: 3 
 
'Clear the Queue 
queue.Clear 
 
Set queue = Nothing 
The 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. 
 
Dim stack as Object 
Set stack = CreateObject("System.Collections.Stack") 'Create Stack 
 
stack.Push "Hello" 
stack.Push "There" 
stack.Push "Mr" 
stack.Push "Smith" 
 
peekAtTopOfStack = stack.Peek() 
doesContain = stack.Contains("htrh") 'Result: False 
doesContain = stack.Contains("Hello") 'Result: True 
 
'Get item from the top of the stack (LIFO) 
topStack = stack.Pop() 'Result: "Smith" 
 
'Clear the Stack 
stack.Clear 
 
Set stack = Nothing 
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. 
5 
 
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.