Referencing calculated named ranges in VBA

[Jinxster]

Hello,

I'm trying to use a calculated Named Range in VBA and I found myself in the position that I cannot do that directly. What am I talking about? In the example given in the picture bellow, the "s" Named Range is calculated using the values from the "a" and "b" Named Ranges. You can then use "s" in excel as you would do with any other Named Range and it works perfectly fine. The same is not true for VBA, as it's not pointing to an actual range in the workbook. So, what would normally work, such as:

Range("a")
ActiveWorkbook.Names("a").RefersToRange

It doesn't. I would expect that

ActiveWorkbook.Names("S").Value

to return the value of the Named Range, but it just returns the "Refers To" from the picture bellow. Is this a bug?
Is there a way to use directly in VBA the Named Range "s"? And yes, I do realize that inputting the value into a cell would solve the issue, but it's not an elegant solution.

Thank you.

 

[FormR]

Hi, you can access the named range's value with the evaluate method.

For example:

Msgbox Evaluate("s")

or using shorthand:

Msgbox [s]