Pré-visualização | Página 1 de 1
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.