vba 在不知道工作表名称的情况下在 Excel 中查找和激活命名范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23251466/
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
Find and activate named range in Excel without knowing Worksheet name
提问by mlnyc
I'm writing a macro that will update a bunch of workbooks.
我正在编写一个宏来更新一堆工作簿。
Assume the workbook is already open.
假设工作簿已经打开。
I need to activate (or select, either one) a cell that has a name (named range) but the kicker is that I don't know what worksheet it is in.
我需要激活（或选择一个）具有名称（命名范围）的单元格，但问题是我不知道它在哪个工作表中。
In other words: I am guaranteed that:
换句话说：我保证：
- The named range exists.
- The named range is globally unique to the open workbook.
- 命名范围存在。
- 命名范围对于打开的工作簿是全局唯一的。
I am not guaranteed that:
我不保证：
- The named range will be in the same worksheet index.
- The worksheets will be named the same between workbooks.
- 命名范围将在同一个工作表索引中。
- 工作簿之间的工作表将命名相同。
Any advice will be appreciated, even one that shows me how to back into a worksheet from a global named range since that will be enough for me to then activate the worksheet and go from there. But ideally the solution will show me how to activate/select/update (the final result would be that I update the cell, or insert rows below if that helps) a named range cell without knowing the worksheet name.
任何建议将不胜感激，即使是向我展示如何从全局命名范围返回到工作表的建议，因为这足以让我激活工作表并从那里开始。但理想情况下，该解决方案将向我展示如何在不知道工作表名称的情况下激活/选择/更新（最终结果是我更新单元格，或在下面插入行，如果有帮助）命名范围单元格。
I am doing this in Excel 2007
我在 Excel 2007 中执行此操作
Thanks!
谢谢！
回答by Dmitry Pavliv
Suppose you have named range "test".
假设您已命名 range "test"。
Way 1:
方式一：
Dim rng As Range
Set rng = Range("test")
'or if you have more than one workbook opened
'Set rng = ThisWorkbook.Names("test").RefersToRange
'select sheet
rng.Parent.Select
'select named range
rng.Select
Way 2:
方式二：
'for currently active workbook
Application.Goto "test"
