# Trace dependents for entire worksheet



## sarahrosenberg

I have an excel file with lots and lots of tabs.  A file that has been worked in for many months.  Some of the tabs seem outdated and may be deleted.  Is there a way to "trace dependents" for an entire tab?  To see if any other tabs link to a sheet before deleting?

TIA!!

Sarah


----------



## Rick Rothstein

You will need to test this against known, complex worksheets to be sure, I am almost positive it works correctly (Precedents and Dependents can be a real pain to work with when they involve "foreign" sheets). Select the sheet you want to test and run the macro below... it will pop up a MessageBox either showing you the addresses it found with Dependents on other sheets or tell you it found none... it also outputs the addresses, if any were found, to the VB editor's Immediate Window so you can refer to them while you investigate the what-and-why of those Dependents. Note that there is also a line of code that was remarked out... if you unremark it, all cells with foreign Dependents will be filled with red so you can see them (I remarked the line out just in case you had other colored cells that were red or had colors that you would not want overwritten in case they were flagged).

Okay, here is the code... select the sheet you want to test and run it.


		Code:
__


Sub IdentifyForeignDependentsOnActiveSheet()
  Dim ShapeCount As Long, DirectDependentCount As Long, R As Range, DependantCells As Range
  Application.ScreenUpdating = False
  ActiveSheet.ClearArrows
  ShapeCount = ActiveSheet.Shapes.Count
  For Each R In ActiveSheet.UsedRange
    R.ShowDependents
    If ActiveSheet.Shapes.Count > ShapeCount Then
      On Error Resume Next
      If ActiveSheet.Shapes.Count > ShapeCount + Range(R.Dependents.AddressLocal).Count Then
        If DependantCells Is Nothing Then
          Set DependantCells = R
        Else
          Set DependantCells = Union(R, DependantCells)
        End If
      End If
      On Error GoTo 0
    End If
    ActiveSheet.ClearArrows
  Next
  If Not DependantCells Is Nothing Then
    ' DependantCells.Interior.ColorIndex = 3
    Debug.Print "=====" & vbLf & ActiveSheet.Name & vbLf & "-----" & _
                vbLf & DependantCells.Address(0, 0) & vbLf & "====="
    MsgBox DependantCells.Address(0, 0)
  Else
    MsgBox "No dependent cells on this sheet"
  End If
  Application.ScreenUpdating = True
End Sub


----------



## sarahrosenberg

This is awesome.  It works perfectly and will save me a TON of time!

Thank you!

Sarah


----------



## Rick Rothstein

sarahrosenberg said:


> This is awesome.  It works perfectly and will save me a TON of time!


Great! I am glad it worked for you... it took a little bit of time to figure out (as I said, Precedents and Dependents, when they involve references to other sheets, are a real pain to work with... I wish Excel had implemented a more straightforward object model for them).


----------



## davelot

This is a great functionality to have, though I did find it didn't work properly in my workbook.  In some sheets it found external dependencies, but I have sheets where it reports no dependencies when they actually exist.  Just a heads up if anyone else comes across this code as I did.  As it is an older post, maybe the newer versions of Excel changed things that caused it to break.  I'm using 2013.


----------



## Rick Rothstein

davelot said:


> This is a great functionality to have, though I did find it didn't work properly in my workbook.  In some sheets it found external dependencies, but I have sheets where it reports no dependencies when they actually exist.  Just a heads up if anyone else comes across this code as I did.  As it is an older post, maybe the newer versions of Excel changed things that caused it to break.  I'm using 2013.


Can you post a copy of the workbook(s) that my code failed on to DropBox so I and anyone else who might want to fool-around with this can download it? If you have information in the workbook that you would not want to expose to the world, and if you trust me not to abuse the information in it, you can email it to me directly and I will see if I can spot where the code might be failing at. I am using (and probably was using when I developed the code) XL2010 right now, but I don't think Excel version will be behind the problem. Also, if you either upload to DropBox or email me directly, could you point out where you see it failing so I have some kind of starting point for my investigation.

If you decide to send to me, my email address is rick  DOT  news  AT  verizon  DOT net (you should be able to figure out how to turn that into a real email address). Please put the thread title in your email so I can find my way back to this thread more easily.


----------



## Rick Rothstein

I got your email with the workbook attached and I see what you mean. It appears that I omitted a "(1)" suffix on a Dependent range reference (highlighted in red below). I am not an expert on this Dependent/Precedent object structure in Excel (which I think is far more complex than it needed to be), so I cannot be 100% sure the fix below is totally correct... I *believe* it is because every cell the code identified that I randomly sampled appears to have an offset dependent somewhere off sheet according to the "Trace Dependents" button on the Ribbon when I checked... but I cannot be 100% sure my code is 100% accurate (again, I do believe it is). So, with that caution, here is the revised code (only what is highlighted in red was added)...


		Code:
__


