blocks|key|1435133|text|使用Wait+method|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1435134|Application.Wait+Now+%2B+#0:00:01#|code-block|syntax|javascript|1435135|或者(对于Excel+2010和更高版本)：|1435136|Application.Wait+Now+%2B+#12:00:01+AM#|1435137|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/aa213656%2528office.11%2529.aspx^0|2|B|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@$A|V|B|W|1|X]]|C|$]]|$1|D|3|E|5|F|7|Y|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|Z|8|@]|9|@]|C|$]]|$1|K|3|L|5|F|7|10|8|@]|9|@]|C|$G|H]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|C|$]]]|N|$O|$5|P|Q|R|C|$S|T]]]]

Use the <a href="http://msdn.microsoft.com/en-us/library/aa213656%28office.11%29.aspx" rel="noreferrer">Wait method</a>:

<pre><code>Application.Wait Now + #0:00:01#
</code></pre>

or (for Excel 2010 and later):

<pre><code>Application.Wait Now + #12:00:01 AM#
</code></pre>

blocks|key|1220209|text|将这个添加到你的模块中|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1220210|Public+Declare+Sub+Sleep+Lib+"kernel32"+(ByVal+dwMilliseconds+As+Long)|code-block|syntax|javascript|1220211|或者，对于64位系统，请使用：|1220212|Public+Declare+PtrSafe+Sub+Sleep+Lib+"kernel32"+(ByVal+dwMilliseconds+As+LongPtr)|1220213|在宏中调用它，如下所示：|1220214|Sub+Macro1()
'
'+Macro1+Macro
'
Do
++++Calculate
++++Sleep+(1000)+'+delay+1+second

Loop
End+Sub|1220215|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

Add this to your module

<pre><code>Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
</code></pre>

Or, for 64-bit systems use:

<pre><code>Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
</code></pre>

Call it in your macro like so:

<pre><code>Sub Macro1()
'
' Macro1 Macro
'
Do
 Calculate
 Sleep (1000) ' delay 1 second

Loop
End Sub
</code></pre>

blocks|key|1220379|text|而不是使用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1220380|Application.Wait(Now+%2B+#0:00:01#)|code-block|syntax|javascript|1220381|我更喜欢：|1220382|Application.Wait(Now+%2B+TimeValue("00:00:01"))|1220383|因为之后阅读起来容易多了。|1220384|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

instead of using:

<pre><code>Application.Wait(Now + #0:00:01#)
</code></pre>

i prefer:

<pre><code>Application.Wait(Now + TimeValue("00:00:01"))
</code></pre>

because it is a lot easier to read afterwards.

blocks|key|1220300|text|这对我来说是完美无瑕的。在"do+until“循环之前或之后插入任何代码。在本例中，将5行代码(time1=+&+time2=+&+"do+until“循环)放在do循环中的末尾|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1220301|sub+whatever()
Dim+time1,+time2

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

End+sub|code-block|syntax|javascript|1220302|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

this works flawlessly for me. insert any code before or after the "do until" loop. In your case, put the 5 lines (time1= &amp; time2= &amp; "do until" loop) at the end inside your do loop

<pre><code>sub whatever()
Dim time1, time2

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

End sub
</code></pre>

blocks|key|1220343|text|kernel32.dll中的睡眠声明在64位Excel中不起作用。这可能会更一般化一点：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1220344|#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|code-block|syntax|javascript|1220345|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The declaration for Sleep in kernel32.dll won't work in 64-bit Excel. This would be a little more general:

<pre><code>#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
</code></pre>

blocks|key|1435487|text|只是clemo代码的一个清理版本-在Access中工作，它没有Application.Wait功能。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1435488|Public+Sub+Pause(sngSecs+As+Single)
++++Dim+sngEnd+As+Single
++++sngEnd+=+Timer+%2B+sngSecs
++++While+Timer+<+sngEnd
++++++++DoEvents
++++Wend
End+Sub

Public+Sub+TestPause()
++++Pause+1
++++MsgBox+"done"
End+Sub|code-block|syntax|javascript|1435489|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Just a cleaned up version of clemo's code - works in Access, which doesn't have the Application.Wait function.

<pre><code>Public Sub Pause(sngSecs As Single)
 Dim sngEnd As Single
 sngEnd = Timer + sngSecs
 While Timer &lt; sngEnd
 DoEvents
 Wend
End Sub

Public Sub TestPause()
 Pause 1
 MsgBox "done"
End Sub
</code></pre>

blocks|key|1220402|text|Application.Wait++Second(Now)+%2B+1|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1220403|entityMap^0|0|X|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

<code>Application.Wait Second(Now) + 1</code>

blocks|key|1220440|text|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+%2B+T),+2,+2)
++++++++++++strSecsDelay+=+"00:00:"+&+strT
++++Application.Wait+(Now+%2B+TimeValue(strSecsDelay))
End+Function|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1220441|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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:" &amp; strT
 Application.Wait (Now + TimeValue(strSecsDelay))
