Visual Basic code to know all the PRECEDENTS in a worksheet in Excel 2013

Vote count:

0

I've been stuck the last weeks trying to figure out how to know all the wrksheets related to my current sheet in Excel 2013, well, not all, just the Precedents sheets, I don't need the Dependents sheets. I know that with the tab "Formulas", the "Trace Precedents" button will help me to know it cell by cell. The thing is that I would prefer the automated code because I've around 2,000 and 3,000 cells for each worksheet. So I've spend the time looking for a VBA code to do this but I haven't found nothing.

There is the following code in internet but there is one problem with it, when I've more than 1 sheet refered in the formula of my cell, it just throw the first sheet, the others don't appear in the Mesage Box.

Sub ShowLinks()

''Find formulas that reference other sheets, and ''display a list of referenced sheets ''

Dim Rng As Range, _
    c As Range
Dim dic As Object, _
    dic2 As Object
Dim x, y, z
Dim j As Long, _
    k As Long, _
    m As Long
Dim Sht As Worksheet
Dim strSheets As String

Set dic = CreateObject("Scripting.Dictionary")
Set dic2 = CreateObject("Scripting.Dictionary")
Set Rng = Cells.SpecialCells(xlCellTypeFormulas)
j = 0
For Each c In Rng
    If InStr(1, c.Formula, "!") > 0 Then 'references another sheet
    'load all unique strings into a Dictionary object
        x = Split(c.Formula, "!")
        If Not dic.exists(x(0)) Then
            j = j + 1
            dic.Add x(0), j
        End If
    End If
Next c
If j=0 Then 'no formulas with links
    MsgBox "This sheet is not linked to other sheets", vbInformation
    GoTo ExitHere
End If
y = dic.keys
'Now we have a list of unique strings containing sheet names
'referenced from this sheet. Next step is to list just the sheet names.
m = 0
For k = LBound(y) To UBound(y)
    For Each Sht In ActiveWorkbook.Worksheets
        If InStr(1, y(k), Sht.Name) > 1 Then
            If Not dic2.exists(Sht.Name) Then
                m = m + 1
                dic2.Add Sht.Name, m
            End If
            Exit For
        End If
    Next Sht
Next k
strSheets = Join(dic2.keys, vbCrLf)
MsgBox strSheets

ExitHere:
    Set dic2 = Nothing
    Set dic = Nothing
    Set Rng = Nothing
End Sub

The code above is from this site: http://ift.tt/19wbnZQ

Sadly I couldn't be able to solve it and make it print all the sheets precedents in current worksheet. I hope somebody can help me with this.

Beforehand, Thank you so much!

PS. I've already tried with the INQUIRE tool but, due they are a lot of cells and worksheets (more than 200 worksheets in total) the tool crashes due to an stack overflow error. It didn'r work for me.

asked 1 min ago

Josue Rocha

118

Visual Basic code to know all the PRECEDENTS in a worksheet in Excel 2013