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

Answer 1

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.

Answer 2

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

Answer 3

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.