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.