CODEWITHSUNDEEP
vbaexcelreference
speendo
speendo

Reputation: 13345

access value (content) of a named cell in Excel VBA

This

Dim WB As Workbook
Set WB = Workbooks.Open("foo\bar.xlsx", , True)
Debug.Print (WB.Names("named_cell").Value)
WB.Close (False)

returns

='Sheet'!$C$377

instead of the value in the cell C377.

How can I receive the value of the cell instead?

Upvotes: 5

Views: 14561

Answers (3)

Axel Richter
Axel Richter

Reputation: 61975

If WB.Names("named_cell") returns a Name object, then this object has a RefersToRange property. That returns the Range object referred to by a Name object. See https://msdn.microsoft.com/en-us/library/office/ff834918.aspx.

So

Debug.Print WB.Names("named_cell").RefersToRange.Value

See also Malfunction of Excel-VBA own function when open other excel-file.

Upvotes: 5

Gary's Student
Gary's Student

Reputation: 96791

Once the Named Range has been established, Excel knows its Parent, so:

Sub dural()
    Dim r As Range
    Set r = Range("named_cell")
    MsgBox r.Parent.Name & vbCrLf & r.Address(0, 0) & vbCrLf & r.Value
End Sub

is one way to get info about this range:

enter image description here

This uses the properties of the Range Object rather than the Workbook Object.

Upvotes: 2

SierraOscar
SierraOscar

Reputation: 17647

Couple of ways:

Debug.Print Range("Named_Range").Value
Debug.Print [Named_Range].Value '// This uses the evaluate method

You don't need to qualify a named range with a sheet object if it's in the active workbook (which it should be as it's the last workbook you opened).

Upvotes: 5

Related Questions