VBA vs C++ function – which one is faster in Excel?

Comparing the speed of VBA and C++ in general is quite not fair. Its like comparing the speed of a military jet and a Boeing 767. But still, it’s interesting to see how much C++ is faster in Excel’s native world – the Visual Basic Editor.

So, I have decided to build a simple Fibonacci with recursion function in both languages and simply to compare its speed with Excel. In general. Fibonacci with recursion is something that you should NEVER do at work or in a production code, because it is extremely slow, due to the huge number of useless calculations. See here for the memoization optimization.

So, as noted, I have used Fibonacci with Recursion, because it’s really easy to simulate a few billion useless calculations, thus the difference would be noted by everyone easily.

In C++ this is our function:

int __stdcall FibWithRecursion(int & x)
{
	int k = 0;
	int p = 0;

	if (x == 0)
		return 0;

	if (x == 1)
		return 1;

	k = x - 1;
	p = x - 2;
	return FibWithRecursion(k) + FibWithRecursion(p);
}

And the *.def file, needed to export the *.dll library:

LIBRARY "VitoshAcademy"
EXPORTS
FibWithRecursion

In VBA this is our function:

Public Function FibWithRecursionVBA(ByRef x As Long) As Long
    
    Dim k As Long: k = 0
    Dim p As Long: p = 0
    
    If (x = 0) Then FibWithRecursionVBA = 0: Exit Function
    If (x = 1) Then FibWithRecursionVBA = 1: Exit Function
    
    k = x - 1
    p = x - 2
    
    FibWithRecursionVBA = FibWithRecursionVBA(k) + FibWithRecursionVBA(p)
    
End Function

As you see, the functions are exactly the same and they make billions of calculation, due to the useless recursion and lack of memoization. In my tests, I have noted that up to using 10 a number of times for calculating the Fibonacci, both C++ and VBA work quite fast (no doubt about it, we do not reach a billion calculations there). However, if we increase the number of tests to 40, the result is really not in a favour of VBA. The speed of C++ is more than twice faster:

C++ is:
0:43
VBA is:
1:47

Yup, C++ really beats VBA in its own realm –  the VB Editor. And the bad news for VBA do not end here –  C++ is not just faster, it is exponentially faster, concerning the fact that at the beginning the results are quite the same.

That’s the VBA whole code I have used:

Option Explicit

Declare Function FibWithRecursion Lib _
    "C:\Users\~\Debug\CompareWithVBA.dll" _
    (ByRef x As Long) As Long

Public dblTimer As Double

Public Sub TestMe()

    Dim i                   As Long
    Dim dtStart             As Date
    Dim lngTotalTests       As Long
    Dim lngResultTime       As Long
    
    lngTotalTests = 40
    
    dblTimer = Timer
        
    For i = 0 To lngTotalTests
        FibWithRecursion (i)
    Next i
    
    Debug.Print "C++ is:"
    lngResultTime = Timer - dblTimer
    Debug.Print lngResultTime \ 60 & ":" & IIf(Len(CStr(lngResultTime Mod 60)) < 2, "0", "") & (lngResultTime) Mod 60
    dblTimer = Timer
    
    For i = 0 To lngTotalTests
        FibWithRecursionVBA (i)
    Next i
    
    Debug.Print "VBA is:"
    lngResultTime = Timer - dblTimer
    Debug.Print lngResultTime \ 60 & ":" & IIf(Len(CStr(lngResultTime Mod 60)) < 2, "0", "") & (lngResultTime) Mod 60
    
End Sub


Public Function FibWithRecursionVBA(ByRef x As Long) As Long
    
    Dim k As Long: k = 0
    Dim p As Long: p = 0
    
    If (x = 0) Then FibWithRecursionVBA = 0: Exit Function
    If (x = 1) Then FibWithRecursionVBA = 1: Exit Function
    
    k = x - 1
    p = x - 2
    
    FibWithRecursionVBA = FibWithRecursionVBA(k) + FibWithRecursionVBA(p)
    
End Function

Still, VBA is fast enough! 🙂