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