Excel VBA worksheet.names 与 worksheet.range
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2175962/
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 VBA worksheet.names vs worksheet.range
提问by cOrOllArY
I have created a defined name/range on a worksheet called bob, pointing to a single cell. There are a number of other name/ranges set up on this worksheet, which I didn't create. All the number/ranges work perfectly except for mine.
我在名为 的工作表上创建了一个定义的名称/范围bob，指向一个单元格。此工作表上还设置了许多其他名称/范围，但不是我创建的。除了我的之外，所有数字/范围都可以完美运行。
I should be able to refer to the contents of this cell by using either of the following statements:
我应该能够使用以下任一语句来引用此单元格的内容：
(worksheet object).Names("bob").RefersToRange.Value
(worksheet object).Range("bob").Value
However, only the second statement, referring to the Rangeworks for some reason. The first one can't find the name in the Nameslist.
但是，只有第二个声明，Range出于某种原因提到了作品。第一个在Names列表中找不到名称。
My questions are:
我的问题是：
- What is the difference, if any, between a
Nameand aRange? - Is this something to do with the global/local scope of my name/range?
- How were the other name/ranges created on the sheet so that they appear in both the worksheets
NameandRangelist?
- a
Name和 a之间有什么区别（如果有）Range？ - 这与我的名称/范围的全局/本地范围有关吗？
- 如何在工作表上创建其他名称/范围，以便它们同时出现在工作表
Name和Range列表中？
回答by GSerg
Yes, you are right. Names can be local (belong to a worksheet) and global (belong to a workbook).
你是对的。名称可以是本地的（属于工作表）和全局的（属于工作簿）。
(worksheet object).Names("bob")will only find a local name. Your name is obviously global so you could access it as (worksheet object).Workbook.Names("bob").RefersToRange.
(worksheet object).Names("bob")只会找到本地名称。您的名字显然是全球性的，因此您可以将其作为(worksheet object).Workbook.Names("bob").RefersToRange.
The "other names" are probably local. They only appear in the ranges list when their parent sheet is active (check that out). To create a local name, prepend it with the sheet name, separated by a '!': 'My Sheet Name'!bob.
“其他名称”可能是本地的。它们仅在其父工作表处于活动状态时才会出现在范围列表中（请查看）。要创建本地名称，请在其前面加上工作表名称，并以 '!': 分隔 'My Sheet Name'!bob。
回答by xdhmoore
I don't know how to do it with code, but if you go to the Name Manager Under the Formula tab group in the Ribbon in Excel 2007, you can create names and choose their scope.
我不知道如何使用代码来实现，但是如果您转到 Excel 2007 功能区中公式选项卡组下的名称管理器，您可以创建名称并选择其范围。
