VBA：如何按定义的名称显示单元格值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3881370/
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
VBA: How to display a cell value by its defined name?
提问by Vantomex
I have already a defined name for a particular column in my worksheet. How to display a cell value by its defined name?
我已经为工作表中的特定列定义了名称。如何按定义的名称显示单元格值？
I've tried these:
我试过这些：
Public Sub Test()
Dim R As Range
Set R = ThisWorkbook.Names("SomeName").RefersToRange
MsgBox CStr(R.Value)
End Sub
but run-time error occured "Type Mismatch" (error code: 13).
但出现运行时错误“类型不匹配”（错误代码：13）。
What's wrong with the code? What is the actual data type of RefersToRange.Value?
代码有什么问题？RefersToRange.Value 的实际数据类型是什么？
The documentation says that RefersToRange returns the Range object, but it seems differ with the Range object returned by ActiveCell, because I've no problem with the following code:
文档说 RefersToRange 返回 Range 对象，但它似乎与 ActiveCell 返回的 Range 对象不同，因为我对以下代码没有问题：
MsgBox CStr(ActiveCell.Value)
I've been using Excel 2003
我一直在使用 Excel 2003
回答by Fink
You Need to iterate through the cells within that range and get the value of each cell separately if the range spans multiple cells. Otherwise it throws an error.
如果范围跨越多个单元格，您需要遍历该范围内的单元格并分别获取每个单元格的值。否则会抛出错误。
Sub Test()
Dim r As Range
Dim cell As Range
Set r = Range("Something")
If r.Cells.Count > 1 Then
For Each cell In r.Cells
MsgBox cell.Value
Next cell
Else
MsgBox r.Value
End If
End Sub
However, you can set the value to all of the cells defined in the range by setting the value of a multi-cell range like this:
但是，您可以通过设置多单元格范围的值来为范围中定义的所有单元格设置值，如下所示：
Sub Test()
Dim r As Range
Set r = Range("Something")
r.Value = "Test"
End Sub
回答by Dick Kusleika
RefersToRange does return a Range object. I assume you're getting your Type Mismatch on the Cstr line. If the range has multiple cells, the Value property returns a Variant array. Do this in the Immediate Window (Control+G in the VBE).
RefersToRange 确实返回一个 Range 对象。我假设你在 Cstr 线上得到了你的类型不匹配。如果区域有多个单元格，则 Value 属性返回一个 Variant 数组。在立即窗口中执行此操作（VBE 中的 Control+G）。
?typename(thisworkbook.Names("SomeRange").RefersTorange)
Range
?typename(thisworkbook.Names("SomeRange").RefersToRange.Value)
Variant()
The CStr function can't handle an array argument and that's why you're getting that error. You can get to a particular cell in the range, like the first cell, like this
CStr 函数无法处理数组参数，这就是您收到该错误的原因。您可以访问范围内的特定单元格，例如第一个单元格，如下所示
ThisWorkbook.Names("SomeRange").RefersToRange(1).Value
Or loop through them as Fink suggests.
或者按照 Fink 的建议循环遍历它们。
回答by Mikeb
You should be able to just supply the name to the Range (or even the Cells) property:
您应该能够为 Range（甚至是 Cells）属性提供名称：
Set R = ThisWorkbook.Range("SomeName")
回答by Javed Iqbal
You can get cell value by its name as follows:-
您可以通过其名称获取单元格值，如下所示：-
Workbook.Range("SomeName").Value
