Clever Geek Handbook

Runtime error 6 Overflow in vba for excel

I run the following program and get an overflow error on the first iteration when the program gets to a

. In my opinion, this shouldn't happen, because I am using paired pairs that have a ridiculous amount of possibilities and a long one, but since it only goes up to 100, it doesn't matter at present and the code doesn't fire long before that. Here are 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 solution.

+3


source to share


2 answers


Your overflow occurs when i = 14 (the 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 explodes. It helps if you describe what you are trying to do. - John Coleman 17 mins ago

@ John Coleman I see the problem now, I am doing the equation of resistance and I forgot to divide the term by m, thanks. - Anthrochange 9 min ago

You have already determined what needs to be done.

Now for an explanation of the reasons for the overflow on m * g - b * (v ^ 2)

. I have added a clock as shown below.

Consider the before and after screenshot

Calculated v = v0 + a0 * dt

enter image description here



After v = v0 + a0 * dt

calculating

enter image description here

What you see here is a very peculiar behavior. A Type Double

changing toInteger

This is not normal and it used to be. Unfortunately, this problem has existed in Excel for a long time and, unfortunately, is also present in Excel 2016. This only happens when you use very large numbers. Such errors are very rare, but yes, they do exist.

When using, MOD

you can use a similar experience as mentioned in the link below

MOD function

+5


source


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



How big is the number you expect? A rough estimate of the number of atoms in the universe is 1 * 10 ^ 82, and the range of the double value is almost 2 * 10 ^ 308. If you need to work with large numbers, you will have to use different ones because it just won't fit the standard VBA data types.

+1


source






More articles:


All Articles