End Function
</code></pre>

blocks|key|1435545|text|以下是睡眠的另一种选择：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1435546|Sub+TDelay(delay+As+Long)
Dim+n+As+Long
For+n+=+1+To+delay
DoEvents
Next+n
End+Sub|code-block|syntax|javascript|1435547|在下面的代码中，我在一个旋转按钮上做了一个“发光”效果的闪烁，如果用户“有麻烦”，我会将他们引导到它，在循环中使用“睡眠1000”不会导致可见的闪烁，但循环工作得很好。|1435548|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|1435549|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Here is an alternative to sleep:

<pre><code>Sub TDelay(delay As Long)
Dim n As Long
For n = 1 To delay
DoEvents
Next n
End Sub
</code></pre>

In the following code I make a "glow" effect blink on a spin button to direct users to it if they are "having trouble", using "sleep 1000" in the loop resulted in no visible blinking, but the loop is working great.

<pre><code>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
</code></pre>

blocks|key|1220587|text|等待和睡眠功能会锁定Excel，在延迟结束之前，您无法执行其他任何操作。另一方面，循环延迟不会给你一个确切的等待时间。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1220588|因此，我将这两个概念结合在一起，解决了这个问题。它一直循环到您想要的时间为止。|1220589|Private+Sub+Waste10Sec()
+++target+=+(Now+%2B+TimeValue("0:00:10"))
+++Do
+++++++DoEvents+'keeps+excel+running+other+stuff
+++Loop+Until+Now+>=+target
End+Sub|code-block|syntax|javascript|1220590|你只需要在你需要延迟的地方调用Waste10Sec|1220591|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Wait and Sleep functions lock Excel and you can't do anything else until the delay finishes. On the other hand Loop delays doesn't give you an exact time to wait.

So, I've made this workaround joining a little bit of both concepts. It loops until the time is the time you want.

<pre><code>Private Sub Waste10Sec()
 target = (Now + TimeValue("0:00:10"))
 Do
 DoEvents 'keeps excel running other stuff
 Loop Until Now &gt;= target
End Sub
</code></pre>

You just need to call Waste10Sec where you need the delay

blocks|key|1435441|text|我做了这个来回答这个问题：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1435442|Sub+goTIMER(NumOfSeconds+As+Long)+'in+(seconds)+as:++call+gotimer+(1)++'seconds
++Application.Wait+now+%2B+NumOfSeconds+/+86400#
++'Application.Wait+(Now+%2B+TimeValue("0:00:05"))++'other
++Application.EnableEvents+=+True+++++++'EVENTS
End+Sub|code-block|syntax|javascript|1435443|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

i had this made to answer the problem:

<pre><code>Sub goTIMER(NumOfSeconds As Long) 'in (seconds) as: call gotimer (1) 'seconds
 Application.Wait now + NumOfSeconds / 86400#
 'Application.Wait (Now + TimeValue("0:00:05")) 'other
 Application.EnableEvents = True 'EVENTS
End Sub
</code></pre>

blocks|key|1435226|text|试试这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1435227|Threading.thread.sleep(1000)|code-block|syntax|javascript|1435228|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try this :

<pre><code>Threading.thread.sleep(1000)
</code></pre>

blocks|key|1220652|text|您可以使用Application.wait+now+%2B+timevalue("00:00:01")或Application.wait+now+%2B+timeserial(0,0,1)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1220653|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

You can use 
Application.wait now + timevalue("00:00:01")
or
Application.wait now + timeserial(0,0,1)

I have an Excel worksheet that has the following macro. I'd like to loop it every second but danged if I can find the function to do that. Isn't it possible?

<pre><code>Sub Macro1()
'
' Macro1 Macro
'
Do
 Calculate
 'Here I want to wait for one second

Loop
End Sub
</code></pre>

How to pause for specific amount of time? (Excel/VBA)

我有一个Excel工作表，其中包含以下宏。我想每秒循环一次，但如果我能找到这样做的函数，我就会挂起。有没有可能？Sub Macro1()'' Macro1 Macro'Do    Calculate    'Here I want to wait for one secondLoopEnd Sub

问如何暂停特定的时间量？(Excel/VBA)
EN

回答 13

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何暂停特定的时间量？(Excel/VBA)EN

回答 13

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何暂停特定的时间量？(Excel/VBA)
EN