Why does VBA Find loop fail when called from Evaluate?

Question

I'm having problems starting a search loop inside a subprogram when calling a subprogram using the Application.Evaluate or ActiveSheet.Evaluate method. For example, in the code below, I define a FindSub () routine that looks for a sheet for the string "xxx". Regular CallSub () calls the FindSub () routine using the standard Call and Evaluate statements.

When I launch Call FindSub, everything will work as expected: each corresponding address will be printed in the nearest window, and we will receive the final message "Finished" when the code is executed. However, when I do Application.Evaluate "FindSub ()", only the address of the first match is displayed, and we never get to the message "Finished". In other words, an error occurs after the Cells.FindNext line, because the loop is trying to evaluate whether to continue, and the program stops without any runtime error.

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

Note. In this example, I clearly do not need to use Evaluate. This version is simplified to just focus on the specific problem that I am experiencing 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

Answer 1

The reason, in all likelihood, is that Evaluate sees your function as UDF - as if it were being called from a worksheet formula. UDFs have strict restrictions on what they can do - in particular, no settings or calling other functions - and I think that something here violated these restrictions, although I can not isolate exactly what I did here.

UDF , VB. ( Excel, VB)

UDF . http://support.microsoft.com/kb/170787.

: , , , Evaluate. :

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

:

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

, , .

• . Evaluate, , Excel . Evaluate , , Evaluate.
• Find Next . , Evaluate , , undefined . Evaluate , . - Find Next , , , Evaluate .
• . Loop Until Or. , rngFoundCell - Nothing, ; VBA , rngFoundCell.Address . UDF (.. ). "" Evaluate.

Answer 2

:

Call FindSub
Call Application.Run("FindSub") 

.Evaluate .

Call Application.Run("FindSub()") ( parens), , ( "" ).

Application.Evaluate "FindSub"