VBA: How to display a cell value by its defined name?

Question

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

What's wrong with the code? What is the actual data type of 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:

  MsgBox CStr(ActiveCell.Value)

I've been using Excel 2003

Answer 1

You should be able to just supply the name to the Range (or even the Cells) property:

Set R = ThisWorkbook.Range("SomeName")

Answer 2

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

Answer 3

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

?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

ThisWorkbook.Names("SomeRange").RefersToRange(1).Value

Or loop through them as Fink suggests.