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:

  1. Get an input value
  2. check if its larger than 0.5
  3. If true assign the value 1 to an output variable
  4. 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

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:

Leave a Reply

Your email address will not be published. Required fields are marked *