VBA speed up. Boost VBA performance with VbaCompiler.

 

After you have applied all of our most important optimization tips for VBA code and now that your code performs faster than it did before optimization, then you may continue to speed up your VBA code further. To improve the performance speed of your VBA code, all you have to do is just by compile it with VbaCompiler for Excel.
After compilation you may multiply performance of your code, by several orders of magnitude in some cases!
Why would the compilation to DLL improve the VBA code execution performance?
Here are the main 3 reasons for that:

1. During compilation, the VbaCompiler converts your code from VBA code to the one of the best run-time performance efficient programming language – the C-language.

2. MinGW GCC C-compiler, which is used as the back-end compiler, performs speed optimizations during binary code generation – the process in which the DLL file is created.

3. We are working to make the run-time library for VBA elements as fast as possible.

Let’s consider different VBA elements and how the compilation to DLL affect their performance.
All of the following examples were compiled with the optimization option level set to “Ofast”.
You may download the “vba speed up.xlsm” workbook, which contains the examples that are used here, from our website.

 

 

Impressive VBA speed up in compiled For Loop.

 

The For loop shows a tremendous speed up after compilation, where the index variable is declared as Long.
For example, the following piece of code shows more than a x3000000 (3 million) speed up rate after compilation, when optimization option level in VbaCompiler compilation options is selected as “Ofast”.

Dim iterCount as Long
Dim i as Long
For i = 0 to 10000000
       iterCount = iterCount + 1
Next

In the case of For Loop the C-compiler optimizer does the best job to improve performance of the compiled code.

 

 

Speed Up VBA with Strongly Typed Method Declarations

 

The way in which VBA methods are declared has a significant impact on the performance of the compiled VBA code.
Let’s consider different ways of method declarations.

Function Func1(a, b, c, d)
    Func1 = a + b + c + d
End Function

In this version all of the parameters are declared as a Variant, which are passed by reference. This function returns a Variant value.

 

 

The next version of the same function:

 

Function Func2(a As Long, b As Integer, c As Double, d As Single) As Double
    Func2 = a + b + c + d
End Function

In this version of method declaration, the same parameters are declared with specific numeric types passed by reference and the function returns Double.

 

 

The final form of the same function declaration:

Function Func3(ByVal a As Long, ByVal b As Integer, ByVal c As Double, ByVal d As Single) As Double
    Func3 = a + b + c + d
End Function

In this version of method declaration, the same parameters are declared with specific numeric types, but, in this case, they are also passed by value. This function, much like the second function covered above, still returns a Double.

 

As you can see, the declaration with a Variant type has the longest execution time, both in the original VBA code and in the compiled DLL version. Following the ‘Avoid Variant type’ and the ‘Avoid declaration of method parameters without type’ VBA optimization tips provide the best performance boost to the compiled VBA code.

 

Object Methods

 

Our approach to speeding up VBA code for object methods after compilation with c-compiler optimization gives a small performance improvement or does not give a performance boost at all. This is related to the c-language optimizer, which cannot optimize complex objects. Also, all of the low-level manipulations of object methods in ActiveX (a technology which is used in VBA objects) are based on the Variant type. The Variant type, from the low-level point of view, is a structure, which is always more complex than a basic type. The performance of structure data manipulations cannot be significantly improved with a c-language compiler optimizer.

Let’s consider the effect of compilation performance boost on the calculation time of VBA methods which populate a big Excel cell range with random numbers (in this case, the “PopulateCells” method) and calculate sum of random data in this range (using the “CalcSum” method).

 

 

 

Conclusion

In order to get the maximum out of your VBA code performance after compiling with DoneEx VbaCompiler for Excel, you need to prepare your VBA code before the compilation. This means applying all of the Top 25 VBA performance improvement tips and compiling your VBA code with the VbaCompiler’s “Optimization Level” option set to “O3” or “Ofast”.
As you can see from the tests above, the performance of the compiled VBA code depends on avoiding Variant types and carefully providing the exact data type declaration for your variables, method parameters, and method return types.