Dynamic versus Static Data Structures
VBA provides a simple data structure, the array. If you know how many elements you're going to need to store, arrays can suit you fine. On the other hand, arrays present some difficulties:
They are linear only: You cannot overlay any kinds of relationships between the elements of an array without going through a lot of work.
They're essentially fixed-size: Yes, you can ReDim (Preserve) to resize the array, but all VBA does in that case is create a new data structure large enough for the new array and copy all the elements over, one by one. This isn't a reasonable thing to do often, or for large arrays.
They often use too much space: No matter how many elements you're going to put into the array, you must predeclare the size. It's just like the prepayment rip-off the car rental companies provide–you pay for a full tank, regardless of whether you actually use it. The same goes for arrays: if you dimension the array to hold 50 elements and you store only 5, you're wasting space for the other 45.
Because of these limitations, arrays are normally referred to as static data structures.
A dynamic data structure, on the other hand, is one that can grow or shrink as needed to contain the data you want stored. That is, you can allocate new storage when it's needed and discard that storage when you're done with it.
Dynamic data structures generally consist of at least some simple data storage (in our case, it will be a class module), along with a linkage to the next element in the structure. These links are often called pointers, or references. You'll see both terms used here.
The study of dynamic data structures could be a full semester college course on its own, so we can't delve too deeply in this limited space. We do, however, introduce the basic concepts and show how you can use class modules to create your own dynamic data structures. In addition, we suggest some ways in which you might use these data structures in your own applications.