当前位置：文江博客话题详情

VBA 中是否有相当于 Thread.Sleep() 的函数

发布于 2024-07-12 10:53:19 字数 45 浏览 6 评论 0原文

Access VBA 中是否有与 Thread.Sleep() 等效的函数？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

烟若柳尘 2024-07-19 10:53:19

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

使用以下语法调用 Sleep 函数：

Sub Sleep()
Sleep 1000 'Implements a 1 second delay
End Sub

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

Use the following syntax to call the Sleep function:

Sub Sleep()
Sleep 1000 'Implements a 1 second delay
End Sub

回复收藏 0 原文

野の 2024-07-19 10:53:19

另一种不使用 kernel32 的方法：

Dim started As Single: started = Timer

Do: DoEvents: Loop Until Timer - started >= 1

Another way without using kernel32:

Dim started As Single: started = Timer

Do: DoEvents: Loop Until Timer - started >= 1

回复收藏 0 原文

寒尘 2024-07-19 10:53:19

使 Excel 等待的所有其余方法都会导致 Excel 完全无响应。让 Excel 等待同时确保 UI 响应的解决方案是调用此 wait Sub 并指定等待的秒数。

    Sub Wait(seconds As Integer)
      Dim now As Long
      now = Timer()
      Do
          DoEvents
      Loop While (Timer < now + seconds)
    End Sub

All of the rest of the methods to make Excel wait result in Excel becoming completely unresponsive. The solution to make Excel wait while ensuring a responsive UI is to call this wait Sub with the number of seconds to wait.

    Sub Wait(seconds As Integer)
      Dim now As Long
      now = Timer()
      Do
          DoEvents
      Loop While (Timer < now + seconds)
    End Sub

回复收藏 0 原文

落叶缤纷 2024-07-19 10:53:19

需要进行一些修改才能使代码正常工作。
下面的代码是更正后的版本。

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

Sub SleepVBA() 
Sleep 1000 'Implements a 1 second delay 
End Sub

A couple of amendments are required to get the code to work.
The code below is the corrected version.

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

Sub SleepVBA() 
Sleep 1000 'Implements a 1 second delay 
End Sub

回复收藏 0 原文

残龙傲雪 2024-07-19 10:53:19

我在 Excel 中使用它，效果很好：

Application.Wait DateAdd("s", 1, Now())

DateAdd() 是一个设置时间的函数，相对于 Now() （在本例中 - 您可以使用其他值作为参数），"s" 是时间度量（在本例中为秒），增量为 1。因此，在这里，函数调用告诉应用程序等待 1 秒。

另请参阅了解更多信息有关 DateAdd 函数使用的详细信息。

I use this in Excel and it works great:

Application.Wait DateAdd("s", 1, Now())

DateAdd() is a function that set a time, relative to Now() (in this case - you can use other values as your argument), "s" is the time measure (seconds in this case), and the increment is 1. So here, the function call is telling the application to wait 1 second.

回复收藏 0 原文

各自安好 2024-07-19 10:53:19

如果您使用Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)，您可能会在对象模块中收到此错误。

如果是这样，您可以将其声明为私有：

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

回复收藏 0 原文

梦里兽 2024-07-19 10:53:19

可以使用 Access VBA 中的 Excel Wait() 过程。

第一步是确保从您的项目中引用 Excel 库。

完成后，以下代码将等待十秒钟：

Call Excel.Application.Wait(Time:=DateAdd("s",10,Now()))

It is possible to use the Excel Wait() procedure from Access VBA.

The first step is to ensure that the Excel library is referenced from your project.

When that's done the following code will work to wait for ten seconds :

Call Excel.Application.Wait(Time:=DateAdd("s",10,Now()))

回复收藏 0 原文

星 2024-07-19 10:53:19

跨平台解决方案

接受的答案将不适用于 64 位 VBA。另外，它无法在 Mac 上运行。

感谢 @Cristian Buse 指出Mac 兼容性请见评论！

要实现完全的 Mac 兼容性，需要导入用于暂停线程执行的平台相关库函数，即 Mac 上的 usleep 和 睡眠（在 Windows 上）。由于 usleep 的参数以微秒为单位，而 Sleep 使用毫秒，因此有必要声明一个自定义的 Sub 来处理转换。