[table="width: 500"]
[tr]
	[td]Sub IdentifyForeignDependentsOnActiveSheet()
  Dim ShapeCount As Long, DirectDependentCount As Long, R As Range, DependantCells As Range
  Application.ScreenUpdating = False
  ActiveSheet.ClearArrows
  ShapeCount = ActiveSheet.Shapes.Count
  For Each R In ActiveSheet.UsedRange
    R.ShowDependents
    If ActiveSheet.Shapes.Count > ShapeCount Then
      On Error Resume Next
      If ActiveSheet.Shapes.Count > ShapeCount + Range(R.Dependents[B][COLOR="#FF0000"][SIZE=3](1)[/SIZE][/COLOR][/B].AddressLocal).Count Then
        If DependantCells Is Nothing Then
          Set DependantCells = R
        Else
          Set DependantCells = Union(R, DependantCells)
        End If
      End If
      On Error GoTo 0
    End If
    ActiveSheet.ClearArrows
  Next
  If Not DependantCells Is Nothing Then
    ' DependantCells.Interior.ColorIndex = 3
    Debug.Print "=====" & vbLf & ActiveSheet.Name & vbLf & "-----" & _
                vbLf & DependantCells.Address(0, 0) & vbLf & "====="
    MsgBox DependantCells.Address(0, 0)
  Else
    MsgBox "No dependent cells on this sheet"
  End If
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]

Once you verify that you are satisfied with the code and that it will not need any further modification, I will delete your email with the workbook attached as well as the copy of the workbook I used to test/modify my code. So make sure you come back to this thread and let us know whether the code is working correctly for you or not.


----------



## Rick Rothstein

Rick Rothstein said:


> I got your email with the workbook attached and I see what you mean. It appears that I omitted a "(1)" suffix on a Dependent range reference (highlighted in red below). I am not an expert on this Dependent/Precedent object structure in Excel (which I think is far more complex than it needed to be), so I cannot be 100% sure the fix below is totally correct...


I actually now think the above fix is not 100% guaranteed, but I did come up with a method that I am sure is foolproof... I simple look for a non-solid line after I ShowDependents (the code equivalent of "Trace Dependents")... if there is one, then there is a dependent on a different sheet. Here is the code with this new modification...


		Code:
__


[table="width: 500"]
[tr]
	[td]Sub IdentifyForeignDependentsOnActiveSheet()
  Dim ShapeCount As Long, L As Long, DirectDependentCount As Long, R As Range, DependantCells As Range
  Application.ScreenUpdating = False
  ActiveSheet.ClearArrows
  ShapeCount = ActiveSheet.Shapes.Count
  For Each R In ActiveSheet.UsedRange
    R.ShowDependents
    If ActiveSheet.Shapes.Count > ShapeCount Then
      For L = ActiveSheet.Shapes.Count To ShapeCount + 1 Step -1
        If ActiveSheet.Shapes(L).Line.DashStyle <> msoLineSolid Then
          If DependantCells Is Nothing Then
            Set DependantCells = R
          Else
            Set DependantCells = Union(R, DependantCells)
          End If
          Exit For
        End If
      Next
    End If
    ActiveSheet.ClearArrows
  Next
  If Not DependantCells Is Nothing Then
    MsgBox DependantCells.Address(0, 0)
  Else
    MsgBox "No dependent cells on this sheet"
  End If
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]


----------



## Radek Z

Hi Rick,

I registered myself on this site to thank you for providing this macro code. I was in a MAJOR time crunch to reverse-engineer a highly complex financial cost model with 30 worksheets, and create a high-level "how it works" PPTX for my VP to understand (the workbook was created by someone in finance who did a fantastic job of it, but it was insanely complex and somewhat inconsistent).

You saved me many hours of work, and have given me confidence that I didn't miss anything.

I also confirm that the last post's code works very well in Excel 365. I spent some time validating it in workbooks that I was sure where the foreign dependencies were located, and all were found as expected. Great job!!


----------



## Rick Rothstein

Thank you for the feedback... it is good to know that the code actually works. And welcome to the forum, please feel free to come back anytime with any questions you may have. There is a diverse core of volunteers here and the odds are great that at least one of them will have the expertise to address any questions you may throw our way.


----------



## sarahrosenberg

I have an excel file with lots and lots of tabs.  A file that has been worked in for many months.  Some of the tabs seem outdated and may be deleted.  Is there a way to "trace dependents" for an entire tab?  To see if any other tabs link to a sheet before deleting?

TIA!!

Sarah


----------



## ExcelHokie

Rick Rothstein said:


> Thank you for the feedback... it is good to know that the code actually works. And welcome to the forum, please feel free to come back anytime with any questions you may have. There is a diverse core of volunteers here and the odds are great that at least one of them will have the expertise to address any questions you may throw our way.


Hi Rick, I stubbled across this forum while looking for help with a financial model that I need to decode.  I'm not macro user and I'm having a hard time understanding where/how to insert this macro and run it.  My apologies in advance for the novice nature of my question.


----------

