vba 如何使用vba for excel获取另一个工作簿中定义名称的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11561421/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to get the value of a defined name in another workbook, using vba for excel
提问by neXus
I have several workbooks containing calculations and I am making an "automatic overview" combining the data in all those sheets. To find the data I use named ranges. These work fine.
However there is one defined name that does not refer to a range. It is a formula.
我有几个包含计算的工作簿，我正在制作一个“自动概览”，将所有这些工作表中的数据结合起来。为了查找数据，我使用命名范围。这些工作正常。
但是，有一个定义的名称不涉及范围。它是一个公式。
What I want is to access the result from that formula (in an opencalculations book) from my overview book.
我想要的是从我的概述书中访问该公式（在打开的计算书中）的结果。
Obviously wb.Names("myNamedFormula").RefersToRangedoes not work as the name does not refer to a range.wb.Names("myNamedFormula").Valuegives me the formula, but not the result. None of the name's members gets the result.
I tried evaluating the formula using Evaluate(...)but this doesn't work as it should be evaluated in the other workbook.
显然wb.Names("myNamedFormula").RefersToRange不起作用，因为名称不引用范围。wb.Names("myNamedFormula").Value给我公式，但不是结果。名称的成员都没有得到结果。
我尝试使用评估公式，Evaluate(...)但这不起作用，因为它应该在其他工作簿中评估。
Does anyone know how to get the result?
有谁知道如何得到结果？
Thanks,
谢谢，
ps: I know one possibility is to make the name refer to a range and then do the calculation there, but I don't want that.
ps：我知道一种可能性是让名称引用一个范围，然后在那里进行计算，但我不想要那样。
回答by Charles Williams
I think you will have to use Worksheet.Evaluate rather than Application.evaluate (note that all references in the Named Formula should be fully qualified absolute references). Try this syntax for Evaluate
我认为您将不得不使用 Worksheet.Evaluate 而不是 Application.evaluate （注意命名公式中的所有引用都应该是完全限定的绝对引用）。尝试使用此语法进行评估
ansa = Workbooks("Book1.xlsb").Worksheets(1).Evaluate(Workbooks("Book1.xlsb").Names("AddNumbers").RefersTo)
回答by chris neilsen
This is a bit of a cludge, but:
这有点混乱，但是：
Add a formual to a cell in the calculation wb =MyNamedFormula(Lets assume as an example cell A1 on Sheet1)
将公式添加到计算 wb 中的单元格=MyNamedFormula（假设以 Sheet1 上的单元格 A1 为例）
Add a Name in calculation wb PointsToMyNamedFormulathat refers to Sheet1!A1
在计算 wbPointsToMyNamedFormula中添加一个名称，指的是Sheet1!A1
Then you can reference wb.Names("PointsToMyNamedFormula").RefersToRange.Value1
然后你可以参考 wb.Names("PointsToMyNamedFormula").RefersToRange.Value1
