brainshwa.blogg.se

SAddr = rCell.Address(bAbs, bAbs, xlA1, bExt) RCell.showPrecedents ' even if rCell has no formula 'Return all direct precedents (bPrec=True) or dependents (bPrec=False) of rCellĭim sAddr As String, nLink As Integer, nArrow As IntegerĬonst bAbs As Boolean = False, bExt As Boolean = True Function findDents(rCell As Range, bPrec As Boolean) As Collection

It assumes all worksheets were made visible and arrows were cleared before use. Here's a simpler version of Colm Bhandal's findDents and findExternalDents. 'Sheets("Sprint Schedule Worksheet").Visible = False IArrowNum = iArrowNum + 1 'try another arrow ILinkNum = iLinkNum + 1 ' try another link If rLast.Address(external:=True) = ActiveCell.Address(external:=True) Then Exit Do 'Need to unhide sheets for external dependencies or the navigate arrow won't workĭim rLast As Range, iLinkNum As Integer, iArrowNum As IntegerĪctiveCell.NavigateArrow TowardPrecedent:=precDir, ArrowNumber:=iArrowNum, LinkNumber:=iLinkNum

'If precDir is true, then we look for precedents, else we look for dependentsįunction findDents(rng As Range, precDir As Boolean) As Collection 'this procedure finds the cells which are the direct precedents/dependents of the active cell 'Gives back only the dependencies that are not on the same sheet as rngįunction findExternalDents(rng As Range, precDir As Boolean) As Collection Set findExternalDependents = findExternalDents(rng, False) Set findExternalPrecedents = findExternalDents(rng, True)įunction findExternalDependents(rng As Range) As Collection Set findDependents = findDents(rng, False)įunction findExternalPrecedents(rng As Range) As Collection Set findPrecedents = findDents(rng, True)įunction findDependents(rng As Range) As Collection StMsg = stMsg & vbNewLine & dent.Address(external:=external)įunction findPrecedents(rng As Range) As Collection Sub showDents(dents As Collection, external As Boolean, header As String) 'external determines whether or not to print out the absolute address including workbook & worksheet Set precs = findExternalPrecedents(ActiveCell)Ĭall showDents(precs, True, "External Precedents: ")

Set deps = findExternalDependents(ActiveCell)Ĭall showDents(deps, True, "External Dependents: ") 'Module for examining depedencies to/from a sheet from/to other sheets I also added a call to a custom macro called unhideAll this was necessary for me as dependencies were not being found in hidden worksheets. The main addition by me is the ability to find external dependencies only, and the extension to both precedents and dependents. The macro still works, but I broke it out into functions allowing for more flexibility and reusability. This answer is based off Bill Manville's macro from many years back.