CODEWITHSUNDEEP
excelvba
user10731314
user10731314

Reputation: 41

VBA time difference in seconds

I want to make an activity to happen only when there is minimum 60 seconds time gap between two successive activities. I have the below code:

Sub timediff()
    Dim Psos As New Collection
    Dim currtime As Date
    Dim dummytime As Date
    Dim diffoftime As Long

    If Psos.Count = 0 Then
        dummytime = "9:00:00"
        Psos.Add dummytime
    End If

    currtime = Format(Now(), "hh:mm:ss")
    diffoftime = DateDiff("s", CDate(Psos.Item(Psos.Count)), CDate(currtime))

    If diffoftime > 60 Then
        Debug.Print diffoftime
        Psos.Add currtime
    End If
End Sub

But I'm always getting the time difference from 9:00:00 to current time instead of time difference between current time and latest added collection item. Would anyone suggest the issue.

Upvotes: 0

Views: 7447

Answers (2)

Dominique
Dominique

Reputation: 17565

Why don't you simply take the difference between two results of the Now() worksheet function, and multiply it with 86400, without any formatting?

Now() gives the datetime of now, where units are days. As there are 86400 seconds within one day, the multiplication of the difference with 86400 will give you the amount of seconds.

Upvotes: 1

drgs
drgs

Reputation: 398

Every time you call timediff, Psos is reset to empty... Not sure why a collection is needed, just make a global Date variable and compare to it, or do everything in one procedure:

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

Public Sub OneMinuteLoop()
    Dim dtmNow As Date
    Dim dtmLastUpdate As Date

    Do While True
        dtmNow = Now()
        If DateDiff("s", dtmLastUpdate, dtmNow) > 30 Then
            'Do something
            dtmLastUpdate = dtmNow
        End If
        Sleep 5000
        DoEvents
    Loop
End Sub

Upvotes: 3

Related Questions