Excel VBA worksheet.names 与 worksheet.range [英] Excel VBA worksheet.names vs worksheet.range

问题描述

我在名为 bob 的工作表上创建了一个已定义的名称/范围，指向单个单元格.此工作表上设置了许多其他名称/范围，但我没有创建这些名称/范围.除了我的以外，所有数字/范围都可以正常工作.

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.

我应该能够通过使用以下任一语句来引用此单元格的内容:

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

但是，出于某种原因，只有第二个语句引用 Range 有效.第一个在 Names 列表中找不到名称.

However, only the second statement, referring to the Range works for some reason. The first one can't find the name in the Names list.

我的问题是:

1. Name 和 Range 之间有什么区别(如果有)?
2. 这与我的姓名/范围的全局/本地范围有关吗?
3. 如何在工作表上创建其他名称/范围，以便它们同时出现在工作表 Name 和 Range 列表中?

1. What is the difference, if any, between a Name and a Range?
2. Is this something to do with the global/local scope of my name/range?
3. How were the other name/ranges created on the sheet so that they appear in both the worksheets Name and Range list?

推荐答案

是的，你是对的.名称可以是本地的(属于工作表)和全局的(属于工作簿).

Yes, you are right. Names can be local (belong to a worksheet) and global (belong to a workbook).

(worksheet object).Names("bob") 只会找到本地名称.您的名字显然是全局的，因此您可以将其作为 (worksheet object).Workbook.Names("bob").RefersToRange.

(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.

其他名称"可能是本地的.它们仅在其父工作表处于活动状态时才会出现在范围列表中(检查一下).要创建本地名称，请在其前面加上工作表名称，用！"分隔:'My Sheet Name'!bob.

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.

这篇关于Excel VBA worksheet.names 与 worksheet.range的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！