Excel VBA Cells.Find 失败，除非我使用 Application.Wait。为什么？

Question

我正在开发 VBA 宏，并且遇到了一些非常奇怪的行为。

它可以在 Application.ScreenUpdating = True 下正常工作，甚至在屏幕更新关闭并使用 VBA 调试器单步执行宏时也可以正常工作。

遗憾的是，仅在屏幕更新关闭的情况下运行宏会导致以下代码中的 Find 函数失败:

Application.StatusBar = "Extracting data and updating tables..."
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Workbooks.Open FileName:=Save_folder & "new_data.xls"
Workbooks("new_data.xls").Sheets("data").Range("B9:B39").Copy

Dim tempdate As Date, startCell As Range

tempdate = DateAdd("d", -1, Now)
tempdate = DateSerial(Year(tempdate), Month(tempdate), 1) 'Start of the month
Dim strdate As String
strdate = Format(tempdate, "Short Date")

Set startCell = ThisWorkbook.Sheets("Raw Data").Cells.Find(What:=CDate(strdate), After:=ThisWorkbook.Sheets("Raw Data").Range("A1"), LookIn:=xlFormulas _
 , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

If startCell Is Nothing Then
    MsgBox "Couldn't find a date equal to the start of yesterday's month."
    Exit Sub
Else
    startCell.Offset(0, 1).PasteSpecial xlPasteValues, Transpose:=False
End If

在对 Cells.Find 的调用上方添加这个简短的代码片段可以解决问题:

'Wait one second, to appease the Excel object creation gods
Application.StatusBar = "Wait one second..."
Application.Wait Now + TimeValue("00:00:01")
Application.StatusBar = "Pasting values..."

抛出MsgBox或提示等也可以让Find成功。

我的问题是，为什么我必须等待？

Answer 1

我无法产生这种行为。查看截图

Sub Sample()
    Dim tempdate As Date, startCell As Range
    Dim strdate As String

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    tempdate = DateAdd("d", -1, Now)
    tempdate = DateSerial(Year(tempdate), Month(tempdate), 1) 'Start of the month

    strdate = Format(tempdate, "Short Date")

    Set startCell = ThisWorkbook.Sheets("Raw Data").Cells.Find(What:=CDate(strdate), LookIn:=xlFormulas _
     , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

    If startCell Is Nothing Then
        MsgBox "Couldn't find a date equal to the start of yesterday's month."
    Else
        MsgBox "Found"
    End If

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

快照

更新:

如果您的代码中发生了很多事情，请使用DoEvents，以便代码将控制权传递给操作系统，以便操作系统可以处理其他事件。