Why does VBA Find loop fail when called from Evaluate?
I am having some problems running a find loop inside of a subroutine when the r开发者_运维百科outine 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
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.
- 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.
- 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.
- Your bug. On the
Loop Until
line, you process an Or test. The trouble is, ifrngFoundCell
isNothing
, the second test will throw an error; VBA is trying to process the full expression andrngFoundCell.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.
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"
精彩评论