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

Accurate Performance Timers in VBA

March 8, 2013 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
…read more

Increasing Performance of Regular Expressions in VBA

February 12, 2013 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. Caching Compiled Regular Expressions What we’ll use is the fact that, behind the scenes, every regular expression pattern gets compiled into a tiny little program that executes in order to actually apply the regular expression.
…read more