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) — Cyril
– Cyril, Commented Apr 21, 2022 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: stackoverflow.com/a/23949000/3688861 stackoverflow.com/a/20272781/3688861 — Tragamor
– Tragamor, Commented Apr 21, 2022 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
– Keeday, Commented Apr 22, 2022 at 7:11

Tim Williams · Accepted Answer · 2022-04-22 16:07:50Z

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, 2022 at 16:07

answered Apr 21, 2022 at 15:36

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Keeday Over a year ago

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?

Tim Williams Over a year ago

I'd replaced use of tCell with the With block and forgotten to remove it before posting. Edited to remove...

Keeday Over a year ago

Makes sense! Thanks so much for the help!

Collectives™ on Stack Overflow

Excel VBA - Set timer goes faster after each loop

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related