Accessing Defined Names in VBA

[karenjoy511]

I would like to access some defined names in my VBA code. These names have a ReferTo value that is a formula. For some the formula evaluates to a single value, like 5005 or "The Client", and for others the formula evaluates to a range. After quite a bit of searching I haven't been able to find an explanation of how to do this (if there is a way). Does anyone know if I can use defined formula names in VBA? If so, how do you access the value the name evaluates to (RefersTo just returns the formula, not the result).

Thanks!

 

[mikerickson]

For ranges you can use syntax like

MsgBox ThisWorkbook.Names("myNamedRange").RefersToRange.Address

For something named values or functions (that return a value rather than a range)

MsgBox Evaluate(ThisWorkbook.Names("myNamedThing").RefersTo)

Be cautious with named formulas that use relative referencing, you may need to control the ActiveCell to get the expected value.