Excel VBA毫秒转向下取整天数时溢出错误解决求助

问题根源 #

你遇到的溢出错误，核心原因是VBA默认把字面整数（比如60）识别为Integer类型，而Integer的最大值是32767。计算60*60*24时，结果是86400，远超Integer的上限，直接触发溢出。

解决方案 #

只需要让运算以更大范围的数值类型（比如Long）执行即可，有几种简单的实现方式：

方法1：给字面量添加Long类型后缀&

把其中一个参与运算的数字改成Long类型，强制整个运算用Long计算：

Public Function Floor(ByVal myValue As Variant) As Variant
    Floor = Int(myValue) - 1 * (Int(myValue) > myValue)
End Function
Sub Test()
    Dim MilSecs As Variant: MilSecs = 321049000
    Dim Seconds As Variant: Seconds = MilSecs / 1000
    ' 给60添加&，强制运算为Long类型
    Dim Days As Variant: Days = Floor(Seconds / (60& * 60 * 24))
    ' 修正WorksheetFunction.Floor的参数，同时用Long类型运算
    Days = Application.WorksheetFunction.Floor(Seconds / (60& * 60 * 24), 1)
    ' 同样给数字加&避免溢出
    Debug.Print 60& * 60 * 24
    Debug.Print "MilSecs: " & MilSecs & ", Seconds: " & Seconds & ", Days: " & Days
End Sub

方法2：直接使用常量86400

既然60*60*24的结果是固定的86400，直接写常量可以彻底避免运算溢出：

Public Function Floor(ByVal myValue As Variant) As Variant
    Floor = Int(myValue) - 1 * (Int(myValue) > myValue)
End Function
Sub Test()
    Dim MilSecs As Variant: MilSecs = 321049000
    Dim Seconds As Variant: Seconds = MilSecs / 1000
    Dim Days As Variant: Days = Floor(Seconds / 86400)
    Days = Application.WorksheetFunction.Floor(Seconds / 86400, 1)
    Debug.Print 86400
    Debug.Print "MilSecs: " & MilSecs & ", Seconds: " & Seconds & ", Days: " & Days
End Sub

额外修正点

你的原代码里有个明显错误：重复定义了Days变量，VBA不允许在同一个过程里重复声明同名变量，修正时要去掉其中一个Dim Days As Variant。

内容的提问来源于stack exchange，提问作者Roland