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

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL | More Free Downloads.. Best Value: Finance Templates Bundle

SEE ALSO: Excel 4.0 EVALUATE Function

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

New & Less Than You Think: List Managers | Working With Excel Sheets In VBA | Excel Charting Lessons | Delete rows by condition | TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL | More Free Downloads.. Best Value: Finance Templates Bundle

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to special@ozgrid.com 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Try out: Analyzer XL | Downloader XL | Smart VBA | Trader XL Pro (best value) | ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft Ž and Microsoft Excel Ž are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

GIVE YOURSELF OR YOUR COMPANY 24/7 MICROSOFT EXCEL SUPPORT & QUESTIONS