Putting a 30sec delay into a macro

[Nick]

Hi All

I want to set up a spreadsheet so that it can randomly
calculate whole numbers between (and including) zero (0)
and thirty-six (36) for an imitation roulette wheel.
However, instead of just allocating the recalculate (F9)
function to a macro, I want there to be a thirty second
delay between the time of pressing the button and the
random number appearing.

The formula I have so far is "=ROUNDUP(RANDBETWEEN(-
0.9999,36),0)" but I don't know how to write a macro that
will delay the Recalculate function by thirty seconds once
I press the macro-assigned button.

Any ideas?

Cheers
Nick

 

[Robert Christie]

Hi Nick
From Excel help "Wait" the following;
will re-calculate your formula after 30 second pause.

Sub Re_Calculate()
'
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 30
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
'
Calculate
End Sub

HTH

Bob C.

 

[mudraker]

Nick

Here's another way


Application.Wait TimeValue(Now()) + TimeValue("00:00:30"

 

[NICK]

THANKS GUYS...