Access contents of a named Excel range which does not refer to a cell in VBA

Question

I have created a named range, say "TestRange" in Excel which contains a formula in the "Refers to" box. It could be as simple as multiplying 2 cells together eg Refers to:=Sheet1!G25*Sheet1!G26. I have also tried this for storing a constant and I assume the solution to the issue is the same in that case as well. The idea is for the result not to be in a cell in the worksheet making it easier to hide from the user but nevertheless accessible in the worksheet and VBA.

So far so good, I can access that value in the worksheet so I know the formula is OK but what I also want is to use that value in VBA.

Using

SomeValue=Range("TestRange").Value

does not work although it does work if the formula is in the worksheet and the "Refers to:" part is the cell containing the formula. I have also tried to use

Somevalue=ThisWorkbook.Names("TestRange").RefersTo

without success. What should I be doing or is it not possible?

Answer 1

You have to use Evaluate which will calculate the RefersTo like if in a cell.

Somevalue=Evaluate(ThisWorkbook.Names("TestRange").RefersTo)