使用 Wait method :

Application.Wait Now + #0:00:01#

或(适用于Excel 2010及更高版本):

Application.Wait Now + #12:00:01 AM#

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

或者,对于64位系统,请使用:

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

在宏中这样调用它:

Sub Macro1()
'
' Macro1 Macro
'
Do
    Calculate
    Sleep 1000   ' delay 1 second

Loop
End Sub

而不是使用:

Application.Wait(Now + #0:00:01#)

我更喜欢:

Application.Wait(Now + TimeValue("00:00:01"))

因为之后读起来容易多了。

sub whatever()
Dim time1, time2

time1 = Now
time2 = Now + TimeValue("0:00:01")
    Do Until time1 >= time2
        DoEvents
        time1 = Now()
    Loop

End sub

kernel32.dll中的Sleep声明在64位Excel中不起作用。这将有点笼统:

#If VBA7 Then
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

暂停VBA执行的完整指南

背景信息和说明

所有Microsoft Office应用程序都在与主用户界面相同的线程中运行VBA代码。这意味着,只要VBA代码不调用 DoEvents Sleep API函数。曾经

Application.Wait 除了应用程序不会显示为 not responding

DoEvents

这就是为什么让VBA暂停执行的最佳方法是结合这两种方法,使用 DoEvents 保持反应灵敏 以避免最大的CPU使用率。下面介绍了这一点的实现。

通用解决方案

WaitSeconds Sub 这将暂停执行给定的秒数,同时避免所有上述问题。 它可以这样使用:

Sub UsageExample()
    WaitSeconds 3.5
End Sub

这将暂停宏3.5秒,而不会冻结应用程序或导致CPU过度使用。为此,只需将以下代码复制到任何标准代码模块的顶部即可。

#If Mac Then
    #If VBA7 Then
        Private Declare PtrSafe Sub USleep Lib "/usr/lib/libc.dylib" Alias "usleep" (ByVal dwMicroseconds As Long)
    #Else
        Private Declare Sub USleep Lib "/usr/lib/libc.dylib" Alias "usleep" (ByVal dwMicroseconds As Long)
    #End If
#Else
    #If VBA7 Then
        Private Declare PtrSafe Sub MSleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)
    #Else
        Private Declare Sub MSleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)
    #End If
#End If

'Sub providing a Sleep API consistent with Windows on Mac (argument in ms)
'Authors: Guido Witt-Dörring, https://stackoverflow.com/a/74262120/12287457
'         Cristian Buse,      https://stackoverflow.com/a/71176040/12287457
Public Sub Sleep(ByVal dwMilliseconds As Long)
    #If Mac Then 'To avoid overflow issues for inputs > &HFFFFFFFF / 1000:
        Do While dwMilliseconds And &H80000000
            USleep &HFFFFFED8
            If dwMilliseconds < (&H418937 Or &H80000000) Then
                dwMilliseconds = &H7FBE76C9 + (dwMilliseconds - &H80000000)
            Else: dwMilliseconds = dwMilliseconds - &H418937: End If
        Loop
        Do While dwMilliseconds > &H418937
            USleep &HFFFFFED8: dwMilliseconds = dwMilliseconds - &H418937
        Loop
        If dwMilliseconds > &H20C49B Then
            USleep (dwMilliseconds * 500& Or &H80000000) * 2&
        Else: USleep dwMilliseconds * 1000&: End If
    #Else
        MSleep dwMilliseconds
    #End If
End Sub

'Sub pausing code execution without freezing the app or causing high CPU usage
'Author: Guido Witt-Dörring, https://stackoverflow.com/a/74387976/12287457
Public Sub WaitSeconds(ByVal seconds As Single)
    Dim currTime As Single:  currTime = Timer()
    Dim endTime As Single:   endTime = currTime + seconds
    Dim cacheTime As Single: cacheTime = currTime
    Do While currTime < endTime
        Sleep 15: DoEvents: currTime = Timer() 'Timer function resets at 00:00!
        If currTime < cacheTime Then endTime = endTime - 86400! '<- sec per day
        cacheTime = currTime
    Loop
End Sub

_{here 和 here .}

