Excel: Check Sheet Dependencies within a Workbook?

Question

I'm in the process of refactoring a huge workbook woth a lot of legacy parts, redundant computations, cross-dependencies etc.

Basically, I'm trying to remove unneeded sheets and implement some proper information flow within the workbook. Is there a good way to extract the dependencies between the sheets (with VBA)?

Thanks Martin

As in ? http://superuser.com/questions/234124/excel-dataflow-uml-viewer-navigator-visualiser-tool-hint — Fionnuala, Feb 21 '11 at 10:45
Trace Precedents only shows the precedents of a single cell. What I'm looking for is something like what the Links menu does for inter-workboot links, but for worksheets within a workbook. — Martin, Feb 21 '11 at 11:28

score 5 · Answer 1 · answered Feb 21 '11 at 12:42

5

You can use ShowPrecedents and NavigateArrow. here is some pseudocode

for each oCell in oSht containing a formula
ocell.showprecedents
do until nomoreprecedents
i=i+1
Set oPrec = oCell.NavigateArrow(True, 1, i)
If not oPrec.Parent Is oSht Then
' off-sheet precedent
endif
loop
next ocell

answered Feb 21 '11 at 12:42

Charles Williams

23,121
5
38
38

1

Wow, I didn't realise there was actually a way to track off-sheet references. Superb. – Chris Rae Feb 21 '11 at 22:22

score 0 · Answer 2 · answered May 11 '16 at 23:33

You can follow the steps at "Find external references that are used in cells" topic of the following link:

Find external references in a worbook

But instead of enter the "[" you should enter the name of the sheet you're trying to find its dependencies. It will display a large list of every single cell referencing the sheet, but at the end it works. Haven't find the way to group by Sheet.

score 0 · Accepted Answer · answered Apr 16 '11 at 18:59

I came up with a little sub to do this. It moves all the sheets into seperate workbooks and prints out the dependencies. The advantage over using showPrecedents is that it captures all links including names, embedded forms/diagramms etc.

Word of warning: Moving worksheets isn't undo-able, save your workbook before running this and close (without saving) and re-open afterwards.

Sub printDependencies()
    ' Changes workbook structure - save before running this
    Dim wbs As VBA.Collection, wb As Workbook, ws As Worksheets
    Dim i As Integer, s As String, wc As Integer
    Set ws = ThisWorkbook.Worksheets
    Set wbs = New VBA.Collection
    wbs.Add ThisWorkbook, ThisWorkbook.FullName

    For i = ws.Count To 2 Step -1
        ws(i).Move
        wc = Application.Workbooks.Count
        wbs.Add Application.Workbooks(wc), Application.Workbooks(wc).FullName
    Next
    Dim wb As Workbook

    For Each wb In wbs
        For Each s In wb.LinkSources(xlExcelLinks)
            Debug.Print wb.Worksheets(1).Name & "<-" & wbs(s).Worksheets(1).Name
        Next
    Next
End Sub

The code isn't very polished or user-friendly, but it works.

I have a Compile error my friend. For Each control variable must be Variant or Object. At
For Each s In wb.LinkSources (xlExcelLinks) I hope you can help me with it. — Josue Rocha, May 11 '16 at 22:32

Excel: Check Sheet Dependencies within a Workbook?

3 Answers3