VBA Calculate Pause

[jc0r]

Hi everyone I am having a little trouble with a sheet i'm currently working on. Having exhausted what i believe to be every avenue trying to do this without VBA, i've finally had to sucomb to it as i can't see an alternative and i'm no VB expert by any means! This is what i have so far:

Private Sub Worksheet_Calculate()
    If Sheet1.Cells(10, 10) = "1" _
    Or Sheet1.Cells(10, 10) = "2" Then
    'Pause needs to go here!
    Sheet1.Cells(10, 13) = "3"
    Else
        Sheet1.Cells(10, 13) = ""
    End If
End Sub

Basically what i am trying to do is insert a pause, sleep, wait etc. command into my IF statement.
If cell J10 (10, 10) = 1 or 2, pause for 300 milliseconds, then fill cell M10 (10, 13) with the value 3.
I am aware the wait and sleep commands cause excel not to funtion while the command is in use. Ideally i would like everything to perform as usual just that specific cell is paused. I use a program that writes all the data into Excel and it updates every 250ms hence my above effort is under Worksheet_Calculate().

 

[Andrew Poulsom]

Welcome to the Board.

Maybe try the OnTime method, calling a macro that updates the cell.

http://www.cpearson.com/Excel/OnTime.aspx

I don't think it will work for milliseconds though.

 

[bigturnip]

You can get it to wait for 1 second by:

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

Not sure if there's a way to get it as accurate as 300 milliseconds though

 

[bigturnip]

Although I am guessing this would depend where in the second you were as to how long it paused for, it could be anything from 1 millisecond up to a whole second, so you might be better using 2 seconds

 

[jc0r]

Not sure if ive done this correctly but i just tried the following code after your advice and it just stares at me for about a minute as if its "Waiting"?

Private Sub Worksheet_Calculate()
    If Sheet1.Cells(10, 10) = "1" _
    Or Sheet1.Cells(10, 10) = "2" Then
    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 1
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime
    Sheet1.Cells(10, 13) = "3"
    Else
        Sheet1.Cells(10, 13) = ""
    End If
End Sub

 

[LucB]

In a normal Module put:

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

And this in the Worksheet-Module.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Sheets(1).Cells(10, 10) = 1 _
Or Sheets(1).Cells(10, 10) = 2 Then
Sleep 300 '= 300 milliseconds
Sheets(1).Cells(10, 13) = 3
Else
Sheets(1).Cells(10, 13) = ""
End If
Application.EnableEvents = True
End Sub