<p class="j"><b>SEE ALSO:</b> <a href="http://www.ozgrid.com/News/excel-evaluate-formula-VBA.htm">Excel 4.0 EVALUATE Function</a></p><h2>Excel VBA WorksheetFunction Property</h2><p class="j">As you may, or may not know, we can use standard <a href="http://www.ozgrid.com/Excel/">Excel Worksheet Functions</a> in VBA by preceding the Function name with <b>WorksheetFunction</b> or <b>Application</b>. E.g. the example below will sum the range A1:A10.</p>
Sub SumUp()
MsgBox WorksheetFunction.Sum(Sheet1.Range("A1:A10"))
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub SumUp2()
MsgBox Application.Sum(Sheet1.Range("A1:A10"))
End Sub
Display More
<p class="j">However, IF there ANY <a href="http://www.ozgrid.com/Excel/formula-errors.htm">Formulae Errors</a> in the range used, it will result in a <a href="http://support.microsoft.com/kb/146864">RunTime Error</a> so you may want to replace all error cells with zero, or at least confirm the range has no formula errors with the <a href="http://www.ozgrid.com/VBA/special-cells.htm">SpecialCells Method</a>, like below;</p>
Sub ReplaceErrors()
On Error Resume Next
With Sheet1.Range("A1:A10")
.SpecialCells(xlCellTypeFormulas, xlErrors) = 0
MsgBox WorksheetFunction.Sum(.Cells)
End With
On Error GoTo 0
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub CheckForErrors()
Dim rErrCheck As Range
On Error Resume Next
With Sheet1.Range("A1:A10")
Set rErrCheck = .SpecialCells(xlCellTypeFormulas, xlErrors)
If Not rErrCheck Is Nothing Then
MsgBox "Please fix formula errors in selected cells"
Application.Goto .SpecialCells(xlCellTypeFormulas, xlErrors)
Else
MsgBox WorksheetFunction.Sum(.Cells)
End If
End With
On Error GoTo 0
End Sub
Display More
<h2>Excel VBA Evaluate Method</h2><p class="j">With the Evaluate Method, we must still check for, or fix, formula errors in the range we Evaluate with an <a href="http://www.ozgrid.com/Excel/">Excel Formula</a>, but there is less typing and we can simply copy Formulas from the formula bar. The <a href="http://www.ozgrid.com/VBA/">VBA Macros</a> below shows some uses of the Evaluate Method, BUT contain no error checks. <b>Don't forget to add them like above</b>.</p>