Trace Error in Excel Addin

Trace Error in Excel Addin

Trace Error:

In Excel if you want to check errors like reference error, name error, value error the Trace Error feature enables you to highlight all the errors available in active sheet. The errors would be highlighted with the help of arrows in each cell by pointing references. Let’s see how we can do a error checking in Excel.

  • Navigate Formulas tab
  • Toggle Error Checking command under Formula Auditing group
  • Select Trace Error command and see sheet output as shown below

Let’s put code to automate above command. Please be acknowledged the DOT NET code I am using is based on VSTO (Visual Studio Tools for Office) for both C# and VB styles. Hence developers are advised to follow the prerequisites.

Prerequisites:

  • Visual Studio 2015 or above having Microsoft Office for Developer Tool installed
  • Create Excel Addin in C#/VB code style (Visual Studio Tools for Office)
  • Excel 2010 or above
  • Create a ribbon designer and put button

C# code example:

private void btnTraceError_Click(object sender, RibbonControlEventArgs e)
{
	//Get current selection
	excel.Range oRange = Globals.ThisAddIn.Application.Selection;

	//Enable tracer
	oRange.ShowErrors();
}

VB.Net code example:

Private Sub btnShowErrors_Click(sender As Object, e As RibbonControlEventArgs) Handles btnShowErrors.Click
	'Declare range variable
	dim oRange as excel.Range
	oRange=Globals.ThisAddIn.Application.Selection

	'Show errors
	oRange.ShowErrors()
End Sub

VBA code example:

Public Sub ShowErrors()
    Selection.ShowErrors
End Sub

Let’s see output: before code execution:

Post code execution output:

Next >> Detect errors in excel

Leave a Reply

Your email address will not be published.