Calculation Problem

  • Thread starter Thread starter Derek
  • Start date Start date
D

Derek

I am using the worksheet_change event (EXCEL 97) to make
sure that the user types in numbers to no more than the
hundredths decimal place. If they type in a number like:

4.57 - entry is OK
4.572 - entry is bad

If the entry is bad I change the font to bold.

The code is as follows:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If Int(.Value * 100) <> .Value * 100 Then
.Font.Bold = True
Else
.Font.Bold = False
End If
End With
End Sub

The code appears to work for most cases, but for certain
numbers like 2.32 and 4.56 it turns the values bold (even
though it should not). I am going NUTZ. Help would be
much appreciated...
 
It sounds like a binary conversion problem. You could try using the Len and
InStr functions to determine how many numbers there are after the decimal.

HTH, Greg
 
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
if Instr(.Value,".") = 0 then
.FontBold = False
else
If len(.value)-instr(.Value,".") > 2 Then
.Font.Bold = True
Else
.Font.Bold = False
End If
End if
End With
End Sub
 
Your solution seems to work nicely. I was also able to
correct the problem by comparing strings instead of
numbers. Was there a flaw in my original logic, or is do
you think it is just a bug in Excel 97? When I stepped
through the code I was getting 2.32 x 100 = 231 (instead
of the expected 232). Thanks for your help.
 
I believe it has to do with the way computers store floating point numbers.

Just like you can't precisely represent 1/3 as a decimal number, many
numbers can not be represented exactly in binary. Since you truncate the
number, if it is slightly less that 2.32 (2.3199999999999), you get 231.
 
Back
Top