Application.Wait Does it really work?

[Mousehunter]

Well I 'll be damned...

I am trying to use Application.Wait function in order to alter the contents of a cell automatically using a preset period of time using a variable called BPM. For instance if I have 60 Beats Per Minute (BPM hereafter) then I want the cell to change every second because I want 60BPM per 60sec. If I have 30BMP I want it changed every second second and if I have 120BPM every half second. The formula to calculate the beats per second is 60/BPM.

It seems to be acting bizzare... While it is fairly accurate for BPM up to 60 (implied period of time between consecutive changes more than or equal to a second), it seems to be stuck to 1 second beating only 60 times per minute after 61BPM and up to 119BPM while it should be beating equal times per minute and seems to go crazy if I dare setting BPM at 120 or more (implied period of time between consecutive changes less that half a second)

Summary of Behaviour

BPM	Expected BPM	Actual BPM	Comment
<=60	<=60	<=60	Accurate
<=119	<=120	60	Stuck to 60BPM
>=119	>=119	>10.000	Goes crazy...

Below is the code I use

Option Explicit Sub StartClock() Dim BPM As Byte, Rythm Let BPM = [j4] Let [d4] = 0 [b2:b3].ClearContents Let [b1].Value = "Start" Let Rythm = TimeValue("00:01:00") / BPM Let [b5].Value = Rythm If [b2].Value = "" Then [b2].Value = Now Label: VBA.DoEvents Let [b3].Value = Now Let [d4] = [d4] + 1 'Beep Application.Wait (Now + Rythm) If [b1].Value = "Stop" Then Exit Sub GoTo Label End Sub Sub StopClock() Let [b1].Value = "Stop" End Sub Sub Reset_Timer() Let [b1].Value = "Stop" Let [d4] = 0 [b2:b3].ClearContents End Sub

Here is the behaviour for 60BPM

View attachment 44120

Below is the behaviour for 119 BPM
View attachment 44121
And finally, below is the behaviour from 120 and over
View attachment 44123

 

[John_w]

The minimum wait for Application.Wait is 1 second.

You could use the Sleep API instead. Add this at the top of the module, after the Option Explicit:

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

And replace the Application.Wait with:

Sleep CDbl(60 / BPM) * 1000

 

[Mousehunter]

The minimum wait for Application.Wait is 1 second.

You could use the Sleep API instead. Add this at the top of the module, after the Option Explicit:

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

And replace the Application.Wait with:

Sleep CDbl(60 / BPM) * 1000

Thank you very much for the answer.

I would rather not use code I do not understand (like Windows API calls) because if things do not turn out well, I do not know how to clean up the mess...

I will give it a shot since though since there is no other option and I will get back to you.

This could prove a life-saver as my ultimate goal is to synchronise Excel with the beat of a song and this is just an experiment...

 

[Mousehunter]

The minimum wait for Application.Wait is 1 second.

You could use the Sleep API instead. Add this at the top of the module, after the Option Explicit:

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

And replace the Application.Wait with:

Sleep CDbl(60 / BPM) * 1000

It worked, many thanks!