vba - excel 的 vba 中运行时错误 6 溢出

Question

我正在运行以下程序，当程序到达 a 时，我会在第一次迭代时收到溢出错误。根据我的理解，这种情况不应该发生，因为我使用的 Double 具有可笑的容量和 Long，但因为它目前仅达到 100，所以应该不重要，并且代码在此之前很久就失败了。这是我的原始输入:

h0 = 1000, v0 = 0, a0 = g = -9.80665, dt = .01, m = 752.2528, b = .287875

这是代码:

Sub drag()
    Dim h0 As Double
    Dim v0 As Double
    Dim a0 As Double
    Dim dt As Double
    Dim m As Double
    Dim b As Double
    Dim g As Double
    Dim i As Long
    Dim h As Double
    Dim v As Double
    Dim a As Double

    h0 = Worksheets("Drag").Cells(2, 8).Value
    v0 = Worksheets("Drag").Cells(2, 9).Value
    a0 = Worksheets("Drag").Cells(2, 10).Value
    g = Worksheets("Drag").Cells(2, 10).Value
    dt = Worksheets("Drag").Cells(2, 7).Value
    m = Worksheets("Drag").Cells(2, 4).Value
    b = Worksheets("Drag").Cells(2, 5).Value
    Debug.Print h0 & v0 & a0 & dt & m & b

    For i = 1 To 100
        v = v0 + a0 * dt
        h = 0.5 * a0 * (dt ^ 2) + v0 * dt + h0
        a = m * g - b * (v ^ 2)   'Line where overflow occurs
        v0 = v
        h0 = h
        a0 = a
        Cells(i + 2, 8) = h0
        Cells(i + 2, 9) = v0
        Cells(i + 2, 10) = a0
        Next i
    Debug.Print h0 & v0 & a0 & dt & m & b
End Sub

希望这是一个简单的修复。

Answer 1

Your overflow happens when i=14 (14th pass through the loop) and when v = -1.689 x 10^209. I don't know what you are trying to do, but v is blowing up. It would help if you describe what you are trying to do. – John Coleman 17 mins ago

@John Coleman I see the problem now, I'm doing the drag equation and I forgot to divide the a term by m, thanks. – Anthrochange 9 mins ago

您已经确定了需要做什么。

现在解释一下m * g - b * (v ^ 2) 上溢出的原因。我添加了一个 watch ，如下所示

考虑之前和之后的屏幕截图

计算v = v0 + a0 * dt之前

计算v = v0 + a0 * dt后

您在这里看到的是一种非常奇怪的行为。类型 Double 更改为 Integer

这是不正常的，以前也经历过。不幸的是，这个问题在 Excel 中存在很长时间，并且在 Excel 2016 中也存在。仅当您使用非常大的数字时才会出现此问题。此类错误非常罕见，但它们确实存在。

使用MOD时可以有类似的体验，如下面的链接所述

The MOD Function