Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Excel: Check Sheet Dependencies within a Workbook?

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

like image 397
Martin Avatar asked Feb 21 '11 09:02

Martin


People also ask

How do you see what sheets are linked in Excel?

Open the file that you want to analyze for worksheet links. On the Inquire tab, click Worksheet Relationship.

How do you check if an Excel sheet is linked to another workbook?

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.


1 Answers

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
like image 145
Charles Williams Avatar answered Sep 21 '22 15:09

Charles Williams