## 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 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%