|
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
This engine has one method:Set oEngine = CreateObject("XDA.Connect") Trace( <Range>, <mode> )
Range - Excel's Range object for which dependency will be traced
It returns Trace Item object.
mode - what you want to trace. Possible values:
Trace Item properties:
Macro which exports precendents of the specific range into another worksheet looks like this:
Public Sub ExportDependenciesToSheet2()
On Error GoTo err_handler ' clear Sheet2 Sheets("Sheet2").Cells.Clear 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 'wb.Close Set wb = Nothing exit_func: Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Sheets("Sheet2").Activate Sheets("Sheet2").Cells(1, 1).Select Exit Sub err_handler: 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 Else ' 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, nInd |