从 VBA 中的命名范围获取值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26242960/
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
Getting a value from a named range in VBA
提问by user2506589
I want to retrieve a value from named range. Imagine a named range that has X columns and Y rows. I want to return a value, e.g., from column 2, row 3. The issue I experience is that if I write the code and run it, Excel throws an error. If I write the code into the watch window, it returns fine. See below
我想从命名范围中检索一个值。想象一个具有 X 列和 Y 行的命名范围。我想返回一个值，例如，从第 2 列第 3 行。我遇到的问题是，如果我编写代码并运行它，Excel 会引发错误。如果我将代码写入监视窗口，它会返回正常。见下文
...
Dim NamedRange As Variant: NamedRange = Range(NamedRangeName)
...
Dim ReturnValue As Object
Set ReturnValue = NamedRange(RowIndex, ColumnToRetrieveIndex) 'Throws Run-time error 424. Object required
If I write NamedRange(RowIndex, ColumnToRetrieveIndex) into the watch window, I can see the correct value of the cell.
如果我将 NamedRange(RowIndex, ColumnToRetrieveIndex) 写入监视窗口，我可以看到单元格的正确值。
I don't know VB much so I guess it's just some kind of syntax error how I want to pass it into the ReturnValue but I just can't figure it out.
我不太了解 VB，所以我想这只是某种语法错误，我想如何将它传递给 ReturnValue，但我就是想不通。
回答by Barry
Use this
用这个
ThisWorkbook.Names("myNamedRange").RefersToRange(1,1)
To get the value from the first cell in the named range "myNamedRange"
从命名范围“myNamedRange”中的第一个单元格获取值
With ThisWorkbook.Namesyou can access all named ranges of all the sheets within the current workbook.
With RefersToRangeyou get a reference to the actual range.
随着ThisWorkbook.Names您可以在当前工作簿中访问的所有表的所有指定范围。随着RefersToRange您获得对实际范围的参考。
回答by AjV Jsy
This looks like a good place to put a handy note (as it's a top search result):
If you name a cell "TopLeft", then Sheets(1).Range("TopLeft").Valuegets the contents, and Sheets(1).Range("TopLeft").Offset(2,3).Valuegets the value from 2 down, 3 across from there.
这看起来是放置便利注释的好地方（因为它是搜索结果的顶部）：
如果您将单元格命名为“TopLeft”，则Sheets(1).Range("TopLeft").Value获取内容，并Sheets(1).Range("TopLeft").Offset(2,3).Value获取从 2 向下、3 对向的值。
