VBA Internals: Performance of Fixed vs. Dynamic Arrays

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 cost of using a dynamic array? I set to measure exactly that, and I present my results here. I'll give it away from the start: There is essentially no statistical difference in how long it takes to initialize or copy fixed-length arrays vs. dynamic-length arrays. My recommendation, then: Only use fixed-length arrays when an API function or other external limitation requires it!


To compare the speed of initializing and copying fixed and dynamic arrays, I used the high-resolution performance timer described in Accurate Performance Timers in VBA. I executed each test 1,000 times and recorded each measurement. I then calculated the overall average and standard deviations of the measurements, and also graphed the histograms of the actual measurements. The code used to actually perform the tests is included at the end of this post.

Read more

Arrays in VBA – Part 2: Array Assignment Rules

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.

Read more

Arrays in VBA - Part 1: Types of Arrays

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. Read more