Link to home
Start Free TrialLog in
Avatar of Bryce Bassett
Bryce BassettFlag for United States of America

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.
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

Open in new window

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!
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

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
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Bryce Bassett

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

Open in new window

How do I need to modify this code?
Neeraj, changing x to a double did the trick.  Problem solved.   Thanks!
You're welcome Bryce! Glad it worked. 👍