Going totally bonkers - getting Run-time error '6' (Overflow)

[ss123]

I am totally confused here. I was well into a project and assigned a value of 14464 * 5 to a variable and got the run-time error 6 - Overflow. Hmm, oh yes, I dimensioned my variable as an Integer. No problems. Changed to a Long. Saved, re-ran the code. Error 6 again, overflow. Sorry, but this is getting stupid. The results of the above calculation is 72320. Well a Long variable has an upper range exceeding 2.147 million so as nuts as this sounds, this does not compute. At this point I was starting to get angry.

So I ran the following code in a new Workbook in a new subroutine.

Dim calcRez as Single
calcRez = 14464 * 1
Msgbox calcRez

Ran it, there was no problem as the result is 14464. Incremented the calculation by one to 14464 * 2. Again no problem because that result is 28928. Increment the calculation by one to 14464 * 3. Overflow error. What the <bleep>? The result of that calculation is 43992 and it is waaaaaaaaaaaaay under the billions in the upper bounds of the Single dimensioned variable. In fact, my original calculation of 14464 * 5 should have worked with a variable dimensioned as Long.

I am now beyond angry. What am I missing here? Is this a bug in VBA? If so, I am amazed I have never encountered it before. True, I have mainly been using Integers for numbers. Do you have to dimension a Long and a Single in different manner?

I did a search on that run-time error in the forum, but most people had improperly dimensioned or forgotten to declare their variables. Not so with me once I corrected for the Integer variable mistake.

 

[JoeMo]

My experience with Overflow error is that once it strikes a workbook it becomes persistent regardless of the correct 'fixes' being applied. Have you tried: applying the fixes, closing all open workbooks and restarting Excel?

 

[GWteB]

Your issue is caused by VBA's implicitness regarding numeric values directly used, rather than stored into variables. Numeric values are 2 byte integers by default***. When the boundaries are reached an overflow occurs even before the end result could be stored (and be converted to a long, single, currency, ...).

EDIT:
*** when they are within the range from -32,768 to 32,767

 

[ss123]

Wow, I think both GWteB and JoeMo solved this one on me. My original calculation was based on variables properly all dimensioned to Single (once the error had shown for the first time). I simplified the code to show exactly how insane the error was. My bad.

GWteB's solution did not work when I tried it. But, then following JoeMo's solution of closing down Excel and rebooting (for good measure), I opened a new sheet, saved it as a macro enabled sheet, added a new module and added this following code, it DID WORK. Thank you to both of you:

Sub onceMore()

Dim calcRez, messLenn, timesThru As Single

messLenn = 14464
timesThru = 5

calcRez = messLenn * timesThru
MsgBox calcRez

End Sub


JoMoe - I like your tag line. Readily applies to me...!

 

[Fluff]

That will work because you are using values that have been stored in variables. If you try your original code again, it will almost certainly fail.

 

[GWteB]

GWteB's solution did not work when I tried it.

I didn't give you a solution, just the cause of your issue: direct use of integer values instead of using variables. Closing your workbook or even quiting Excel has nothing to do with it.
Try for example :

Sub Integers()

    MsgBox 32766.5 + 1  ' Single
    MsgBox 32768 * 2    ' Long
    MsgBox 32767 + 1    ' Integer >> errors on overflow

End Sub

 

[ss123]

I think that reply from me: "GWteB's solution did not work when I tried it" should not have been posted. I thought I cancelled the screen. Sigh!

Before posting my thread, I did many steps of troubleshooting and got angrier and angrier with the constant Overflow errors. So to simplify the process, I switched to raw numbers instead of variables. Thus when I saw GWteB's post, I automatically started to type that it does not work because I had already tested for it before resorting to raw numbers before posting this thread. But then I thought maybe to check to see if JoeMo's advice might work. So I rebooted, did a whole new workbook, and then ran similar code to that above with the variables. It worked. Now mind you, I had been working for over 10 hours on various things on my PC and perhaps it needed a simple reboot to help it along. Like me, it is getting very long in the tooth... Again, thanks to the two of you.