停止 VBA Evaluate 两次调用目标函数

Stop VBA Evaluate from calling target function twice

提问by Abiel

I am having trouble getting VBA's Evaluate() function to only execute once; it seems to always run twice. For instance, consider the trivial example below. If we run the RunEval() subroutine, it will call the EvalTest() function twice. This can be seen by the two different random numbers that get printed in the immediate window. The behavior would be the same if we were calling another subroutine with Evaluate instead of a function. Can someone explain how I can get Evaluate to execute the target function once instead of twice? Thank you.

我无法让 VBA 的 Evaluate() 函数只执行一次；它似乎总是运行两次。例如，请考虑下面的简单示例。如果我们运行 RunEval() 子例程，它将调用 EvalTest() 函数两次。这可以通过立即窗口中打印的两个不同的随机数看出。如果我们使用 Evaluate 而不是函数调用另一个子程序，行为将是相同的。有人可以解释我如何让 Evaluate 执行目标函数一次而不是两次吗？谢谢你。

Sub RunEval()
    Evaluate "EvalTest()"
End Sub

Public Function EvalTest()
    Debug.Print Rnd()
End Function

回答by Charles Williams

This bug only seems to happen with UDFs, not with built-in functions. You can bypass it by adding an expression:

这个错误似乎只发生在 UDF 中，而不是内置函数中。您可以通过添加表达式来绕过它：

Sub RunEval()
    ActiveSheet.Evaluate "0+EvalTest()"
End Sub

But there are also a number of other limitations with Evaluate, documented here http://www.decisionmodels.com/calcsecretsh.htm

但是 Evaluate 也有许多其他限制，记录在此处 http://www.decisionmodels.com/calcsecretsh.htm

回答by jtolle

I don't know of a way to stop it, but you can at least recognize when it is happening most of the time. That could be useful if your computation is time consuming or has side effects that you don't want to have happen twice and you want to short circuit it.

我不知道有什么方法可以阻止它，但是您至少可以识别出大多数时候它何时发生。如果您的计算很耗时或具有您不想发生两次的副作用并且您想将其短路，那么这可能很有用。

