Wait Seconds and Tenths

[Zone]

I want to put a whole number in a cell, say A1, to represent seconds,
and another whole number in a cell, say B1, to represent tenths (or
hundredths) of a second. How do I get my macro to pause for this
amount of time? Please specify type of any variables! Thanks, James

 

[JLGWhiz]

I asked this question a few weeks ago and could not find anyone who knew how
to get less than one second. Hope you have better luck, but I don't think
you will find the answer in VBA.

 

[Zone]

JLG, I think I figured it out. Help says that in Windows, Timer
returns fractional seconds, and on the Mac the Timer resolution is 1
second. Since I'm on a PC, I decided to combine the seconds and the
fractional part into one mixed number. This SEEMS to work! Cheers,
James

Sub myTest()
TestTimer 0.5
End Sub

Sub TestTimer(myWait As Single)
Dim start
start = Timer ' Set start time.
Do While Timer < start + myWait
DoEvents ' Yield to other processes.
Loop
End Sub

 

[Chip Pearson]

Zone,

Try the following code:

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub FallAsleep()
Dim Seconds As Long
Dim Tenths As Long
Dim SleepTime As Double
Seconds = Range("A1").Value
Tenths = Range("B1").Value
SleepTime = ((Seconds * 1000) + (Tenths * 100))
Debug.Print "Ready To Sleep: " & CStr(SleepTime)
Sleep dwMilliseconds:=SleepTime
Debug.Print "Wake Up"
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Zone]

Thanks, Chip. Even better! James

Zone,

Try the following code:

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub FallAsleep()
Dim Seconds As Long
Dim Tenths As Long
Dim SleepTime As Double
Seconds = Range("A1").Value
Tenths = Range("B1").Value
SleepTime = ((Seconds * 1000) + (Tenths * 100))
Debug.Print "Ready To Sleep: " & CStr(SleepTime)
Sleep dwMilliseconds:=SleepTime
Debug.Print "Wake Up"
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[JLGWhiz]

Zone, couldn't get yours to work with my application:

Counter = 1
Do Until Counter = 37
MyClrValue = Int((55 * Rnd) + 1)
Worksheets(2).Range(Cells(Counter, 1), Cells(Counter,
16)).Interior.ColorIndex = MyClrValue
'Here is where I need about a half second,
'or maybe quarter second delay.
Counter = Counter + 1
Loop

Without the delay it looks like all the colors appear at once and I want the
waterfall effect.

 

[Tom Ogilvy]

Sub EFG()
Dim counter As Long
Dim s As Single
counter = 1
Do Until counter = 37
MyClrValue = Int((55 * Rnd) + 1)
With Worksheets(2)
.Range(.Cells(counter, 1), _
.Cells(counter, 16)).Interior _
.ColorIndex = MyClrValue
End With
s = Timer + 0.5
Do While Timer < s
DoEvents
Loop
'Here is where I need about a half second,
'or maybe quarter second delay.

counter = counter + 1
Loop

End Sub


worked on my machine. Can't say it waits exactly half a second, but there
is a measurable pause.

 

[Zone]

JLG, Strange that mine wouldn't work for you. The solution suggested by
Chip is surely much more accurate, delay-wise, but I liked mine because it's
so darned simple. It's maybe the Do Events that's causing the problem (?).
You could rem out that line and try again. Or/and put it in a new file and
try it by itself. It seems to work surprisingly well for me. Cheers, James

 

[JLGWhiz]

Thanks, Tom. Old Codgers can learn new tricks. I was trying to nest the
wrong kind of loop. Never tried the DoEvents thing before.

And Zone, glad you got yours working too.

Merry Crhistmas to all.

John

 

[JLGWhiz]

James, yours did work after Tom gave me the clue to how I could apply it. My
old brain just don't grasp things like it used to. I sort of have to be led
by the hand.

Happy hoidays! John

 

[Zone]

John, Terrific! Lets me know that it's not just a fluke that it's