round number up to the nearest 1/10th

[kskinne]

I apologize if this has already been covered in another threat, but I need a number to always round up to the nearest 1/10th using VB6 - for example:

1.2 = 1.2, no change - already rounded to nearest 10th
1.2222 = 1.3
1.29 = 1.3
1.2000001 = 1.3

Thank you,
Kevin

 

[kskinne]

I should mention what I am doing right now is setting a reference to the Microsoft Excel object library, then using

application.worksheetfunction.ceiling(mynumber, 0.1)

to round to the nearest 1/10th. This works but I'd like to get away from using this, in case a machine that doesn't have MS Office is using the application.

Thanks,
Kevin

 

[gmmastros]

try....

round(YourNumber + 0.05, 1)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kskinne]

Thank you George, however this function takes numbers that are already rounded to the nearest 10th, and rounds them up again, which will not work for me - for example:

0 becomes 0.1
0.1 becomes 0.2
0.9 becomes 1.0
etc.

Any ideas on how to avoid that?

Thanks,
Kevin

 

[Andrzejek]

I'm sure there is a better way, but try:

[pre]
Private Function RoundMe(ByRef sngNo As Single) As Single

If Len(CStr(sngNo)) > 3 Then
RoundMe = Round(sngNo + 0.05, 1)
Else
RoundMe = sngNo
End If

End Function
[/pre]

Have fun.

---- Andy

 

[SkipVought]

maybe...

Private Function RoundMe(ByRef sngNo As Single) As Single
    Dim a
    
    a = sngNo * 10
    
    If a <> Int(a) Then
        RoundMe = Round(sngNo + 0.05, 1)
    Else
        RoundMe = sngNo
    End If
End Function

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[strongm]

>and rounds them up again

Actually ... it depends. The VBA Round function performs Banker's Rounding. So George's function will indeed round 0.9 to 1.0 - but it'll round 0.8 to 0.8

 

[strongm]

Should have finished that off with the comment that Microsoft have produced examples of a number of different rounding functions in

http://support.microsoft.com/kb/196652,

although I don't think any of them quite do what you want, since you are indeed looking for a ceiling function. Fortunately there is a FAQ in the Access forum (faq705-5031) that provides both a ceiling and a floor function that produce the same results as the Excel functions

 

[gmmastros]

I noticed this earlier. I wonder how it compares to the code in FAQ. (unfortunately, I don't have time to test).

http://mario-reynoso.blogspot.com/2009/01/ceiling-y-floor-en-vb6.html

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[strongm]

Test? A quick glance shows that they are pure integer ceiling and floor functions, whilst the Excel (and the FAQ) functions work to an arbitrary decimal precision

 

[xwb]

Which way do you want to round the negative numbers or do you not get any negative numbers?

 

[macropod]

Try something based on:

Dim i As Single
i = InputBox("input number")
MsgBox -Int(--Int(-i * 100) / 10) / 10

Cheers
Paul Edstein
[MS MVP - Word]

 

[strongm]

As with George's original solution, this suffers the same problem of rounding 1.2 to 1.3 or 0.1 to 0.2 ...

 

[macropod]

OK, try:

Sub Demo()
Dim MyVal As Double, Precision As Integer
MyVal = CDbl(InputBox("Value to Round Up"))
Precision = CInt(InputBox("Round up Precision"))
MsgBox Roundup(MyVal, Precision)
End Sub

Function Roundup(MyVal As Double, Precision As Integer) As Single
If Int(MyVal * 10 ^ Precision) = (MyVal * 10 ^ Precision) Then
  Roundup = MyVal
Else
  Roundup = -(Int(-MyVal * 10 ^ Precision) / 10 ^ Precision)
End If
End Function

Cheers
Paul Edstein
[MS MVP - Word]