Category: [Excel]  Demo Available 

---

Excel VBA Evaluate Method. Excel Formulas/Functions In Macro Code

 

Excel VBA WorksheetFunction Property

As you may, or may not know, we can use standard excel worksheet functions in VBA by preceding the Function name with WorksheetFunction or Application. E.g. the example below will sum the range A1:A10.

Sub SumUp()
    MsgBox WorksheetFunction.Sum(Sheet1.Range("A1:A10"))
End Sub



Sub SumUp2()
    MsgBox Application.Sum(Sheet1.Range("A1:A10"))
End Sub

However, IF there ANY Formulae Errors in the range used, it will result in a RunTime Error so you may want to replace all error cells with zero, or at least confirm the range has no formula errors with the SpecialCells Method, like below;

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

Excel VBA Evaluate Method

With the Evaluate Method, we must still check for, or fix, formula errors in the range we Evaluate with an excel formula, but there is less typing and we can simply copy Formulas from the formula bar. The VBA Macros below shows some uses of the Evaluate Method, BUT contain no error checks. Don't forget to add them like above.

Sub EvaluateSum()
    MsgBox Evaluate("SUM(1,2)")
    MsgBox Evaluate("SUM(Sheet1!A1:A10)")
End Sub


Sub EvaluateVlookup()
    MsgBox Evaluate("VLOOKUP(Sheet2!A1,Sheet1!B1:C10,2,FALSE)")
End Sub

 

See also Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions.

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery

---

• 
• 
• 
• 
• 
•