Test for fractions of a penny

[Wescotte]

I'm attempting to write code to determine if the user entered a value
that has a fraction of a cent. Here is my logic

dim i as long
dim d as double

If IsNumeric(.Value) = True And .Value <> 0 Then
.Value = Abs(.Value)
i = CLng(CDbl(.Value) * 100)
d = CDbl(.Value) * 100
If Abs(d - i) > 0 Then
MsgBox "Rounding error detected! Make sure you don't have umbers
with values less than 1/100th", vbExclamation
End If
Else
.Value = ""
End If

It seems to work for most cases however I'd found some specific values
that always report "Round error detected!...."

Values such as 282.47, 32.41 and 132.70

If I MsgBox i & " " & d the numbers are the same so
Abs(d - i) > 0 should always be false I don't quite understand what's
doing on. Is there a better way to test for fractions of a cent?

Eric

 

[Guest]

Eric,

How about multiplying the test value by 100, subtract the integer portion
and test whats left against zero.

eg: 100.47 x 100 = 10047
10047 -int(10047) = 0 ' the value is ok

100.472 x 100 = 10047.2
10047.2 - int(10047.2) = .2 ' user enter a fraction of a cent

Roy

 

[Jim Cone]

Eric,

How about just forcing the numbers to two decimal places?
'-------------------------
Sub xxx()
Dim dblNum As Double
dblNum = 282.47123

If IsNumeric(dblNum) = True And dblNum <> 0 Then
dblNum = Abs(WorksheetFunction.Round(dblNum, 2))
MsgBox dblNum
Else
MsgBox "Your entry should be a number greater than 0. "
End If
End Sub
'-----------------------

Regards,
Jim Cone
San Francisco, USA


I'm attempting to write code to determine if the user entered a value
that has a fraction of a cent. Here is my logic

dim i as long
dim d as double

If IsNumeric(.Value) = True And .Value <> 0 Then
.Value = Abs(.Value)
i = CLng(CDbl(.Value) * 100)
d = CDbl(.Value) * 100
If Abs(d - i) > 0 Then
MsgBox "Rounding error detected! Make sure you don't have umbers
with values less than 1/100th", vbExclamation
End If
Else
.Value = ""
End If
It seems to work for most cases however I'd found some specific values
that always report "Round error detected!...."
Values such as 282.47, 32.41 and 132.70
If I MsgBox i & " " & d the numbers are the same so
Abs(d - i) > 0 should always be false I don't quite understand what's
doing on. Is there a better way to test for fractions of a cent?
Eric

 

[Wescotte]

That's what this code does..

i = CLng(CDbl(.Value) * 100)
d = CDbl(.Value) * 100
If Abs(d - i) > 0 Then
' Entered value has a fraction of a cent

Try 282.47
if I add MsgBox i & " " & d & " " & .Value & " " & Abs(d - i) just
after the d = line above

the results are
28247 28247 282.47 3.6379788709171E-12

so basically Double(282.47 * 100) - Long(282.47 * 100) != 0

Not sure how to get around thing correctly..

 

[Wescotte]

I thought of that but I need to keep the correct value in the cell and
just not let the user advance until they correct it manually because if
I just round everything it will cause a headache trying to figure out
where your extra penny went/came from.

 

[Jim Cone]

Eric,

Why not enter the rounded number into the cell?

Jim Cone

I thought of that but I need to keep the correct value in the cell and
just not let the user advance until they correct it manually because if
I just round everything it will cause a headache trying to figure out
where your extra penny went/came from.

 

[Jim Cone]

Eric,

Also suggest you review this article...
http://www.cpearson.com/excel/rounding.htm

Regards,
Jim Cone
San Francisco, USA


I thought of that but I need to keep the correct value in the cell and
just not let the user advance until they correct it manually because if
I just round everything it will cause a headache trying to figure out
where your extra penny went/came from.

 

[okaizawa]

Hi,
how about this:

If CStr(CLng(.Value * 100) / 100) <> CStr(.Value) Then