Runtime Error 6 Overflow

[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

[Wescotte]

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

[Harald Staff]

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

[George Nicholson]

Post by Wescotte
Dim i As Integer
Dim d As Double

Integer can be up to +/- 32,767
Double can be +/- 2,147,483,647

If Value is larger than 327.68 then

Post by Wescotte
i = CInt(CDbl(.Value) * 100)

will cause a numeric overflow. CInt can't return a value that large and i
can't contain it.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

Post by 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

[Wescotte]

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?

[George Nicholson]

eek!!

Post by George Nicholson
Double can be +/- 2,147,483,647

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,
--
George Nicholson

Remove 'Junk' from return address.

Post by George Nicholson
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?