午夜计时 [英] Time counting over midnight

问题描述

午夜计时失败

午夜计时失败
我用两个计数器编写了一个代码-一个（CountUPS）从0开始计数秒每次我启动程序（A1）和一个（CountUP），它都会从预设时间（A2）开始计时。
它可以在同一天内进行计数，但是可以正常工作，但是每次计数到午夜时都会出现错误。当A2及时到达23:37:53时，它将停止。
我的值定义有问题吗？

Counting time over midnight fails I have made a code with two counters - one (CountUPS) that counts seconds from 0 every time I start the program (A1) and one(CountUP) that counts seconds from a preset time (A2). It works fine while it counts within the same day, but it gets a bug every time it shall count over midnight. It stops when A2 reach 23:37:53 in time. Is something wrong in my definition of values?

Sub RunMe()

Dim StartS As Single
Dim CellS As Range
Dim Cellt As Range
Dim CountUPS As Date
Dim CountUp As Date

'Timer is the number of seconds since midnight.
'Store timer at this point in a variable
StartS = Timer

'Store A1 in a variable to make it easier to refer
'to it later. Also, if the cell changes, you only
'have to change it in one place
Set CellS = Sheet1.Range("A1")

'This is the starting value.
CountUPS = TimeSerial(0, 0, 0)

'Set our cell to the starting value
CellS.Value = CountUPS

Set Cellt = Sheet1.Range("A2")
CountUp = Sheet1.Range("A2")

b_pause = True

Do While CellS.Value >= 0

    CellS.Value = CountUPS + TimeSerial(0, 0, Timer - StartS + (StartS > Timer))
    Cellt.Value = CountUp + TimeSerial(0, 0, Timer - StartS + (StartS > Timer))
    DoEvents
Loop

End Sub

推荐答案

错误消息清除了很多问题。

The error messages clear up a lot of issues.

• 正如已经指出的那样，溢出错误是由于 TimeSerial 函数参数上的整数约束。


• 1004 错误发生在午夜，是因为当计时器恢复为 0 在午夜，您的秒表示为负数。除非您使用1904年日期系统，否则您将无法在Excel中表达负数。


• 在尝试调整通过午夜时，您需要将秒和天以及Excel和VBA混合在一起。您正在宏中尝试添加一秒钟，而您可能真的想添加一天。另外，由于VBA中的 True 等于 -1 ，因此实际上您是在减去而不是加！

• The overflow error, as has been pointed out, is because of the integer constraints on the TimeSerial function arguments.
• The 1004 error, occurring at midnight, is because, when Timer reverts to 0 at midnight, your expression for seconds is a negative number. Unless you are using the 1904 date system, you cannot express negative times in Excel.
• In trying to adjust for "passing midnight" you are conflating seconds and days, and Excel and VBA. You are, in your macro, trying to add one second, whereas you probably really want to add one day. Also, since True in VBA equates to -1, you are actually subtracting instead of adding anyway!

我认为以下修改可能有效，但尚未对其进行广泛的测试。 86400 是一天中的秒数。

I think the following modifications may work, but have not tested them extensively. 86400 is the number of seconds in a day.

Option Explicit
Sub RunMe()

Dim StartS As Single
Dim CellS As Range
Dim Cellt As Range
Dim CountUPS As Date
Dim CountUp As Date

'Timer is the number of seconds since midnight.
'Store timer at this point in a variable
StartS = TIMER

'Store A1 in a variable to make it easier to refer
'to it later. Also, if the cell changes, you only
'have to change it in one place
Set CellS = Sheet1.Range("A1")

'This is the starting value.
CountUPS = TimeSerial(0, 0, 0)

'Set our cell to the starting value
CellS.Value = CountUPS

Set Cellt = Sheet1.Range("A2")
CountUp = Sheet1.Range("A2")

'b_pause = True

Do While CellS.Value >= 0

    CellS.Value = CountUPS + (TIMER - StartS - 86400 * (StartS > TIMER)) / 86400
    Cellt.Value = CountUp + (TIMER - StartS - 86400 * (StartS > TIMER)) / 86400
    DoEvents
Loop

End Sub

这篇关于午夜计时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！