导入库函数并声明 Sub 可以按如下所示完成。

这个解决方案是我和 Cristian Buse 合作的结果，改编自他的原始解决方案，避免整数溢出并允许睡眠时间超过&HFFFFFFFF微秒（ Mac 上大约 71 分钟：

#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 'Windows
        MSleep dwMilliseconds
    #End If
End Sub

现在 Sleep 将在 Windows 和 Mac 以及 32 位和 64 位环境中可用。

可以这样调用：

Sub ExampleSleepCall()
    Sleep 1000 'Suspends thread execution for 1 second
    'Calling application will freeze completely for that amount of time!
    'If this is undesired, look here: https://stackoverflow.com/a/74387976/12287457
End Sub

注意：

虽然 Sleep 确实以毫秒为单位获取其参数，但其分辨率为实际上不是 1 毫秒。
在 Mac 上，最大 usleep 持续时间受 MAX_UINT = &HFFFFFFFF 微秒或约 4294.97 秒（约 71 分钟）的限制。这就是为什么 Mac 的自定义 Sleep 子程序会针对输入值 dwMilliseconds > 多次调用 usleep 。 &H418937，以避免整数溢出问题。
在 Windows 上（以及在具有自定义 Sleep 子项的 Mac 上），最大 Sleep 持续时间受 MAX_UINT 毫秒限制，约为 4294967.3 秒。（约 49.71 天）
在 Windows 和 Mac 上，使用大于 MAX_LONG = &H7FFFFFFF (= 2147483647) 的毫秒值调用 Sleep 需要将其传递为负 VBA Long 值，通过如下调用实现最大睡眠持续时间： Sleep -1 (=Sleep &HFFFFFFFF)

请注意，这种暂停执行的方式VBA 有严重的缺点！

最重要的是，所有 Microsoft Office 应用程序都在与主用户界面相同的线程中运行 VBA 代码。这意味着，调用 Sleep 本质上会冻结整个应用程序（它将在任务管理器中显示为“未响应”！）。如果不等待时间过去或强制退出应用程序并重新启动它，则无法从此状态恢复。 Excel Application.Wait< /a> 也遇到同样的问题。在这种情况下，虽然应用程序不会在任务管理器中显示为未响应，但它对用户也同样没有响应。

避免此问题的一种方法是在循环中调用 DoEvents，正如其他人已经指出的那样。然而，这又带来了另一个问题。由于应用程序将尝试尽可能快地执行 VBA 代码，因此会以最大可实现的速率调用 DoEvents，从而基本上使该单线程上的 CPU 完全饱和，从而导致不必要的高 CPU 和功耗使用，并可能减慢其他更重要任务的速度。用户界面。

要了解暂停 VBA 执行的最佳方法，请查看此答案。

Cross-Platform Solution

The accepted answer will not work in 64-bit VBA. Also, it won't work on Mac.

Thanks @Cristian Buse for pointing out Mac compatibility in your comment!

Achieving full Mac compatibility requires importing the platform-dependent library function for suspending thread execution, that is usleep on Mac and Sleep on Windows. Because usleep takes it's argument in microseconds and Sleep uses milliseconds, it is necessary to declare a custom Sub that deals with the conversion.

Importing the library functions and declaring the Sub can be done as presented in the following.

This solution emerged from a collaboration of myself and Cristian Buse on an adaption of his original solution, to avoid integer overflow and to allow Sleep times of more than &HFFFFFFFF microseconds (~71 minutes) on Mac:

#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 'Windows
        MSleep dwMilliseconds
    #End If
End Sub

Now Sleep will be available on both, Windows and Mac and in 32- as well as 64-bit environments.

It can be called like this:

Sub ExampleSleepCall()
    Sleep 1000 'Suspends thread execution for 1 second
    'Calling application will freeze completely for that amount of time!
    'If this is undesired, look here: https://stackoverflow.com/a/74387976/12287457
End Sub

Note that:

