Wait Function, Code Stopping Unexpectedly

FracinDean

Board Regular
Joined
Jul 11, 2011
Messages
110
Office Version
  1. 365
Platform
  1. Windows
I'm trying to use the wait function to make a picture rotate slowly. When I run the code, the macro pauses unexpectedly until I hit the "Esc" key, and then after that, it runs as expected. Any ideas how to avoid the initial unexpected pause? Code below, and thank you for taking a look.

VBA Code:
    Dim i As Integer, delay As Double, waitTime As Double
    ActiveSheet.Shapes.Range(Array("Picture 4")).Select
    For i = 1 To 12
        Selection.ShapeRange.ThreeD.RotationZ = -i * 30
        delay = 0.002                                       'in seconds
        waitTime = TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + delay)
        Application.Wait waitTime
    Next i
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
I never liked the Wait method. Jim Ogilvy showed me this method years ago and I have found it very reliable.

VBA Code:
Dim i As Integer, w As Double
    ActiveSheet.Shapes.Range(Array("Picture 4")).Select
    For i = 1 To 12
        Selection.ShapeRange.ThreeD.RotationZ = -i * 30
        w = Counter + 0.002
        Do While Counter < w
            DoEvents
        Loop
    Next i
 

FracinDean

Board Regular
Joined
Jul 11, 2011
Messages
110
Office Version
  1. 365
Platform
  1. Windows
I never liked the Wait method. Jim Ogilvy showed me this method years ago and I have found it very reliable.

VBA Code:
Dim i As Integer, w As Double
    ActiveSheet.Shapes.Range(Array("Picture 4")).Select
    For i = 1 To 12
        Selection.ShapeRange.ThreeD.RotationZ = -i * 30
        w = Counter + 0.002
        Do While Counter < w
            DoEvents
        Loop
    Next i
Thanks JLGWHiz. I don't understand how Counter is getting initialized or changed. What is the value of Counter?
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
The counter is a built in feature of the OS. It is a timing function that allows you to set your values in seconds or decimal parts of seconds. and I am not sure just how many decimal places it will measure, but it seems to work fine with miliseconds. When you initialize the variable 'w' with the 'Counter + 0.002' value, it simply adds 0.002 to whatever the counter is (makes no difference if it starts a 0 or 451.008) then the 'DoEvents' will loop for 0.002 seconds at which point it yields back to the procedure and resumes running.
 

FracinDean

Board Regular
Joined
Jul 11, 2011
Messages
110
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Very clear and thanks for the help.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
OOPS! It has been a long time since I used that. It should be Timer, not Counter. But it works like I described.
VBA Code:
Dim i As Integer, w As Double
    ActiveSheet.Shapes.Range(Array("Picture 4")).Select
    For i = 1 To 12
        Selection.ShapeRange.ThreeD.RotationZ = -i * 30
        w = Timer + 0.002
        Do While Timer < w
            DoEvents
        Loop
    Next i
 
Solution

FracinDean

Board Regular
Joined
Jul 11, 2011
Messages
110
Office Version
  1. 365
Platform
  1. Windows
Well I didn’t want to split hairs but when I tried it and Counter didn’t work, I found another way using the Serial time function. Thanks for explaining that it should have been Timer. Now I know two ways to do it, but yours is better. Thanks again.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Well I didn’t want to split hairs but when I tried it and Counter didn’t work, I found another way using the Serial time function. Thanks for explaining that it should have been Timer. Now I know two ways to do it, but yours is better. Thanks again.
Yeah, the senility slips in there once in a while, but in my more lucid moments I can come up with some fairly good code.
Regards, JLG
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,479
Messages
5,770,323
Members
425,612
Latest member
martinijr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top