如何让vba每10分钟执行一次?

时间:2021-12-17 04:45:47

I need to have my macro executed every 10 minutes .

我需要每10分钟执行一次宏。

This allows it to work in 10 minutes

这允许它在10分钟内工作

sub my_Procedure () 
msgbox "hello world"
end sub

sub test()
Application.OnTime Now + TimeValue("00:00:10"), "my_Procedure"
end sub

But this works only once . How can I have my macro execute every 10 minutes ?

但这只适用一次。如何让我的宏每10分钟执行一次?

2 个解决方案

#1


24  

You should use this pattern:

您应该使用以下模式:

Sub my_Procedure()
    MsgBox "hello world"        
    Call test ' for starting timer again
End Sub

Sub test()
    Application.OnTime Now + TimeValue("00:10:00"), "my_Procedure"
End Sub

#2


6  

Consider:

考虑:

Public RunWhen As Double
Public Const cRunWhat = "my_Procedure"

Sub StartTimer()
RunWhen = Now + TimeSerial(0, 10, 0)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
     schedule:=True
End Sub

Sub StopTimer()
   On Error Resume Next
   Application.OnTime earliesttime:=RunWhen, _
       procedure:=cRunWhat, schedule:=False
End Sub

Sub my_Procedure()
    MsgBox "hello world"
    Call StartTimer
End Sub

all in a standard module..............be sure to run StopTimer before exiting Excel

所有的都在一个标准的模块中在退出Excel之前一定要运行StopTimer。

NOTE

请注意

The "minute" argument in TimeSerial is the second argument.

《时代》杂志中的“分钟”论点是第二个论点。

#1


24  

You should use this pattern:

您应该使用以下模式:

Sub my_Procedure()
    MsgBox "hello world"        
    Call test ' for starting timer again
End Sub

Sub test()
    Application.OnTime Now + TimeValue("00:10:00"), "my_Procedure"
End Sub

#2


6  

Consider:

考虑:

Public RunWhen As Double
Public Const cRunWhat = "my_Procedure"

Sub StartTimer()
RunWhen = Now + TimeSerial(0, 10, 0)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
     schedule:=True
End Sub

Sub StopTimer()
   On Error Resume Next
   Application.OnTime earliesttime:=RunWhen, _
       procedure:=cRunWhat, schedule:=False
End Sub

Sub my_Procedure()
    MsgBox "hello world"
    Call StartTimer
End Sub

all in a standard module..............be sure to run StopTimer before exiting Excel

所有的都在一个标准的模块中在退出Excel之前一定要运行StopTimer。

NOTE

请注意

The "minute" argument in TimeSerial is the second argument.

《时代》杂志中的“分钟”论点是第二个论点。