Excel Speed, IF (Formula vs VBA)
I have conducted several tests to see the fastest way to implement an If function in Excel. In these tests I have compared the native excel IF() function, with several methods of using the “if” statement in VBA.
Each test basically executes the following algorithm 2E8 times:
- Get an input value
- check if its larger than 0.5
- If true assign the value 1 to an output variable
- If false assign the value 0 to an output variable
You can see the different input and output variables in the table below:
|Test Name||Type||Tested Variable||Output Variable|
|Test 1||Excel Functions||Cell||Cell|
|Test 2||VBA||Variant Array||Variant Array|
|Test 3||VBA||Double Array||Integer Array|
|Test 4||VBA||Double Array||Integer Variable|
|Test 5||VBA||Double Variable||Integer Variable|
|Test 6||VBA||Integer Variable||Integer Variable|
|Test 6||VBA||Collection||Integer Variable|
Before showing the results, I will explain how each test was conducted.
Test 1, Excel IF() Function:
In result the IF() function was calculated 2E8 times.
Test 2, VBA Using 2 Variant Arrays:
In the next test, 2 variant arrays with the size 2000×1 were used. The first array was populated with random numbers. Then each of these values were compared with the number 0.5 (similar to Test 1 only using VBA this time). If the value was greater than 0.5 the value 1 was stored in the second array. If the value was smaller than 0.5 the value 0 was stored in the second array:
Test 3, VBA Using a Double and an Integer Array:
Same as test2, only this time the first array was a double array and the second array was an integer array:
Test 4, Double Array, With Integer Variable as Output:
In this test a double array with the size 20000×1 was populated with random values. The same “If” statement as the previous tests was used to check if the value in the array is greater or smaller than 0.5. Only this time, if the value is larger than 0.5, the value 1 is assigned to an integer variable rather than an integer array. If the value is smaller than 0.5 the value 0 is assigned to the integer:
Test 5: A Double and Integer Variable:
In this test the value 0.2 is assigned to a double variable. The same “If” statement as the previous sections is tested on this variable. Then the value 0.7 is assigned to the variable, and the If statement is again tested for this variable. Note each test was conducted half as many times to result in the same amount of calculations:
Test 6, Two Integer Variables:
Same as test 5 only done with 2 integer variables. Instead of assigning the value 0.2 and 0.7 to the first variable the values 0 and 1 were assigned:
Test 7, Collection:
Same as Test2 only 3 only a collection was used instead of the input array:
Test 7 was unable to finish. Collections are not a good method for working with large data.
Double and integer arrays are faster than variant arrays.
The VBA “if” statement is faster than the Excel If() function when working with variables.
VBA arrays are slow. Therefore if you are working with a large amount of data, using the Excel IF() function can increase performance over VBA arrays up to 200%
- Excel VLOOKUP vs INDEX and MATCH Speed Comparison
- Excel VBA, Sheet Reference Speed
- Excel Functions and Formulas, IF()