VBA is compiled in run-time, which has a huge negative impact on it's performance, everything built-in will be faster, try to use them.

As an example I'm comparing SUM and COUNTIF functions, but you can use if for anything you can solve with WorkSheetFunctions.

A first attempt for those would be to loop through the range and process it cell by cell (using a range):

```
Sub UseRange()
Dim rng as Range
Dim Total As Double
Dim CountLessThan01 As Long
Total = 0
CountLessThan01 = 0
For Each rng in Sheets(1).Range("A1:A100")
Total = Total + rng.Value2
If rng.Value < 0.1 Then
CountLessThan01 = CountLessThan01 + 1
End If
Next rng
Debug.Print Total & ", " & CountLessThan01
End Sub
```

One improvement can be to store the range values in an array and process that:

```
Sub UseArray()
Dim DataToSummarize As Variant
Dim i As Long
Dim Total As Double
Dim CountLessThan01 As Long
DataToSummarize = Sheets(1).Range("A1:A100").Value2 'faster than .Value
Total = 0
CountLessThan01 = 0
For i = 1 To 100
Total = Total + DataToSummarize(i, 1)
If DataToSummarize(i, 1) < 0.1 Then
CountLessThan01 = CountLessThan01 + 1
End If
Next i
Debug.Print Total & ", " & CountLessThan01
End Sub
```

But instead of writing any loop you can use `Application.Worksheetfunction`

which is very handy for executing simple formulas:

```
Sub UseWorksheetFunction()
Dim Total As Double
Dim CountLessThan01 As Long
With Application.WorksheetFunction
Total = .Sum(Sheets(1).Range("A1:A100"))
CountLessThan01 = .CountIf(Sheets(1).Range("A1:A100"), "<0.1")
End With
Debug.Print Total & ", " & CountLessThan01
End Sub
```

Or, for more complex calculations you can even use `Application.Evaluate`

:

```
Sub UseEvaluate()
Dim Total As Double
Dim CountLessThan01 As Long
With Application
Total = .Evaluate("SUM(" & Sheet1.Range("A1:A100").Address( _
external:=True) & ")")
CountLessThan01 = .Evaluate("COUNTIF('Sheet1'!A1:A100,""<0.1"")")
End With
Debug.Print Total & ", " & CountLessThan01
End Sub
```

And finally, running above Subs 25,000 times each, here is the average (5 tests) time in milliseconds (of course it'll be different on each pc, but compared to each other they'll behave similarly):

- UseWorksheetFunction: 2156 ms
- UseArray: 2219 ms (+ 3 %)
- UseEvaluate: 4693 ms (+ 118 %)
- UseRange: 6530 ms (+ 203 %)

This modified text is an extract of the original Stack Overflow Documentation created by following contributors and released under CC BY-SA 3.0

This website is not affiliated with Stack Overflow