Rounding Issue 2

[kashford]

I am trying to calculate a value to compare against a filed on our mainframe system.

I can get the calculation to work correctly, but I am unable to round the value to two decimal places, but I just found out that the editor does not support the Round function.

Does anyone have any ideas on how to get around this?

 

[IknowMe]

Does this work for you?

Sub main
  Dim value
  Dim msgtext
  value="12.5655"
  msgtext= str(value)+" Rounded : " & Format(Value,"fixed")
  MsgBox msgtext
End Sub

 

[Skie]

Here's a function that will round to any decimal (even 0). YourNumber is the number you want to round. intRound is the number of decimals you wish to round to.

Function Rounder(YourNumber,intRound)
    strYourNumber = Trim(Str(YourNumber))
    If InStr(strYourNumber,".") = 0 then strYourNumber = strYourNumber & "."
    strFront = Left(strYourNumber,InStr(strYourNumber,".")-1)
    strBack = Mid(strYourNumber,InStr(strYourNumber,".")+1,intRound+1)
    If Len(strBack) = intRound+1 then
        If intRound = 0 then
            intFront = int(strFront)
            If Int(strBack) > 4 then strFront = Trim(Str(intFront+1))
            strBack = ""
        Else
            intHundreths = Int(Left(strBack,intRound))
            If Int(Right(strBack,1)) > 4 then strBack = Trim(Str(intHundreths+1))
        End If
    End If
    While Len(strBack) < intRound
        strBack = strBack & "0"
    Wend
    YourNumber = Int(strFront & strBack)/(10^intRound)
    Rounder = YourNumber
End Function

You can test it with this:

Sub Main
    strNumber = InputBox("Input a number:","Numeric Rounder","")
    If IsNumeric(strNumber) = 0 then
        MsgBox "Your input was not numeric.",0,"Numeric Rounder - Error"
        Exit Sub
    End If
    MsgBox Rounder(strNumber,2),0,"Numeric Rounder"
End Sub

You'll probably want to use something like this:

Sub Main
    MyNumber = 123.4567       'However you get your numbers
    MyNumber = Rounder(MyNumber,2)
End Sub

 

[IknowMe]

Use Skie's example after testing

Sub main
Dim value
Dim msgtext
value="12.5655"
msgtext= str(value)+" Rounded : " & Format(Value,"fixed")
MsgBox msgtext
End Sub

12.56499 ends up as 12.56 so it is only rounding three spots behid the decimal.

 

[Skie]

You beat me by a minute and your way is easier.

 

[IknowMe]

I think it was less than a minute but yours is more useful

 

[Skie]

That rounds right. 12.56499 should be 12.56. If you wanted something that always round up you could use this:

Function RoundUp(YourNumber,intRound)
    strYourNumber = Trim(Str(YourNumber))
    If InStr(strYourNumber,".") = 0 then strYourNumber = strYourNumber & "."
    strFront = Left(strYourNumber,InStr(strYourNumber,".")-1)
    strBack = Mid(strYourNumber,InStr(strYourNumber,".")+1,intRound+1)
    If Len(strBack) = intRound+1 then
        If intRound = 0 then
            strFront = Trim(Str(int(strFront)+1))
            strBack = ""
        Else
            strBack = Trim(Str(Int(Left(strBack,intRound))+1))
        End If
    End If
    While Len(strBack) < intRound
        strBack = strBack & "0"
    Wend
    YourNumber = Int(strFront & strBack)/(10^intRound)
    RoundUp = YourNumber
End Function

 

[kashford]

This worked great. Thanks for the help.