Collections of Collections in VBA

December 2, 2013 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: Multidimensional arrays Jagged arrays Jagged Collections Multidimensional arrays Good Simple syntax (only one variable) Type safety. All elements of a
…read more

VBA Internals: Array Variables and Pointers in Depth

June 5, 2013 in VBA

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: What’s in a variable – Categories of variables in VBA, by implementation Getting Pointers – Overview of how to obtain pointers in VBA Scalar Variables and Pointers in Depth – Details of numeric scalar variables and pointers String Variables and Pointers in Depth – Details of string variables and pointers In this post, I will cover the details of array variables and pointers. See Scalar Variables and Pointers in Depth for
…read more

VBA Internals: Performance of Fixed vs. Dynamic Arrays

May 8, 2013 in VBA

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
…read more

Arrays in VBA – Part 2: Array Assignment Rules

April 29, 2013 in VBA

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. Assignment rules You cannot assign TO fixed-length arrays, EVER I’ll start with the simplest rule: The VBA compiler does not allow
…read more

Arrays in VBA – Part 1: Types of Arrays

March 29, 2013 in VBA

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. Element data type Just as scalar (non-array) variables have a data type, arrays have a data type that restricts what can go in the elements of the array. As we will see in a later post all about array assignments, the declared data type of an array is
…read more

Collections in VBA – Overview

March 27, 2013 in VBA

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 Arrays are a language feature and use value-type semantics (they do not use the Set keyword for assignments, for example). Collections and Dictionaries are object types provided by the Visual Basic for Applications library
…read more