为什么从Evaluate调用VBA Find循环失败？ [英] Why does VBA Find loop fail when called from Evaluate?

问题描述

当使用Application.Evaluate或ActiveSheet.Evaluate方法调用该例程时，我在子例程中运行find循环有一些问题。例如，在下面的代码中，我定义了一个子例程FindSub（），它在表格中搜索字符串xxx。 CallSub（）函数使用标准的Call语句和Evaluate调用FindSub（）例程。

I am having some problems running a find loop inside of a subroutine when the routine is called using the Application.Evaluate or ActiveSheet.Evaluate method. For example, in the code below, I define a subroutine FindSub() which searches the sheet for a string "xxx". The routine CallSub() calls the FindSub() routine using both a standard Call statement and Evaluate.

当我运行Call FindSub时，一切都将按预期工作：每个匹配的地址被打印到立即窗口，当代码完成时，我们得到一个最后的消息完成。但是，当我执行Application.EvaluateFindSub（）时，只有第一个匹配的地址被打印出来，我们从来没有达到完成消息。换句话说，在Cells.FindNext行之后遇到一个错误，因为循环尝试评估它是否应该继续，并且程序执行停止，而不会打印任何运行时错误。

When I run Call FindSub, everything will work as expected: each matching address gets printed out to the immediate window and we get a final message "Finished up" when the code is done. However, when I do Application.Evaluate "FindSub()", only the address of the first match gets printed out, and we never reach the "Finished up" message. In other words, an error is encountered after the Cells.FindNext line as the loop tries to evaluate whether it should continue, and program execution stops without any runtime error being printed.

我会期望在这种情况下调用FindSub和Application.EvaluateFindSub（）得到相同的结果。有人可以解释为什么不这样做，如果可能的话，可以解决这个问题吗？谢谢。

I would expect both Call FindSub and Application.Evaluate "FindSub()" to yield the same results in this case. Can someone explain why they do not, and if possible, a way to fix this? Thanks.

注意：在这个例子中，我显然不需要使用Evaluate。这个版本是简化的，只是关注我在一个更复杂的情况下遇到的特殊问题。

Note: In this example I obviously do not need to use Evaluate. This version is simplified to just focus on the particular problem I am having in a more complex situation.

Sub CallSub()
    Call FindSub
    Application.Evaluate "FindSub()"
End Sub

