Application.wait timing is too fast or too slow

[tj4242]

I am simulating an old board game in Excel. The board game used a spinner to provide random numbers. I am planning to simulate the spinner pressing a button, then the numbers on the worksheet will highlight one by one as the imaginary hand moves past them. The 1 will highlight, then 2, then 3 and so on until it gets to the random number spun.
Here is the original

Here is the Excel with the 1 highlighted.

The problem is the spin timing. I calculate the random number, highlight the correct cell in Red, unhighlight the previous cell, then do an Application.Wait before moving to the next cell to highlight. The problem is it is either too fast to see the animation or painfully slow.

Application.Wait (Now + 0.000005) should wait half a second but the animation is too fast. Application.Wait (Now + 0.000006) is too slow and look more like one second between highlights. Any assistance getting the timing right? I am guessing 1/4 to 1/2 second would be about right, but I would have to see it to tweak it.

Battlestar Game.xlsm
	AH	AI	AJ	AK	AL	AM	AN	AO	AP
6			P			BH
7
8				6		1			P
9	BH		4	5		2	3
10
11			3	2		5	4		BH
12	P			1		6
13
14				BH			P
Board

Global CurrentSpinValue As Integer

Const ms As Double = 0.000000011574

Sub Spin_Click()

    Dim spin As Integer
    
    Dim spin_row(13) As Integer
    Dim spin_col(13) As Integer
    
    'top right 1
    spin_row(0) = 8
    spin_col(0) = 39
    
    'top right 2
    spin_row(1) = 9
    spin_col(1) = 39
    
    'top right 3
    spin_row(2) = 9
    spin_col(2) = 40
    
    'bottom right 4
    spin_row(3) = 11
    spin_col(3) = 40
    
    'bottom right 5
    spin_row(4) = 11
    spin_col(4) = 39
    
    'bottom right 6
    spin_row(5) = 12
    spin_col(5) = 39
    
    'bottom left 1
    spin_row(6) = 12
    spin_col(6) = 37
   
    'bottom left 2
    spin_row(7) = 11
    spin_col(7) = 37
    
    'bottom left 3
    spin_row(8) = 11
    spin_col(8) = 36
    
    'top left 4
    spin_row(9) = 9
    spin_col(9) = 36
    
    'top left 5
    spin_row(10) = 9
    spin_col(10) = 37
    
    'top left 6
    spin_row(11) = 8
    spin_col(11) = 37
    
    Randomize
    spin = Int(11 + Rnd * 48)       'random line from 1 revolution to 4

    highlight_spin = CurrentSpinValue   ' start where we left off from previous spin
    For i = 1 To spin
        Cells(spin_row(highlight_spin), spin_col(highlight_spin)).Interior.ColorIndex = 3               'highlight cell in red
        If (highlight_spin = 0) Then                                                                    'if we wrapped around, clear the last cell
            Cells(spin_row(11), spin_col(11)).Interior.ColorIndex = 0
        Else
            Cells(spin_row(highlight_spin - 1), spin_col(highlight_spin - 1)).Interior.ColorIndex = 0
        End If
        highlight_spin = highlight_spin + 1
        If highlight_spin > 11 Then
            highlight_spin = 0
        End If

        Application.Wait (Now + 0.000006) '1/10sec
    Next
    
    CurrentSpinValue = highlight_spin
    
End Sub

 

[Anthony47]

Application.Wait runs with seconds, it doesn't manage sub-second delays

You may create your own "wait" using Timer; for example:

Sub myWait(ByVal myDelay As Single)
Dim myTim As Single
'
myTim = Timer
Do
    DoEvents
    If Timer > (myTim + myDelay) Or Timer < myTim Then Exit Do
    DoEvents
Loop
End Sub

Then you may use, for example

myWait(0.22)

This will wait some 0.22 seconds

Or you may use Windows "Sleep", that need to be declared on top of the module:

#If VBA7 Then       '!!! ON  TOP  OF  THE  VBA  MODULE   !!!!
    Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
#Else
    Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

Then you may use, for example

Wait 200                       'Wait in milliseconds

 

[tj4242]

Doh! I should have known that. Your myWait works perfectly and the adjusted timing (0.05) looks great