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.
Contents
Test 1, Excel IF() Function:
Private Declare Function GetTickCount Lib "kernel32" () As Long
Sub Test1()
Dim intTime1 As Long
Dim intTime2 As Long
Dim i As Integer
Sheet1.EnableCalculation = False
intTime1 = GetTickCount
''''''''''''
'test start
For i = 1 To 1000
Sheet1.Calculate
Next i
'test end
''''''''''''
intTime2 = GetTickCount
MsgBox (intTime2 - intTime1)
End Sub
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:
Sub test2()
Dim intTime1 As Long
Dim intTime2 As Long
Dim arrInput(1 To 20000, 1 To 1) As Variant
Dim arrOutput(1 To 20000, 1 To 1) As Variant
Dim i As Long
Dim j As Long
For i = 1 To 20000
arrInput(i, 1) = Rnd
Next i
intTime1 = GetTickCount
''''''''''''
'test start
For j = 1 To 1000 * 10
For i = 1 To 20000
If arrInput(i, 1) > 0.5 Then
arrOutput(i, 1) = 1
Else
arrOutput(i, 1) = 0
End If
Next i
Next j
'test end
''''''''''''
intTime2 = GetTickCount
MsgBox (intTime2 - intTime1)
End Sub
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:
Sub test3()
Dim intTime1 As Long
Dim intTime2 As Long
Dim arrInput(1 To 20000, 1 To 1) As Double
Dim arrOutput(1 To 20000, 1 To 1) As Integer
Dim i As Long
Dim j As Long
For i = 1 To 20000
arrInput(i, 1) = Rnd
Next i
intTime1 = GetTickCount
''''''''''''
'test start
For j = 1 To 1000 * 10
For i = 1 To 20000
If arrInput(i, 1) > 0.5 Then
arrOutput(i, 1) = 1
Else
arrOutput(i, 1) = 0
End If
Next i
Next j
'test end
''''''''''''
intTime2 = GetTickCount
MsgBox (intTime2 - intTime1)
End Sub
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:
Sub Test4()
Dim intTime1 As Long
Dim intTime2 As Long
Dim arrInput(1 To 20000, 1 To 1) As Double
Dim dblOutput As Integer
Dim i As Long
Dim j As Long
For i = 1 To 20000
arrInput(i, 1) = Rnd
Next i
intTime1 = GetTickCount
''''''''''''
'test start
For j = 1 To 1000 * 10
For i = 1 To 20000
If arrInput(i, 1) > 0.5 Then
dblOutput = 1
Else
dblOutput = 0
End If
Next i
Next j
'test end
''''''''''''
intTime2 = GetTickCount
MsgBox (intTime2 - intTime1)
End Sub
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:
Sub test5()
Dim intTime1 As Long
Dim intTime2 As Long
Dim dblInput As Double
Dim dblOutput As Integer
Dim i As Long
Dim j As Long
dblInput = 0.2
intTime1 = GetTickCount
''''''''''''
'test start
For j = 1 To 50 * 10
For i = 1 To 20000
If dblInput > 0.5 Then
dblOutput = 1
Else
dblOutput = 0
End If
Next i
Next j
dblInput = 0.7
For j = 1 To 50 * 10
For i = 1 To 20000
If dblInput > 0.5 Then
dblOutput = 1
Else
dblOutput = 0
End If
Next i
Next j
'test end
''''''''''''
intTime2 = GetTickCount
MsgBox (intTime2 - intTime1)
End Sub
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:
Sub test6()
Dim intTime1 As Long
Dim intTime2 As Long
Dim intInput As Integer
Dim intOutput As Integer
Dim i As Long
Dim j As Long
intInput = 0
intTime1 = GetTickCount
''''''''''''
'test start
For j = 1 To 50 * 10
For i = 1 To 20000
If intInput > 0.5 Then
intOutput = 1
Else
intOutput = 0
End If
Next i
Next j
intInput = 1
For j = 1 To 50 * 10
For i = 1 To 20000
If intInput > 0.5 Then
intOutput = 1
Else
intOutput = 0
End If
Next i
Next j
'test end
''''''''''''
intTime2 = GetTickCount
MsgBox (intTime2 - intTime1)
End Sub
Test 7, Collection:
Same as Test2 only 3 only a collection was used instead of the input array:
Sub test7()
Dim intTime1 As Long
Dim intTime2 As Long
Dim colInput As Collection
Dim arrOutput(1 To 20000, 1 To 1) As Integer
Dim i As Long
Dim j As Long
Set colInput = New Collection
For i = 1 To 20000
colInput.Add (Rnd)
Next i
intTime1 = GetTickCount
''''''''''''
'test start
For j = 1 To 1 * 10
For i = 1 To 20000
If colInput.Item(i) > 0.5 Then
arrOutput(i, 1) = 1
Else
arrOutput(i, 1) = 0
End If
Next i
Next j
'test end
''''''''''''
intTime2 = GetTickCount
MsgBox (intTime2 - intTime1)
End Sub
Result:
Result 1:
Test 7 was unable to finish. Collections are not a good method for working with large data.
Result 2:
Double and integer arrays are faster than variant arrays.
Result 3:
The VBA “if” statement is faster than the Excel If() function when working with variables.
Result 4:
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%
See also: