使用 VBA 引用命名的 Excel 变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15663018/
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
Reference a named Excel variable using VBA
提问by Rick
Situation: I'm using Excel 10. I have a named variable that uses a formula to compute it's value. This is a named variable, not a named range (in the 'Name Manager' the name "MaxDate" refers to "=MAX(Sheet1!B:B)" where column B is a list of dates). The computed value does not appear in any cells by itself, but rather is used in various formulas within the spreadsheet.
情况：我使用的是 Excel 10。我有一个命名变量，它使用公式来计算它的值。这是一个命名变量，而不是命名范围（在“名称管理器”中，名称“MaxDate”指的是“=MAX(Sheet1!B:B)”，其中 B 列是日期列表）。计算出的值本身不会出现在任何单元格中，而是用于电子表格中的各种公式中。
My problem: How can I reference this named variable in VBA code? Using range("MaxDate").value does not work. I know I can simply put the value into a cell and reference the cell, but I'd like to find a way to reference the variable directly. Any ideas?Thanks.
我的问题：如何在 VBA 代码中引用这个命名变量？使用 range("MaxDate").value 不起作用。我知道我可以简单地将值放入一个单元格并引用该单元格，但我想找到一种直接引用变量的方法。有什么想法吗？谢谢。
回答by David Zemens
Here's some examples of working with your named variable:
以下是使用命名变量的一些示例：
Sub TestNamedVariable()
Dim varFormula As String
Dim varValue As Variant
'This will print the formula if you need it:
varFormula = ActiveWorkbook.Names("MaxDate")
'This will evaluate the named variable/formula
varValue = Application.Evaluate("MaxDate")
End Sub
回答by Octopus
For a given workbook with a Named variable "MyVar", valued "=MAX(Sheet1!B:B)".
Try the following code:
对于具有命名变量“MyVar”的给定工作簿，值为“=MAX(Sheet1!B:B)”。
试试下面的代码：
Evaluate(ActiveWorkbook.Names("MyVar").RefersTo)
Replace the ActiveWorkbook with the object you're referring if you need to.
如果需要，请将 ActiveWorkbook 替换为您所指的对象。
