Why does VBA Find loop fail when called from Evalu

Question

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

Answer 1

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.

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)

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

EDIT: 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

So good. But:

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. 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.

Answer 2

The following should work:

Call FindSub
Call Application.Run("FindSub") 

For me .Evaluate fails & does nothing.

If I use Call Application.Run("FindSub()") (with parens) I see the same behaviour as you do (a "partial" second call).

You could also try Application.Evaluate "FindSub"