How to trigger the `Beep` function a second time in VBA

Question

I've built a dynamic to-do scheduler that allows the user to input tasks alongside their respective start times.

The clock runs in real time, and conditional formatting is used to highlight the current task in green. When the clock gets to n number of seconds (according to WarningSeconds) before the start time of the next task, the current task turns red, and the Beep sound plays five times.

Then, when the next task kicks in, it turns green and the previous one gets crossed out. That's great, but I want the Beep to play five times at the start of the new task as well. I've tried various things, but none of my implementations work.

What can I add to my code to get this working?

Check out the example here: https://i.postimg.cc/Pt8wb6Cr/2024-08-15-21-59-54.png.

Here, the Beep plays five times at 17:29:50, but I need it to play at 17:30:00 as well.

#If VBA7 Then
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
#Else
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

Public IsRunning As Boolean
Public AlarmCounter As Integer

Sub StartStopClock()
        IsRunning = Not IsRunning
        AlarmCounter = 0 ' Initialise the counter
        ' Start the timer
        TimerLoop
End Sub

Sub TimerLoop()
    On Error Resume Next
    Do While IsRunning
        DoEvents
        Sheet1.Range("Clock").Value = TimeValue(Now)
        If AlarmCounter < 5 Then
            ActivateAlarm
        End If
    Loop
End Sub

Sub ActivateAlarm()
    If Range("WarningAlert").Value = True Then
        Beep
        Sleep (1000)
        AlarmCounter = AlarmCounter + 1 ' Increment the counter
    End If
End Sub

The formula for WarningAlert is:

=AND(Clock>=ROUND(MINIFS(tblTask[Time],tblTask[Time],">"&Clock)-TIME(0,0,WarningSeconds),10),Clock<MAX(tblTask[Time]))

Answer 1

One important element of a timer loop is to use Sleep in combination with DoEvents to make sure that CPU consumption remains low. At least that made my Excel-Pomodoro-Timer work much smoother and helped keep Excel more responsive. It can be as small as 10 milliseconds and still make a big difference.

If you don't want it to mess up with the timing you can always keep it inside an if-statement:

        DoEvents
        If AlarmCounter = 0 Then
            Sleep (10)
        End If

Regarding you issue of not being able to replay the alarm at the start of the task, I'd suggest to make use of flag variables to allow you to control in what state the loop is in. For instance, are we in the state of playing the alarm at the end of a task; are we waiting for the next task to start; etc.

One possible implementation could look something like this:

Public IsRunning As Boolean
Public AlarmCounter As Integer

Sub StartStopClock()
        IsRunning = Not IsRunning
        AlarmCounter = 0 ' Initialise the counter
        ' Start the timer
        TimerLoop
End Sub

Sub TimerLoop()
    'We need to Resume Next to avoid errors occuring if we edit cells inside Excel while the loop is running.
    On Error Resume Next
    Do While IsRunning
    
        DoEvents
        If AlarmCounter = 0 Then
            Sleep (10)
        End If
        Sheet1.Range("Clock").Value = TimeValue(Now)
        
        Dim NotificationOngoing As Boolean
        Dim WaitingForNextTask As Boolean
        
        If Range("WarningAlert").Value = True And Not WaitingForNextTask Then
            NotificationOngoing = True
        End If
        
        If NotificationOngoing Then
            If AlarmCounter < 5 Then
                ActivateAlarm
            Else
                AlarmCounter = 0
                NotificationOngoing = False
                WaitingForNextTask = True
                Dim NextTaskStart As Date
                NextTaskStart = Now + (ThisWorkbook.Names("WarningSeconds").RefersToRange.Value2 - 5) / 24 / 3600
            End If
        End If
        
        If WaitingForNextTask And Now >= NextTaskStart Then
            If AlarmCounter < 5 Then
                ActivateAlarm
            Else
                AlarmCounter = 0
                WaitingForNextTask = False
            End If
        End If
            
    Loop
End Sub

Sub ActivateAlarm()

    Beep
    Sleep (1000)
    AlarmCounter = AlarmCounter + 1 ' Increment the counter

End Sub