Runtime Error 6 Overflow

  • Thread starter Thread starter Wescotte
  • Start date Start date
W

Wescotte

I have the following code.. It's producing a Runtime error 6 overflow.
I can't get it to produce it every time but generally it will fail when
entering about 10 values in the F:12 or > cells. Also sometimes after
selecting a cell and hitting hte delete key it will force it to 0.00
and will stay that way until a new value is entered. Any ideas?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim i As Integer
Dim d As Double

Application.EnableEvents = False
With Target(1)

' Make sure Debits are postive values
If Not Intersect(.Cells, Range("F12",
Range("F12").End(xlDown))) Is Nothing Then
If IsNumeric(.Value) = True Then
.Value = Abs(.Value)
i = CInt(CDbl(.Value) * 100) ' This line is where the
error is reported from
d = CDbl(.Value) * 100
If Abs(d - i) > 0 Then
MsgBox "Rounding error detected! Make sure you
don't have numbers with values less than 1/100th", vbExclamation
End If
Else
.Value = ""
End If
End If
End With
Application.EnableEvents = True
End Sub
 
One other thing to note.. The code is designed to take all non numbers
and just empty the contents of the cell but that doesn't seem to
function either..

The If IsNumeric(.Value) = True doesn't ever seem to be false
 
Hi

Integer can't keep more than 32k before going mad. Dim i as something
bigger, like Long, and see if it helps.

HTH. Best wishes Harald
 
Dim i As Integer
Dim d As Double
Click to expand...
Integer can be up to +/- 32,767
Double can be +/- 2,147,483,647

If Value is larger than 327.68 then
i = CInt(CDbl(.Value) * 100)
Click to expand...
will cause a numeric overflow. CInt can't return a value that large and i
can't contain it.

HTH,
 
Wow, I remeber back in the old days coding with GW Basic int's only
being 2 bytes but I just assumed VB was 4. Is a long 4 bytes?
 
eek!!
Double can be +/- 2,147,483,647
Click to expand...
should have read
Long can be +/- 2,147,483,647
(Double can pretty much be whatever it wants to be.)
Very sorry.

Byte is 1 Byte (d'oh)
Integer is 2 byte
Long is 4 byte

Single is 4 byte
Double is 8 byte

HTH,
 
Back
Top