time interval other than forms

[JLGWhiz]

Here is the code I currently use:

Sub WaitTime()
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
sitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait sitTime
End Sub

Sub TestShape()
Set myDocument = Worksheets(4)
myDocument.Shapes.AddShape msoShape32pointStar, 250, 250, 100, 200
myDocument.Shapes(1).Fill.ForeColor.RGB = RGB(255, 100, 100)
Set newWordArt =
myDocument.Shapes.AddTextEffect(PresetTextEffect:=msoTextEffect1,
Text:="Test", fontName:="Arial Black", fontSize:=36, FontBold:=msoFalse,
FontItalic:=msoFalse, Left:=10, Top:=10)
Counter = 0
Do
With myDocument.Shapes(1)
.IncrementLeft Counter + 70
.IncrementTop (Counter * -1) + (-50)
.IncrementRotation 30
End With
Counter = Counter + 25
WaitTime
Loop While Counter < 100
Worksheets(4).Shapes(1).Delete
Worksheets(4).Shapes(2).Delete
End Sub

As you can see, one second is the least time interval I can get because of
the time serial limitation. I would like to cut the time interval to a half
second. Is there code for this without using a form?

 

[Dave Patrick]

Try something like this.

Dim mytime As Date
Dim mydelay As Single
Dim mymilliseconds As Single
mymilliseconds = 500
If mymilliseconds > 0 Then
mydelay = mymilliseconds / 86400000
Else
Exit Sub
End If
mytime = Now()
Do While Now() < mytime + mydelay
DoEvents
Loop


--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Here is the code I currently use:
|
| Sub WaitTime()
| newHour = Hour(Now())
| newMinute = Minute(Now())
| newSecond = Second(Now()) + 1
| sitTime = TimeSerial(newHour, newMinute, newSecond)
| Application.Wait sitTime
| End Sub
|
| Sub TestShape()
| Set myDocument = Worksheets(4)
| myDocument.Shapes.AddShape msoShape32pointStar, 250, 250, 100, 200
| myDocument.Shapes(1).Fill.ForeColor.RGB = RGB(255, 100, 100)
| Set newWordArt =
| myDocument.Shapes.AddTextEffect(PresetTextEffect:=msoTextEffect1,
| Text:="Test", fontName:="Arial Black", fontSize:=36, FontBold:=msoFalse,
| FontItalic:=msoFalse, Left:=10, Top:=10)
| Counter = 0
| Do
| With myDocument.Shapes(1)
| .IncrementLeft Counter + 70
| .IncrementTop (Counter * -1) + (-50)
| .IncrementRotation 30
| End With
| Counter = Counter + 25
| WaitTime
| Loop While Counter < 100
| Worksheets(4).Shapes(1).Delete
| Worksheets(4).Shapes(2).Delete
| End Sub
|
| As you can see, one second is the least time interval I can get because of
| the time serial limitation. I would like to cut the time interval to a
half
| second. Is there code for this without using a form?

 

[Jim Cone]

I have experimented with the Wait function using different decimal values
without much success.
The following post from Chip Pearson, while dealing with OnTime, appears
to apply to all VBA code dealing with time intervals.
Note the last paragraph.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Mon, Jul 24 2000 12:00 am
Chip Pearson
Microsoft.public.excel.programming:

OnTime is plenty accurate for 'general schedule' purposes. However, it is
probably not accurate enough if you care whether an event is triggered at
exactly 12:01:02 instead of 12:01:03. However, I can't really think of a
reason that level of accuracy would be required in a spreadsheet (I mean,
you're not running the launch sequence of the space shuttle from a VBA macro
are you?)

Basically, OnTime is really just a simple interface into one of the built-in
Windows API timers. Excel calls this API function, which tells Windows to
send it a message when the Windows timer 'pops'. Excel then searches for
the function you named in the OnTime call (it doesn't pass that to
Windows -- that is handled 'locally' within Excel), and runs the procedure.

If Excel receives the Windows message indicating that the timer 'popped' ,
it will run the procedure at the first chance it can. Excel can't run VBA
when you're in Edit mode, or when another VBA procedure is running, or in
the middle of a recalculation, so the OnTime event will be deferred until
Excel is good and ready.

If you have lots of other CPU intensive application running other than
Excel, those applications can cause an event to be deferred past the
scheduled time, because Excel isn't getting much access to the CPU.
Therefore, it cannot read its incoming messages, and doesn't know that the
timer has 'popped'. However, most good commercial quality software releases
control with the equivalent of a DoEvents statement periodically, which
allows other processes to read and reply to their input messages. And even
if Excel receives the message from Windows, it may not execute the procedure
right then -- it may just read and reply to the Windows message, and then
relinquish control back to Windows and the other running applications. In
this case, it will start the event as soon as it can, but the entire
execution of the procedure may take a while, because then system is very
busy.

All processes in Windows are assigned a priority, and higher priority
processes get more CPU time. Windows' own internal processes have the
highest priority, and everything else is below that. You can't really
control the priority of other processes (nor should you), and anything you
write in VBA is running in the Excel process, so you can't write your code
get higher priority than Excel itself.

OnTime is not what you'd use for high-precision performance timing or
something like that. Its intended purpose is for general use tasks like
updating a recordset from a database every few minutes or something like
that. If it really matters that something occurs at 12:01:02 and not one
second later, then neither Excel nor VBA is the appropriate development
platform.
'-----------------------


"JLGWhiz"
<[email protected]>
wrote in message
Here is the code I currently use:

Sub WaitTime()
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
sitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait sitTime
End Sub
-snip-
As you can see, one second is the least time interval I can get because of
the time serial limitation. I would like to cut the time interval to a half
second. Is there code for this without using a form?

 

[JLGWhiz]

You are right Jim. That is my dilema. The Wait function only allows a
minimum of one second for and interval within a loop. Anything less than
that has to be done on a form like an animation. I was hoping someone had
come up with a way to write a function similar to using Application.Wait ()
that could return the interval time in milliseconds.

The code that Dave Gave me can't be plugged into a loop as an interval, it
will simply allow the loop to run until the On Time mark is reached. My
objective is to make certain loops that change text or colors (any visual
effect) run slow enough for the human eye to perceive, but fast enough that
it gives a motion effect. I am using these loops in a novelty program that I
have written in VBA with Excel as the base. The program runs fine. It just
needs a little visual enhancement.

Thanks to both of you for your assistance.

JLG

 

[Jim Cone]

Chip Pearson has more to say on the subject of procedure timing.
Take a look at Using Windows Timers ...
http://www.cpearson.com/excel/ontime.htm
--
Jim Cone
San Francisco, USA


"JLGWhiz"
<[email protected]>
wrote in message
You are right Jim. That is my dilema. The Wait function only allows a
minimum of one second for and interval within a loop. Anything less than
that has to be done on a form like an animation. I was hoping someone had
come up with a way to write a function similar to using Application.Wait ()
that could return the interval time in milliseconds.
The code that Dave Gave me can't be plugged into a loop as an interval, it
will simply allow the loop to run until the On Time mark is reached. My
objective is to make certain loops that change text or colors (any visual
effect) run slow enough for the human eye to perceive, but fast enough that
it gives a motion effect. I am using these loops in a novelty program that I
have written in VBA with Excel as the base. The program runs fine. It just
needs a little visual enhancement.
Thanks to both of you for your assistance.
JLG