## Excel Speed, IF (Formula vs VBA)

*in*Excel

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:

- Excel VLOOKUP vs INDEX and MATCH Speed Comparison
- Excel VBA, Sheet Reference Speed
- Excel Functions and Formulas, IF()