Why does VBA Find loop fail when called from Evaluate?

Posted by Abiel on Stack Overflow See other posts from Stack Overflow or by Abiel
Published on 2010-04-19T14:19:09Z Indexed on 2010/04/19 15:03 UTC
Read the original article Hit count: 438

Filed under:
|

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.

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.

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.

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

© Stack Overflow or respective owner

Related posts about vba

Related posts about excel