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

Question

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?

Answer 1

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.

Answer 2

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