当引用不是命名区域的活动工作表时，Excel VBA求值函数错误

Question

用更好的示例编辑

我正在尝试使用Evaluate函数来评估命名范围的公式引用。但是，在使用Evaluate函数时，如果没有明确说明图纸引用和单元引用，它将假定活动图纸为单元引用。这导致错误的结果

在我的真实项目中，我试图只评估命名范围公式的一部分，所以这会使它变得更加棘手。

举一个我想做的基本例子，假设你在表1单元格A1中有以下公式，其名称为MyCell:

="Don't evaluate this part"&"My Result Is " & A2

如果活动工作表是工作表2，并且您运行以下代码，它将给您错误的结果(这是一个快速而肮脏的例子来说明问题)

Dim s As String
s = Replace(Range("MyCell").Formula, """Don't evaluate this part""&", "")
Debug.Print Evaluate(s)

它没有给我第1张单元格A2中的值，而是给我第2张单元格A2的值。

对此有什么想法吗？

这是我发现的最接近的问题，但这不是我的确切问题(尽管标题相似)，也没有提供解决方案：Excel VBA评估来自另一工作表的公式

Answer 1

您遇到的问题是，Excel在设计时会假设所有不特定的单元格引用都引用了现有的工作表。这就是为什么建议尽可能在所有代码中明确说明工作表的原因。

最干净的方法(通过一些MSDN定义搜索验证)是只显式地声明工作表而不激活它：

Sub test2()
Debug.Print Range("MyCell").Worksheet.Evaluate(Range("MyCell").Formula)
End Sub

或者，此代码会将活动工作表更改为正确的工作表，然后在评估后将其改回在没有情有可原的情况下，不建议像下面的代码那样执行表单激活。甚至在这里都没有

Sub test()
Dim ws As Worksheet
Set ws = ActiveSheet
Dim s As String
s = Replace(Range("MyCell").Formula, """Don't evaluate this part""&", "")
Range("MyCell").Worksheet.Activate  ' Don't remember if .Worksheet or .Parent ??
Debug.Print Evaluate(s)
ws.Activate
End Sub

正如ThunderFrame在评论中指出的那样，重要的是要记住，这段代码假设MyCell是一个简单的单元格引用，正如问题中所述。否则，您将需要使用其他方法来确定目标工作表名称(或硬编码)。

Answer 2

使用Worksheet.Evaluate而不是默认的Application.EEvaluate：正如Mark Balhoff所指出的，它可以控制不合格的引用。但是Worksheet.Evaluate的速度通常也是Application.Evaluate的两倍。有关详细信息，请参阅我的博客文章https://fastexcel.wordpress.com/2011/11/02/evaluate-functions-and-formulas-fun-how-to-make-excels-evaluate-method-twice-as-fast/

Answer 3

您的线路：

Debug.Print Evaluate(Range("MyCell").Formula)

相当于：

Debug.Print Evaluate("=""My Result Is "" & A2")

这就是为什么您可以根据ActiveSheet中A2的值来获得结果。

如果你想检查公式的内容，你可以使用这行：

Debug.Print [MyCell].Formula

如果你想要MyCell相对于Sheet1的值，那么你有两个选项：

1-使用Debug.Print Range("Sheet1!MyCell").Value

2-使用Debug.Print Sheet1.Range("MyCell").Value