如果应用程序冻结不是问题,例如非常短的延迟或不希望用户交互,最好的解决方案是调用 Public 将其参数视为毫秒。

'Does freeze application
Sub UsageExample()
    Sleep 3.5 * 1000
End Sub

重要提示:

1. Timer() 此解决方案中使用的功能在Windows上更好,但是 documentation 0.1 WaitSeconds 1 1.015 ± 0.02

2. Application.OnTime (见下一节)

暂停VBA执行的替代方案和OP的更好解决方案

XY-Problem .

实际上,没有必要让VBA代码在后台不间断地运行,以便每秒重新计算工作簿。这是一个典型的任务示例,也可以通过以下方式实现 .

一份详细的指南,包括一个复制粘贴解决方案,用于重新计算任何 Range 在任何所需的时间间隔 ≥ 1s 可用的 here .

使用的最大优势

此线程中所有其他解决方案的荟萃分析

:

2. 它们通过调用导致CPU使用率过高(在调用应用程序的线程上为100%) DoEvents

此外,许多提出的解决方案还有其他问题:

3. 有些只在Windows上工作

4. 有些只能在Excel中工作

6. 有些还有其他问题,甚至是bug

概述

其他问题

Public Sub Pause(sngSecs As Single)
    Dim sngEnd As Single
    sngEnd = Timer + sngSecs
    While Timer < sngEnd
        DoEvents
    Wend
End Sub

Public Sub TestPause()
    Pause 1
    MsgBox "done"
End Sub

大多数提出的解决方案都使用Application。等待,它不考虑自当前秒计数开始以来已经过去的时间(毫秒),因此 .

'You can use integer (1 for 1 second) or single (1.5 for 1 and a half second)
Public Sub Sleep(vSeconds As Variant)
    Dim t0 As Single, t1 As Single
    t0 = Timer
    Do
        t1 = Timer
        If t1 < t0 Then t1 = t1 + 86400 'Timer overflows at midnight
        DoEvents    'optional, to avoid excel freeze while sleeping
    Loop Until t1 - t0 >= vSeconds
End Sub

使用此功能测试任何睡眠功能: (打开调试即时窗口:CTRL+G)

Sub testSleep()
    t0 = Timer
    Debug.Print "Time before sleep:"; t0   'Timer format is in seconds since midnight

    Sleep (1.5)

    Debug.Print "Time after sleep:"; Timer
    Debug.Print "Slept for:"; Timer - t0; "seconds"

End Sub

等待和睡眠功能会锁定Excel,在延迟结束之前,您无法执行任何其他操作。另一方面,循环延迟并不能给你一个确切的等待时间。

所以,我做了一个变通方法,将这两个概念结合起来。它一直循环到你想要的时间。

Private Sub Waste10Sec()
   target = (Now + TimeValue("0:00:10"))
   Do
       DoEvents 'keeps excel running other stuff
   Loop Until Now >= target
End Sub

你只需要拨打Waste10Sec,在那里你需要延迟

以下是睡眠的替代方案:

Sub TDelay(delay As Long)
Dim n As Long
For n = 1 To delay
DoEvents
Next n
End Sub

Sub SpinFocus()
Dim i As Long
For i = 1 To 3   '3 blinks
Worksheets(2).Shapes("SpinGlow").ZOrder (msoBringToFront)
TDelay (10000)   'this makes the glow stay lit longer than not, looks nice.
Worksheets(2).Shapes("SpinGlow").ZOrder (msoSendBackward)
TDelay (100)
Next i
End Sub

Application.Wait Second(Now) + 1

Function Delay(ByVal T As Integer)
    'Function can be used to introduce a delay of up to 99 seconds
    'Call Function ex:  Delay 2 {introduces a 2 second delay before execution of code resumes}
        strT = Mid((100 + T), 2, 2)
            strSecsDelay = "00:00:" & strT
    Application.Wait (Now + TimeValue(strSecsDelay))
End Function

我通常使用 计时器 函数暂停应用程序。将此代码插入您的

T0 = Timer
Do
    Delay = Timer - T0
Loop Until Delay >= 1 'Change this value to pause time for a certain amount of seconds