Excel Made Easy

Vba trace error in vba in Excel

Trace Error in Excel lets you trace arrows back to cells referenced by a formula if it displays an error.�

The Trace Error�tool is available when auditing a worksheet within a workbook.

Note that the formula cell that you select must contain an error to use the Trace Error tool in Excel.

Invalid or inaccurate cell referencing is often a common cause of formula errors within Excel worksheets.

Using the Trace Error tool in Excel shows you the cells referenced by a selected formula that contains an error message.

If the cell referencing turns out to be the reason why the formula will not function, you can then fix the cell references within the formula cell to correct the error.

The following example demonstrates the Trace Error feature.

excel vba trace error in vba

The below code shows how to use the Trace Error technique in VBA.

For example purpose, the user has to input formula in such a way that will cause an error, so that, this feature can be demonstrated.

In our example, we have chosen a Divide by Zero Error, where in a number entered by the user in divided by zero, which will result in an error.

The numbers entered by the user are put in Input cells A12 and B12 and the corresponding output is shown in cells E12 and F12.

  1. Sub trc_error()
  2. Dim trsht As Worksheet
  3. Set trsht = Sheets("Q92")
  4. Dim num1 As Double
  5. Dim num2 As Double
  6. num1 = CDbl(InputBox("Enter first number"))
  7. trsht.Range("A12") = num1
  8. num2 = num1 = CDbl(InputBox("Enter second number"))
  9. trsht.Range("B12") = num2
  10. trsht.Range("E12").FormulaR1C1 = "=RC[-4]/0"
  11. trsht.Range("E12").Select
  12. Selection.ShowErrors.Select
  13. trsht.Range("F12").FormulaR1C1 = "=RC[-4]/0"
  14. trsht.Range("F12").Select
  15. Selection.ShowErrors.Select
  16. End Sub

The screenshots of editor and outputs are as shown below

excel vba trace error in vba

excel vba trace error in vba

 

You can find similar Excel Questions and Answer hereunder

1) How to copy files in Excel VBA is explained here

2) One of my formulas has returned an error - how can I trace the error to fix it?

3) How to do webscrapping in VBA. Here some basics

4) How to display messages boxes in VBA with the msgbox function

5) How can I set up ListBox using VBA to allow users to select multiple values?

6) How to see to which cells a cell is connected or used by. How to see the precedents of a cell

7) How to read a value from a cell in vba in Excel

8) How can I check if a file exists in a folder using VBA?

9) Vba to return week numbers in Excel

10) How to print a worksheet in Excel VBA

 

Here the previous and next chapter

Excel Made Easy was founded in 2002. Excel Made Easy is a service to people looking to learn Microsoft Excel or any other spreadsheet SW on the market.

We endeavor to provide the best service and support and to respond quickly to your questions.

Excel Made Easy is a proud sponsor of Dismantle-It.com the site that shows you the belly of your daily objects, Poupounette.com the site of cars loving cat and PowerPointMadeEasy.com the world top reference for PowerPoint Presentations questions and Pet Cemetery Online a site dedicated to your loving Pets.