Sub FindSub()
    Dim rngFoundCell As Range
    Dim rngFirstCell As Range

    Set rngFoundCell = Cells.Find(What:="xxx", after:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

    If Not rngFoundCell Is Nothing Then
        Set rngFirstCell = rngFoundCell
        Do
            Debug.Print rngFoundCell.Address
            Set rngFoundCell = Cells.FindNext(after:=rngFoundCell)
        Loop Until (rngFoundCell Is Nothing) Or (rngFoundCell.Address = rngFirstCell.Address)
    End If

    Debug.Print "Finished up"
End Sub

推荐答案

原因是最有可能的评估是看到你的函数的UDF - 因为它正从一个工作表中公式调用。 UDF对他们可以做的事情有很大的限制 - 特别是没有设置属性或调用其他功能 - 我想象这里的东西已经陷入了这些限制的错误，尽管我无法确切地确定这里的功能。

The cause is most likely that Evaluate is seeing your function as a UDF - as if it was being called from a worksheet formula. UDFs have heavy restrictions on what they can do - in particular, no setting properties or calling other functions - and I imagine something here has fallen foul of these restrictions, although I can't isolate exactly what's done it here.

在UDF内，错误被无声地吞噬，因为工作表公式不允许抛出VB错误。 （如果公式错误不断地导致VB对话框，则会破坏Excel用户界面）

Inside a UDF, errors are swallowed silently because a sheet formula isn't allowed to throw VB errors. (It would disrupt the Excel user interface if a formula error threw VB dialogs constantly)

请参阅 http://support.microsoft.com/kb/170787 了解UDF限制的详细信息。

See http://support.microsoft.com/kb/170787 for details of UDF restrictions.

编辑：好的，这里有一些关于你的问题的澄清，我知道你的代码在评估过程中默认错误。使用以下代码：

Okay, here's some clarification on your problem and I know where your code is silently erroring during the Evaluate. Using this code:

Sub FindSub()
    Dim rngFoundCell As Range
    Dim rngFirstCell As Range

    Set rngFoundCell = Cells.Find(What:="xxx", after:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

    If Not rngFoundCell Is Nothing Then
        Set rngFirstCell = rngFoundCell
        Do
            Debug.Print "FOUND: " & rngFoundCell.Address
            Set rngFoundCell = Cells.FindNext(after:=rngFoundCell)
            Debug.Print "FIND NEXT: " & IIf(rngFoundCell Is Nothing, " NOTHING", " SOMETHING")
        Loop Until (rngFoundCell Is Nothing) Or (rngFoundCell.Address = rngFirstCell.Address)
        Debug.Print "ESCAPED LOOP"
    End If

    Debug.Print "Finished up"
End Sub

我在即时窗口中获得以下输出：

I get the following output in the immediate window:

findsub
FOUND: $G$6
FIND NEXT:  SOMETHING
FOUND: $D$11
FIND NEXT:  SOMETHING
ESCAPED LOOP
Finished up

很好。但是：

callsub
FOUND: $G$6
FIND NEXT:  SOMETHING
FOUND: $D$11
FIND NEXT:  SOMETHING
ESCAPED LOOP
Finished up
FOUND: $G$6
FIND NEXT:  NOTHING

这里有三件事要注意，至少在我运行它时。

There are three things of note here, at least when I run it.

1. 该函数被调用两次。这是Evaluate的一个已知问题，它与Excel如何处理其在计划上的计算有关。这就是为什么Evaluate不应该用于记录数据的函数 - 因为它可以在单个Evaluate中被多次调用。


2. 在第二个循环中，Find Next无法找到另一个单元格。这是一个谜，但评估不应该真的用于运行在表单上运行的功能，所以在某种程度上，这是未定义的行为，不能被认为是一个错误。评估是为了运行公式，其中所有单元格引用在公式中显式映射。我自己的理论是Find Next不起作用，因为您尝试使用不是活动单元格的单元格引用，Evaluate正在试图消除这种非法活动。


3. 你的错误在循环直到行，您处理或测试。麻烦的是，如果 rngFoundCell 是 Nothing ，则第二个测试将抛出错误; VBA正在尝试处理完整的表达式，在这种情况下无法评估 rngFoundCell.Address 。当作为UDF运行时（即在Evaluate中），该代码将立即退出而不出现错误对话框。这就是为什么你没有看到评估中的完成。

1. The function is called twice. This is a known issue with Evaluate, it's to do with how Excel handles its calculations on the sheet. This is why Evaluate should never be used on functions which record data - because it can be called multiple times in a single Evaluate.
2. On the second loop, Find Next fails to find another cell. This is a mystery, but Evaluate shouldn't really be used to run functions which go running around the sheet, so in a way, this is undefined behaviour and can't really be considered a bug. Evaluate is meant to run a formula, where all the cell references are mapped out explicitly in the formula. My own theory is Find Next does not work because you're trying to use a cell reference which isn't the active cell, and Evaluate is trying to kill off that sort of illegal activity.
3. Your bug. On the Loop Until line, you process an Or test. The trouble is, if rngFoundCell is Nothing, the second test will throw an error; VBA is trying to process the full expression and rngFoundCell.Address cannot be evaluated in this case. The code will exit immediately without error dialog when running as a UDF (i.e. within Evaluate). That's why you don't see the "Finished up" inside Evaluate.

这篇关于为什么从Evaluate调用VBA Find循环失败？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！