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

MsgBox wants a string input not a range input. That's the cause of the error. you need to pass the value or a string containing it. — matt_black
– matt_black, Commented Jan 27, 2017 at 12:53

A.S.H · Accepted Answer · 2017-01-20 00:27:47Z

2

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.

answered Jan 20, 2017 at 0:27

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Accessing Value in Named Range

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related