CODEWITHSUNDEEP
excelvba
rwilson
rwilson

Reputation: 2135

Accessing Value in Named Range

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.

enter image description here

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

Upvotes: 1

Views: 4221

Answers (1)

A.S.H
A.S.H

Reputation: 29332

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.

Upvotes: 2

Related Questions