While Sleep does take its argument in milliseconds, its resolution is not actually 1 millisecond.
On Mac, the maximum usleep duration is limited by MAX_UINT = &HFFFFFFFF microseconds or about 4294.97 seconds (~71 minutes). This is why the custom Sleep sub for Mac will call usleep multiple times for input values dwMilliseconds > &H418937, to avoid integer overflow issues.
On Windows (and on Mac with the custom Sleep sub), the maximum Sleep duration is limited by MAX_UINT milliseconds, about 4294967.3 seconds. (~49.71 days)
On both Windows and Mac, calling Sleep with millisecond values greater than MAX_LONG = &H7FFFFFFF (= 2147483647) requires passing it negative VBA Long values, with the maximum Sleep duration achieved by calling it like this: Sleep -1 (=Sleep &HFFFFFFFF)

Note that this way of pausing the execution has severe drawbacks in VBA!

Most importantly, all Microsoft Office applications run VBA code in the same thread as the main user interface. This means, calling Sleep essentially freezes the entire application (It will show as "not responding" in Task-Manager!). There is no way of recovering from this state without waiting for the time to pass or force quitting the Application and restarting it. Excels Application.Wait suffers from the same issue. While the app will not show as not responding in Task Manager in this case, it will be just as unresponsive to the user.

A way to circumvent this problem is calling DoEvents in a loop, as other people have already pointed out. However, this comes with another issue. Because the application will try to execute VBA code as fast as possible, DoEvents is called at the maximum achievable rate essentially saturating the CPU completely on that single thread, leading to high, unnecessary CPU and power usage and potentially slowing down other more important tasks in the UI.

To learn about the best way to pause VBA execution, look at this answer.

回复收藏 0 原文

老娘不死你永远是小三 2024-07-19 10:53:19

以某种方式添加

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

在我的代码中的其他地方产生了额外的问题。
我最终使用了我在其他论坛上找到的这个功能并进行了一些调整：

Function WaitTime(n As Double)
'Function that wait an amount of time n in seconds
TWait = Time
TWait = DateAdd("s", n, TWait)
Do Until TNow >= TWait
     TNow = Time
Loop
End Function

希望这会有所帮助:)

Adding

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

somehow created additional problems somewhere else in my code.
I ended up using this function that I found on an other forum and tweeked a bit:

Function WaitTime(n As Double)
'Function that wait an amount of time n in seconds
TWait = Time
TWait = DateAdd("s", n, TWait)
Do Until TNow >= TWait
     TNow = Time
Loop
End Function

hope this helps :)

回复收藏 0 原文

剧终人散尽 2024-07-19 10:53:19

如果代码在Form中执行，则可以使用Form内置的Timer。

启动表单的计时器：

TimerInterval = 1000 ' Delay in ms

通过声明事件处理程序

Private Sub Form_Timer()
    TimerInterval = 0 ' Stop the timer if don't want to repeat the event each second

    ' Execute your code here
End Sub

If the code is executing in a Form, you can use the built-in Timer of the Form.

Start the timer of the Form with

TimerInterval = 1000 ' Delay in ms

Declare an event handler:

Private Sub Form_Timer()
    TimerInterval = 0 ' Stop the timer if don't want to repeat the event each second

    ' Execute your code here
End Sub

回复收藏 0 原文

~没有更多了~

关于作者

荒岛晴空

暂无简介

0 文章

0 评论

21 人气

关注发私信

友情链接

文江博客

VBA 中是否有相当于 Thread.Sleep() 的函数

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（10）

跨平台解决方案

请注意，这种暂停执行的方式VBA 有严重的缺点！

Cross-Platform Solution

Note that this way of pausing the execution has severe drawbacks in VBA!

关于作者

相关话题

热门标签

推荐作者

qq_FjTq5B

18273202778

WordPress小学生

〃温暖了心ぐ

迷乱花海

niuniu

友情链接

VBA 中是否有相当于 Thread.Sleep() 的函数

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（10）

跨平台解决方案

请注意，这种暂停执行的方式VBA 有严重的缺点！

Cross-Platform Solution

Note that this way of pausing the execution has severe drawbacks in VBA!

关于作者

相关话题

热门标签

推荐作者

qq_FjTq5B

18273202778

WordPress小学生

〃温暖了心ぐ

迷乱花海

niuniu

友情链接

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。