Accessing Value in Named Range

Question

This is a very simplified example of what I am trying to do. I have two named ranges. The first, "fuel", has a value that is hard-coded with 0.3. The second, "Bill", references the value of B2 in the workbook and then multiples it by (1+fuel). When I reference "Bill" in cell "C2", I get the correct output of 650.

Using VBA, how can I get the output (650 in this case) of the named range "Bill" directly without having to first reference "Bill" on the spreadsheet and then use Range("C2") to get the value? Below are a couple of things I have tried.

Sub named_range_value()

Dim wb As Workbook

Set wb = Workbooks("test")


MsgBox Range("bill") 'Run-time error '1004': Method 'Range' of object '_Global' failed

For Each nr In wb.Names
MsgBox nr    'Loops three times and returns:
            '=#NAME
            '=Sheet1!$B$2*(1+fuel)
            '=0.3
Next

End Sub

Answer 1

Application.Evaluate is your friend. It is mainly designed to get you what you expect when you'd type directly in Excel.

x = Application.Evaluate("Bill")
y = Application.Evaluate(Names("Bill").Value)
Debug.Print x, y

Both work. The first form is short and straight. The second is more explicit and may occasionally be useful for disambiguation.