0

I am working with Visual Basic Application on Excel. I have a Macro, which should be executed after a time delay. I want to track the waiting time with a timer meanwhile. I have created an additional module in which I have the following functions:

Public Sub start_time()
Application.OnTime Now + TimeValue("00:00:01"), "next_moment"
End Sub

Public Sub end_time()
Application.OnTime Now + TimeValue("00:00:01"), "next_moment", , False
End Sub

Public Sub next_moment()
If Worksheets("Messwerte").Range("A1").Value = 0 Then Exit Sub
Worksheets("Messwerte").Range("A1").Value = Worksheets("Messwerte").Range("A1").Value - TimeValue("00:00:01")
start_time
End Sub

I have my timer in the cell Worksheets("Messwerte").Range("A1").Value. The structure of my code is now the following:

For Loop
Code Block 1
Call start_time #Waiting should be applied here
Code Block 2
End For Loop

My problem is now that the timer starts at start_time, but Code Block 2 is also starting. I want to have my timer and when my timer shows "00:00:00" then Code Block 2 should start.

On the forums I just found solutions to either having a timer or to wait until the code is continued but not both working like I want to. Does someone has a solution on this?

Thanks

Duc_Dang
  • 1
  • 1
    One advantage of `OnTime` is that it's "non-blocking" (other things can happen while you're waiting for it to fire) Unfortunately this means your code does not wait for the process to complete - it calls `start_time` and then immediately continues. – Tim Williams Jan 19 '21 at 17:54
  • 1
    To fix this you need to cache the state of the loop (eg. store the counter in a global or Static variable) and have a callable entry point which can be called to continue the loop when the timer expires. Difficult to make concrete suggestions without seeing the actual loop code. – Tim Williams Jan 19 '21 at 18:12
  • I've never used OnTime before, so be aware it _may_ have the same quirk as Application.Wait, in that: a 1 second wait, depending on the millisecond it was triggered, will wait from (very near) 0, _up to_ 1 sec. For detail See [How to pause](https://stackoverflow.com/questions/1544526/how-to-pause-for-specific-amount-of-time-excel-vba). For more granular control see "Sleep" in that link. Also, I think Wait and Sleep both _do_ "block". – Stax Jan 19 '21 at 21:41

1 Answers1

0

I found a solution, which is not clean, but it does work. Hopefully it helps you.
I created a function, which is a boolean and becomes false, when the timer is on 0.

Public Function Check_Time() As Boolean
    If Sheets("Timer_Tabular").Range("Timer_Position").Value = 0 Then
        Check_Time = False
    Else
        Check_Time = True
    End If
End Function

The code has the following structure: 

#Code before the waiting
Dim Waiting as Boolean
Waiting = True

'Wait until timer is zero
 Do While Waiting
    If Check_Time() = True Then
    'Enables working on excel while doing nothing.
       DoEvents
    ElseIf Check_Time() = False Then
       Waiting = False
       Exit Do
    End If
 Loop
#Code Block, which is executed after waiting time.

So the Macro is catched at this point inside the while loop and the while loop breaks, when the timer is 0. The DoEvents "unfreezes" the excel screen.
Endresult is: The macro code stops running, but the timer goes on. If the timer is 0 the code will be continued.

Duc_Dang
  • 1