how to "delay" formula's calculations

[furbiuzzu]

hi guys,

i don't know if what i'm going to ask you is possible in Excel or in
VB.

i want to write a macro/vb procedure that:

shows results, of some calculations made on a matrix of cells of 10
rows 15 columns (i.e.), "row by row" with a delay of 2 seconds for
each row !

is it possible?

 

[Guest]

You can use the Application.OnTime method:
Application.OnTime(EarliestTime, Procedure As String, [LatestTime],
[Schedule])
Earliest time is the time you want it to run, e.g.
Now()+TimeValue("00:00:02"). Procedure is the name of the sub you want to run
If by delay of 2 sec you mean 2 sec after any of the matrix values are
changed, you could use the Worksheet_Change event procedure to trigger your
procedure:
Sub Worksheet_Change(ByVal Target as Range)
Application.OnTime Now()+TimeValue("00:00:02"), "MySub"
End Sub

For more info see the Help files.

 

[Guest]

Try something like the following

Sub Test()
Dim i%, dTime As Date
For i = 1 To 10
Cells(i, 1) = i
dTime = Now + TimeValue("00:00:02")
Do While Now < dTime
DoEvents
Loop
Next i
End Sub

Alok Joshi

 

[furbiuzzu]

hi guys !

i've tried to write out your codes but i think the best way to solve my
problem is to use these lines i've found on Vb guide.

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

the application here delays by 2 seconds the execution of the macro in
which these lines are written!

that's what i was lookin for.

thanks for your suggestion 'cause they allowed me to find this useful
macro !

the question now is: if i want to delay my data for less than a second
(0.5 seconds??)

if i put 0.5 in

newSecond = Second(Now()) + 0.5

my macro doesn't recognize it becasue the variable

waitTime = TimeSerial(newHour, newMinute, newSecond)

doesn't 'accept' decimals....

 

[Guest]

The documentation of the Wait method is a little ambiguous, but I have tested
it and it appears to me that it will only work for integer seconds. In fact,
what it seems to do is to "fire" when the "seconds" value of the clock turns
over, which affects the timing even when you specify the "2 second" wait.
What I mean is easiest shown by example: let's say you run your "2 second"
code:

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

If the time for "Now()" is, say, 10:45:03 (exactly) then the wait is up at
exactly 10:45:05 and you have a 2 second delay. But if Now() is actually
10:45:03.9, the wait
is STILL up at exactly 10:45:05 (it seems to take only the integer seconds,
10:45:03, as the "start time"), so you have a 1.1 second delay. I have not
seen any documentation to verify this, but my tests seem to indicate that is
what happens, even if I try using times calculated as fractions of a day
(with a Double variable).

I am not sure how critical your timing is, but wanted you to be aware of
this. I am not sure if any of the other methods for giving you a delay will
behave any differently.

 

[furbiuzzu]

i'm just workin on a tool for an online game "hattrick" i don't know if
you know it! (COOOOOOOL).
i want to calculate some variations and i have to show them with a
little of "suspance".

thevalues i've to show are located in (+ or -) 200/300 cells (depends
on users DATA)
so 1 second of delay for each cell means 3/5 minutes to accomplish my
macro.

if i run the macro without delay it needs less than 1 second to
visualize all values.

i want my application to end between these two times !

so 1 second is too much quick
3-4 minutes are too long.

what to do???

actually i calculate my values with a double loop on rows and columns
to be filled, so my macro ends in 10-15 seconds !

thanks for your 'study' on decimals but, as you said before is not
possible to use decimals in 'time formulas'