访问命名范围内的值 [英] Accessing Value in Named Range

问题描述

这是我正在尝试做的非常简化的示例.我有两个命名范围.第一个，燃料"，具有用0.3硬编码的值.第二个帐单"引用工作簿中B2的值，然后将其乘以(1 + fuel).当我在单元格"C2"中引用帐单"时，我得到了正确的输出650.

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.

使用VBA，如何直接获取命名范围"Bill"的输出(在这种情况下为650)，而不必首先在电子表格上引用"Bill"，然后使用Range("C2")来获取值?以下是我尝试过的几件事.

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

推荐答案

Application.Evaluate 是您的朋友.它的主要目的是使您直接在Excel中键入内容时所期望的内容.

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.

这篇关于访问命名范围内的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！