Trace Precedents in Excel

Trace Precedents in Excel

Precedents:

It is a Excel method which shows relationship between formulas using arrow objects. This method requires a valid formula in a cell or range to show the relationship else excel will prompt warning.

  1. Select the cell that contains the formula for which you want to find precedent cells.
  2. To display a tracer arrow to each cell that directly provides data to the active cell, on the Formulas tab, in the Formula Auditing group, click Trace Precedents .

Look at the formula highlighted in circle above and see the trace points which is indicating current cell refers to three cells and builds a formula. Let’s see how we can show Trace Precedents using code in excel?

Prerequisites:

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

C# code example:

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

	//Get worksheet reference
	excel.Worksheet oWorksheet = Globals.ThisAddIn.Application.ActiveSheet;

	//check if selection has formula because trace precedents works only on formula cells
	if (Globals.ThisAddIn.Application.WorksheetFunction.IsFormula(oRange))
	{
		oRange.ShowPrecedents();
	}
}

VB.Net code example:

Private ShowPrecedents_Click(sender As Object, e As RibbonControlEventArgs) Handles ShowPrecedents.Click

	'Bind Range object from selection
	Dim oRange As excel.Range
	oRange=Globals.ThisAddIn.Application.Selection

	'check if selection has formula because trace precedents works only on formula cells
	if Globals.ThisAddIn.Application.WorksheetFunction.IsFormula(oRange) Then
		oRange.ShowPrecedents()
	End If

End Sub

VBA code example:

Public Sub ShowPrecedents()
    Selection.ShowPrecedents
End Sub

Next >> Trace Dependents in Excel

Leave a Reply

Your email address will not be published.