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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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:
1 2 3 |
LIBRARY "VitoshAcademy" EXPORTS FibWithRecursion |
In VBA this is our function:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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:
1 2 3 4 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
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! 🙂