Trace Dependents in Excel

Trace Dependents in Excel

Trace Dependents:

Displays the relationships between formulas and cells. To execute Trace Dependents selection must point to a cell or range having formula.

  1. Select the cell for which you want to identify the dependent cells.
  2. To display a tracer arrow to each cell that is dependent on the active cell, on the Formulas tab, in the Formula Auditing group, click Trace Dependents .

In Above example I have used total three formulas to identify where A and B formulas are dependent on Formula A-B. Let’s put some code around to achieve above manual process to make it automated 🙂

Note: in this example the C# code style is used with VSTO (Visual Studio Tools for Office) technology which is a Office development solution and you should have following prerequisites before using given code:

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 btnTraceDependets_Click(object sender, RibbonControlEventArgs e)
{
	//Get current selection
	excel.Range oRange = Globals.ThisAddIn.Application.Selection;

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

VB.Net code example:

Private ShowDepndents_Click(sender As Object, e As RibbonControlEventArgs) Handles ShowDepndents.Click

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

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

End Sub

VBA code example:

Public Sub ShowDependents()
    'Bind range object
    Dim oRange As Range
    Set oRange = Selection
    
    'Check if selction points to non formula range
    If WorksheetFunction.IsFormula(oRange) Then
        oRange.ShowDependents
    End If
End Sub

Leave a Reply

Your email address will not be published.