Microsoft Excel：以编程方式创建隐藏的命名范围
有人告诉我，Excel 对象模型允许不属于任何工作表的 Range，但包含一组单元格并在工作簿中用名称表示。
谁能向我解释一下这些如何适合 Excel 对象模型以及如何以编程方式（在 VBA 或 .NET 源代码中）创建这样的东西。
谢谢。
I am told that the Excel object model permits a Range that is not a part of any sheet, yet contains a set of cells and is denoted by a name in the workbook.
Can anyone explain to me how these fit into the Excel object model and how one would go about creating such a thing programatically (either in VBA or .NET source code).
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
你的问题有点模糊，但我会尝试一下。
好吧，正如戴夫所描述的，您可以为工作表上的特定单元格范围指定“范围名称”，然后您可以以编程方式引用它，但这听起来不像您所要求的。
听起来您好像在问“是否有一个抽象的单元格范围可供 VBA 代码使用，但实际上任何工作表上都不存在？”答案是否定的，即使命名范围也只是对真实工作表上的一组真实单元格的方便引用。
但是，您可以以编程方式隐藏工作表，以便用户看不到它，但仍可以使用该工作表上的单元格和区域。只要做：
你问什么是“VeryHidden”？
这意味着用户无法转到“格式”、“工作表”、“取消隐藏”并使工作表可见。
因此，如果我正确理解您想要的内容，只需以编程方式隐藏其中一张工作表，然后使用 Dave 的技术创建对此隐藏（或 VeryHidden）工作表上的范围的命名引用。
Your question is a little vague, but I'll give it a shot.
Well, as Dave describes, you can give a specific range of cells on a sheet a "Range Name" which you can then refer to programatically, but that doesn't sound like what you are asking.
It sounds like you are asking "is there an abstract RANGE of cells available to be used by VBA code that doesn't literally exist on any worksheet?" The answer to this is no, even named ranges are simply a convenient reference to a real set of cells on a real worksheet.
You can, however, programatically hide a worksheet so that the user doesn't see it, and still work with cells and ranges on that sheet. Just do:
What's "VeryHidden", you ask?
It means that the user can't go to Format, Sheet, Unhide and make the sheet visible.
So if I'm correctly understanding what you want, just programatically hide one of the sheets, then use Dave's technique to create a named reference to a range on this hidden (or VeryHidden) sheet.
那将是一个命名范围。您可以引用选定的单元格，只需在编辑栏旁边的“A1”处键入名称即可。这将创建一个不会更改的命名范围。
或者，您可以创建基于公式的命名范围，因此可能会随着电子表格中的数据变化而变化。您可以通过“定义名称”选项（位于 Office 2010 的公式功能区中）执行此操作。
命名范围可以从 VBA 和（我很确定）从 .net 访问。
因此，您可以像这样从 vba 访问命名范围：
That would be a named range. You can reference a selection of cells, and just type a name where it says 'A1' next to the formula bar. That creates a named range that doesn't change.
Alternatively you can create a named range that is based on a formula, and therefore potentially changes as data in the spreadsheet changes. You do this from the 'Define Name' option (which is in the Formulas Ribbon in Office 2010).
Named ranges can be accessed from VBA, and (I'm pretty sure) from .net.
So you'd access the named range from vba like so:
是的，有一个 NamedRange 控件在 VSTO 的
Microsoft.Office.Tools.Excel命名空间中。这是一个主机控件，它是与本机 Range 控件不同在Microsoft.Office.Interop.Excel.Range命名空间中。Yes, there is a NamedRange control in the
Microsoft.Office.Tools.Excelnamespace in VSTO. This is a host control which is different from the native Range control in theMicrosoft.Office.Interop.Excel.Rangenamespace.