(EDIT: Charles Williams actually has an answer to your specific quesion. My answer could still be useful when you don't know what data type you might be getting back, or when you expect to get something like an array or a range.)

（编辑：查尔斯·威廉姆斯实际上对您的特定问题有一个答案。当您不知道可能会返回什么数据类型时，或者当您希望获得数组或范围之类的东西时，我的答案仍然有用。）

If you use the Application.Callerproperty within a routine called as a result of a call to Application.Evaluate, you'll see that one of the calls appears to come from the upper left cell of of the actual range the Evaluatecall is made from, and one from cell $A$1 of the sheet that range is on. If you call Application.Evaluatefrom the immediate window, like you would call your example Sub, one call appears to come from the upper left cell of the currently selected range and one from cell $A$1 of the current worksheet. I'm pretty sure it's the first call that's the $A$1 in both cases. (I'd test that if it matters.)

如果您Application.Caller在因调用而调用的例程中使用该属性Application.Evaluate，您将看到其中一个调用似乎来自发出调用的实际范围的左上角单元格Evaluate，另一个来自单元格 $该范围所在的表中的 1 澳元。如果您Application.Evaluate从即时窗口调用，就像调用示例 Sub 一样，一个调用似乎来自当前选定范围的左上角单元格，另一个调用来自当前工作表的单元格 $A$1。我很确定在这两种情况下都是第一个调用 $A$1。（我会测试它是否重要。）

However, only one value will ever be returned from Application.Evaluate. I'm pretty sure it's the one from the second eval. (I'd test that too.)

但是，从Application.Evaluate. 我很确定它是第二个评估中的那个。（我也会测试一下。）

Obviously, this won't work with calls made from the actual cell $A$1.

显然，这不适用于从实际单元格 $A$1 发出的调用。

(As for me, I would love to know whythe double evaluation happens. I would also love to know why the evaluator is exposed at all. Anyone?)

（至于我，我很想知道为什么会发生双重评估。我也很想知道为什么评估者会被曝光。有人吗？）

EDIT: I asked on StackOverflow here: Why is Excel's 'Evaluate' method a general expression evaluator?

编辑：我在这里询问 StackOverflow：为什么 Excel 的“评估”方法是通用表达式评估器？

I hope this helps, although it doesn't directly answer your question.

我希望这会有所帮助，尽管它不能直接回答您的问题。

回答by Kevin Brock

I did a quick search and found that others have reported similar behavior and other odd bugs with Application.Evaluate(see KB823604and this). This is probably not high on Microsoft's list to fix since it has been seen at least since Excel 2002. That knowledge base article gives a workaround that may work in your case too - put the expression to evaluate in a worksheet and then get the value from that, like this:

我进行了快速搜索，发现其他人报告了类似的行为和其他奇怪的错误Application.Evaluate（参见KB823604和this）。这在 Microsoft 的修复列表中可能并不高，因为它至少自 Excel 2002 以来就已经出现过。该知识库文章提供了一种可能适用于您的情况的解决方法 - 将表达式放在工作表中进行评估，然后从中获取值那个，像这样：

Sub RunEval()
    Dim d As Double

    Range("A1").Formula = "=EvalTest()"
    d = Range("A1").Value
    Range("A1").Clear
    Debug.Print d
End Sub

Public Function EvalTest() As Double
    Dim d As Double
    d = Rnd()
    Debug.Print d
    EvalTest = d + 1
End Function

I modified your example to also return the random value from the function. This prints the value a second time but with the one added so the second print comes from the first subroutine. You could write a support routine to do this for any expression.

我修改了您的示例以还从函数返回随机值。这将第二次打印该值，但添加了一个，因此第二次打印来自第一个子程序。您可以编写一个支持例程来为任何表达式执行此操作。

回答by azizi

I face the same problem, after investigation i found the function called twice because i have drop down list and the value used in a user defined function.

我面临同样的问题，经过调查，我发现该函数被调用了两次，因为我有下拉列表和用户定义函数中使用的值。

working around by the code bellow, put the code in ThisWorkbook

通过下面的代码解决，将代码放在 ThisWorkbook 中

Private Sub Workbook_Open() 'set the calculation to manual to stop calculation when dropdownlist updeated and again calculate for the UDF

Private Sub Workbook_Open() '将计算设置为手动以在下拉列表更新并再次计算 UDF 时停止计算

     Application.Calculation = xlCalculationManual

End Sub

结束子

Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Source As Range)

Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Source As Range)

'calculte only when the sheet changed

'仅当工作表改变时才计算

   Calculate

End Sub

结束子

回答by stenci

It looks like Application.Evaluateevaluates always twice, while ActiveSheet.Evaluateevaluates once if it is an expression.

看起来Application.Evaluate总是计算两次，而ActiveSheet.Evaluate如果它是一个表达式，则计算一次。

When the object is not specified Evaluateis equivalent to Application.Evaluate.

未指定对象时Evaluate等效于Application.Evaluate.

Typing [expression]is equivalent to Application.Evaluate("expression").

打字[expression]相当于Application.Evaluate("expression")。

So the solution is to add ActiveSheetand to make that an expression by adding zero:

因此，解决方案是ActiveSheet通过添加零来添加并使其成为表达式：

ActiveSheet.Evaluate("EvalTest+0")

回答by Jonathan Mori

After seeing there is no proper way to work around this problem, I solved it by the following:

在看到没有适当的方法来解决这个问题后，我通过以下方式解决了它：

Dim RunEval as boolean

Sub RunEval()
    RunEval = True
    Evaluate "EvalTest()"
End Sub

Public Function EvalTest()
    if RunEval = true then
     Debug.Print Rnd()
     RunEval = False
    end if
End Function

problem solved everyone.

问题解决了大家。