Using Worksheet Functions

A Microsoft Excel worksheet function that operates on a range of cells is usually faster than a Visual Basic macro that accomplishes the same task. For example, the SUM worksheet function is much faster than Visual Basic code that iterates a range and adds the values in the range's cells. For example, the following code runs relatively slowly.

For Each c In Worksheets(1).Range("A1:A200")
    totVal = totVal + c.Value
Next

The following code runs faster than the preceding example.

totVal = Application.Sum(Worksheets(1).Range("a1:a200"))

Aggregating worksheet functions (such as PRODUCT, COUNT, COUNTA, and COUNTIF) are good candidates for replacing slower Visual Basic code, as are worksheet functions (such as MATCH and LOOKUP) that can take a range as an argument. For more information, see "Worksheet Functions" in Help.