Run Time error 6 Overrun in vba for excel

Question

I'm running the following program, and get an overflow error at the first iteration once the program gets to a. From my understanding this shouldn't happen because I'm using Doubles which have a ridiculous amount of capacity, and a Long, but because it's only going up to 100 currently it shouldn't matter, and the code fails long before then. Here is my original inputs:

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

and here's the code:

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

Hope this is an easy fix.

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

You have already identified what needs to be done.

Now for the explanation on why the overflow on m * g - b * (v ^ 2). I added a watch as shown below

Consider the before and after screenshot

Before v = v0 + a0 * dt is calculated

After v = v0 + a0 * dt is calculated

What you see here is a very peculiar behaviour. A Type Double changing to an Integer

This is not normal and has been experienced before. Unfortunately this problem exists in Excel for a long time and unfortunately is also present in Excel 2016. It occurs only when you are using very large numbers. These kind of bugs are very rare, but yes, they do exist.

One can have a similar kind of experience when using MOD as mentioned in the below link

The MOD Function

Answer 2

The variable in question that is getting an overflow is v. You defined it as a double, which means you can only put numbers in the range of 1.79769313486232 * 10^308 to 4.94065645841247 * 10^–324. VBA uses 8 bytes to hold the double data. Any number outside of this huge range causes an overflow. (see here for more information about DBA data types)

How big of a number are you expecting? A rough estimate fo the number of atoms in the universe is 1 * 10^82, and the double value range goes to almost 2 *10^308. If you need to work with numbers that large, you're going to have to use a different method because it simply won't fit in the standard VBA data types.