Bryce Bassett
asked on
Apparent Rounding Function Error in Excel VBA
I've having trouble with a rounding routine in Excel. The goal is when the user enters a number with the wrong number of significant digits, the macro rounds the number and replaces what they entered. It does not merely change the cell display to show the desired number of digits, it actually corrects the number entered and updates the cell.
I am seeing some very strange results where sometimes the macro works and other times it does not. Please enter the code below in a blank Excel workbook and try this out.
5 - works
5.54 - correctly changes to 5.5
5.45 - appears to work, but the number in the cell is now 5.40000009536743
5.454545 - correctly changes to 5.5
5.656565 - appears to work, but the number in the cell is now 5.69999980926513
6.899 - appears to work, but the number in the cell is now 6.90000009536743
Etc. I can't find the pattern!
I've got a number of possible rounding values, hence the select case, but for this example I have just provided one. I've followed the steps in my rounding algorithm and they appear to be working correctly. It's gotta be a math thing. Where are these crazy long values coming from, and how do I get this to work correctly?
Appreciate any help!
I am seeing some very strange results where sometimes the macro works and other times it does not. Please enter the code below in a blank Excel workbook and try this out.
Private Sub Worksheet_Change(ByVal target As Range)
If target.Cells.Count > 1 Then Exit Sub
If target.Value = "" Then Exit Sub
If Not (IsNumeric(target)) Then
MsgBox "please enter a number"
target.Value = ""
target.Select
Exit Sub
End If
Call roundit(target, 0.1)
End Sub
Sub roundit(ByVal target As Range, rez As Single)
Dim x As Single
Application.EnableEvents = False
Select Case rez
Case 0.1
x = Round(target * 10, 0) / 10
target.NumberFormat = "0.0"
MsgBox "Rounded value of " & target & " to the nearest " & rez & " is " & x
target = x
End Select
Application.EnableEvents = True
End Sub
Try entering these test values:5 - works
5.54 - correctly changes to 5.5
5.45 - appears to work, but the number in the cell is now 5.40000009536743
5.454545 - correctly changes to 5.5
5.656565 - appears to work, but the number in the cell is now 5.69999980926513
6.899 - appears to work, but the number in the cell is now 6.90000009536743
Etc. I can't find the pattern!
I've got a number of possible rounding values, hence the select case, but for this example I have just provided one. I've followed the steps in my rounding algorithm and they appear to be working correctly. It's gotta be a math thing. Where are these crazy long values coming from, and how do I get this to work correctly?
Appreciate any help!
Format the column (or row or cells) as Numeric with 2 characters after the digit. You can then work with cell(r,c).Text.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks. But I must not be implementing it correctly because I can't get that to work either. The number in the cell must have only one significant digit, as displayed and in fact.
Select Case rez
Case 0.1
x = Round(target * 10, 0) / 10
MsgBox "Rounded value of " & target & " to the nearest " & rez & " is " & x
target.NumberFormat = "0.00"
target.Text = x
target.NumberFormat = "0.0"
End Select
How do I need to modify this code?
ASKER
Neeraj, changing x to a double did the trick. Problem solved. Thanks!
You're welcome Bryce! Glad it worked. 👍