Excel VBA - Set timer goes faster after each loop

Question

I am trying to create a timer that when reaching zero, it calls from the "Database" script, and then repeats the timer again from 30 seconds. Problem is, every time the timer restarts each tick removes more and more and it won't take long until the script starts repeating every 1 second even though the timer starts at 30.

I have been staring at this for ages now, and I can't figure out what is going on. It clearly states 'cell value = cell value - 1 second'.

What am I missing?

Dim IsOffline As String

Sub Repeater()
    
IsOffline = ThisWorkbook.Worksheets(3).Range("BC2")


    If IsOffline = "Online" Then
        
        RunTimer = Now + TimeValue("00:00:01")
        Application.OnTime RunTimer, "NextTick"

    End If
    
    
    
End Sub


Sub NextTick()
    
    If ThisWorkbook.Worksheets(3).Range("BC5").Value <= TimeValue("00:00:00") And IsOffline = "Online" Then
        
        Call Database
        ThisWorkbook.Worksheets(3).Range("BC5").Value = TimeValue("00:00:30")
        Repeater
    
    End If



    If ThisWorkbook.Worksheets(3).Range("BC5").Value > TimeValue("00:00:00") And IsOffline = "Online" Then
        
        ThisWorkbook.Worksheets(3).Range("BC5").Value = ThisWorkbook.Worksheets(3).Range("BC5").Value - TimeValue("00:00:01")
        Repeater

    End If
    

End Sub

For the sake of it, have you attempted `TimeSerial(0,1,0)` as opposed to `TimeValue("00:00:01")` and specified the argument for `EarliestTime`? [Application.OnTime method (Excel)](https://learn.microsoft.com/en-us/office/vba/api/excel.application.ontime) — Cyril, Apr 21 '22 at 13:38
If you are trying to run a macro every 30 seconds, why don't you just use the API `SetTimer` function? Such as: https://stackoverflow.com/a/23949000/3688861 https://stackoverflow.com/a/20272781/3688861 — Tragamor, Apr 21 '22 at 14:41
Cyril - Because I had no idea TimeSerial was a thing :) Will definitely look in to it! Tragamor - Mostly because I want to be able to display the timer in a cell, but maybe that's possible with SetTimer too? will look in to it! Thanks! — Keeday, Apr 22 '22 at 07:11

Tim Williams · Accepted Answer · 2022-04-22T16:07:50.157

1

In NextTick, if the first If block executes then the second one will also execute (because in the first block you reset BC5)

That should be an If... Else... End If block, not two separate If blocks.

Dim IsOffline As String

Sub Repeater()
    Dim RunTimer
    IsOffline = ThisWorkbook.Worksheets(3).Range("BC2").Value
    If IsOffline = "Online" Then
        RunTimer = Now + TimeValue("00:00:01")
        Application.OnTime RunTimer, "NextTick"
    End If
End Sub

Sub NextTick()
    If IsOffline = "Online" Then
        With ThisWorkbook.Worksheets(3).Range("BC5")
            If .Value <= TimeValue("00:00:00") Then
                'Database 'Call is deprecated
                .Value = TimeValue("00:00:30")
            Else
                .Value = .Value - TimeValue("00:00:01")
            End If
        End With
        Repeater
    End If
End Sub

edited Apr 22 '22 at 16:07

answered Apr 21 '22 at 15:36

Tim Williams

154,628
8
97
125

This makes so much more sense that what I did! Thanks a tons! I am still learning and this is huge! Only thing I don't really understand here is what the purpose of assigning the "tCell" variable is, as I don't see it being called for? – Keeday Apr 22 '22 at 07:09
I'd replaced use of `tCell` with the `With` block and forgotten to remove it before posting. Edited to remove... – Tim Williams Apr 22 '22 at 16:08
Makes sense! Thanks so much for the help! – Keeday Apr 22 '22 at 20:38

Excel VBA - Set timer goes faster after each loop

1 Answers1