Getting a value from a named range in VBA

Question

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

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

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.

Answer 1

Use this

ThisWorkbook.Names("myNamedRange").RefersToRange(1,1)

To get the value from the first cell in the named range "myNamedRange"

With ThisWorkbook.Names you can access all named ranges of all the sheets within the current workbook. With RefersToRange you get a reference to the actual range.

Answer 2

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").Value gets the contents, and Sheets(1).Range("TopLeft").Offset(2,3).Value gets the value from 2 down, 3 across from there.