vba Excel：检查工作簿中的工作表依赖性？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5064500/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel: Check Sheet Dependencies within a Workbook?
提问by Martin
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)?
基本上，我试图删除不需要的工作表并在工作簿中实现一些适当的信息流。有没有一种很好的方法来提取工作表之间的依赖关系（使用 VBA）？
Thanks Martin
谢谢马丁
采纳答案by Martin
I came up with a little sub to do this. It moves all the sheets into seperate workbooks and prints out the dependencies. The advantage over using showPrecedentsis that it captures all links including names, embedded forms/diagramms etc.
我想出了一个小子来做到这一点。它将所有工作表移动到单独的工作簿中并打印出依赖项。与使用相比的优势showPrecedents在于它可以捕获所有链接，包括名称、嵌入的表单/图表等。
Word of warning: Moving worksheets isn't undo-able, save your workbook before running this and close (without saving) and re-open afterwards.
警告：移动工作表是不可撤销的，在运行之前保存您的工作簿并关闭（不保存）并在之后重新打开。
Sub printDependencies()
' Changes workbook structure - save before running this
Dim wbs As VBA.Collection, wb As Workbook, ws As Worksheets
Dim i As Integer, s As String, wc As Integer
Set ws = ThisWorkbook.Worksheets
Set wbs = New VBA.Collection
wbs.Add ThisWorkbook, ThisWorkbook.FullName
For i = ws.Count To 2 Step -1
ws(i).Move
wc = Application.Workbooks.Count
wbs.Add Application.Workbooks(wc), Application.Workbooks(wc).FullName
Next
Dim wb As Workbook
For Each wb In wbs
For Each s In wb.LinkSources(xlExcelLinks)
Debug.Print wb.Worksheets(1).Name & "<-" & wbs(s).Worksheets(1).Name
Next
Next
End Sub
The code isn't very polished or user-friendly, but it works.
代码不是很优美或用户友好，但它有效。
回答by Charles Williams
You can use ShowPrecedents and NavigateArrow. here is some pseudocode
您可以使用 ShowPrecedents 和 NavigateArrow。这是一些伪代码
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
回答by Josue Rocha
You can follow the steps at "Find external references that are used in cells" topic of the following link:
您可以按照以下链接的“查找单元格中使用的外部引用”主题中的步骤操作：
Find external references in a worbook
But instead of enter the "[" you should enter the name of the sheet you're trying to find its dependencies. It will display a large list of every single cell referencing the sheet, but at the end it works. Haven't find the way to group by Sheet.
但是不要输入“[”，而应该输入要查找其依赖项的工作表的名称。它将显示引用工作表的每个单元格的大列表，但最后它可以工作。还没有找到按工作表分组的方法。
