Technical Support

This page contains instructions on how to get the support

Spreadsheet Tools welcomes your inquiries. You can contact us by e-mail, or fill the Technical Request form on the Spreadsheet Tools website.

We suggest you to review the topics below. Possible you'll find answer to your questions about Dependency Auditor here.

Use Dependency Auditor engine from VBA macro

Dependency Auditor allows you to go through dependencies using Visual Basic macro.

This VBA code creates Dependency Auditor Engine:

Dim oEngine As Object
Set oEngine = CreateObject("XDA.Connect")

This engine has one method:
Trace( <Range>, <mode> )

Range - Excel's Range object for which dependency will be traced
mode - what you want to trace. Possible values:
  • precedents
  • dependents
  • inputs

It returns Trace Item object.

Trace Item properties:

  • Type integer value, which represents contents of this item: 0 - Range of cells, 1 - Single cell, 2 - constant value
  • Range Excel's Range object
  • Name if this item is constant entered in the NameBox (type=2), this property returns it's name
  • Count Count of the sub-items (dependents/precedents/inputs), depending on what you are tracing
  • Item( index ) returns sub-item. Index is integer 1-based value.

Trace Item properties:

  • Display show Dependency Auditor window, which starts tracing from this item.
  • Select select current item (if this is Range) in Excel

Macro which exports precendents of the specific range into another worksheet looks like this:

Public Sub ExportDependenciesToSheet2()

On Error GoTo err_handler

  ' clear Sheet2

  Dim sSheet As String, sAddress As String

  sSheet = Range("C6")
  sAddress = Range("D6")

  Dim wb As Workbook

  Set wb = Workbooks.Open(ThisWorkbook.Path & "\Example.xls")

  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual

  Dim oRange As Range

  Set oRange = wb.Sheets(sSheet).Range(sAddress)

  Dim oEngine As Object

  Set oEngine = CreateObject("XDA.Connect")

  Dim oTraceItem As Object

  Set oTraceItem = oEngine.Trace(oRange, "precedents")

  Dim nLastRow As Integer
  nLastRow = 2

  Call ExportTraceItem(oTraceItem, 1, nLastRow)

  Set oTraceItem = Nothing

  Set oEngine = Nothing


  Set wb = Nothing


  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic

  Sheets("Sheet2").Cells(1, 1).Select

  Exit Sub

  MsgBox Err.Description

End Sub

Private Sub ExportTraceItem(ByRef oItem As Object, nIndent As Integer, ByRef nLastRow As Integer)
  Dim ws As Worksheet

  Set ws = ThisWorkbook.Sheets("Sheet2")

  If oItem.Type = 2 Then
    ws.Cells(nLastRow, nIndent + 1).Value = oItem.Name
    ' worksheet name
    ws.Cells(nLastRow, nIndent + 1).Value = oItem.Range.Parent.Name
    ' cell address
    ws.Cells(nLastRow, nIndent + 2).Value = oItem.Range.Address
  End If

  nLastRow = nLastRow + 1

  Set ws = Nothing

  Dim oSubItem As Object

  ' export sub items
  Dim nItem As Integer
  For nItem = 1 To oItem.Count
    ' get pointer to sub item
    Set oSubItem = oItem.Item(nItem)
    ' export it
    Call ExportTraceItem(oSubItem, nIndent + 1, nLastRow)
    Set oSubItem = Nothing

End Sub