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
Open the file that you want to analyze for worksheet links. On the Inquire tab, click Worksheet Relationship.
The conventional way to check links in Excel is by using the Edit Links feature: Data tab > Queries & Connections group > Edit Links. This will display the following information: Source - the name of the linked file. Type - the link type: a workbook or worksheet.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With