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

Accurate Performance Timers in VBA

There are many times when you want to know how fast your code really is. Especially if you find your VBA application responding slowly you need to know where the bottleneck is occurring. You can build a Stopwatch type class around the built-in VBA Timer function, but this will only get you resolution up to a few milliseconds and also suffers from some quirks in the Timer function. I won't cover those in this article, but I will show how to make a super accurate Stopwatch class using the Windows API functions QueryPerformanceFrequency and QueryPerformanceCounter. For an in-depth look at these functions, see the MSDN Magazine article Implement a Continuously Updating, High-Resolution Time Provider for Windows.
Read more

Increasing Performance of Regular Expressions in VBA

In a previous post I presented some general-purpose wrappers for adding regular expressions functionality to VBA and SQL in Microsoft Access.

These wrappers work well, but we can make them much more efficient for situations where the same pattern is used many times, such as in a SQL query where the regular expression will be executed on each row of the input table or query. This article describes how to use a simple factory pattern to cache and reuse RegExp objects, dramatically increasing performance when the same pattern is used more than once during the lifetime of a program.

Read more