# VBA Rounding?

• ### Question

• two part question:

1. x=(a/b) how to round to two decimal places

2. y=c/d) how to roundup to next even multiple of 2

thanks,

Doug

Monday, July 31, 2017 12:05 PM

• x = Application.WorksheetFunction.Round(a / b, 2)

and

y = Application.WorksheetFunction.Ceiling_Math(c / d, 4)

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Monday, July 31, 2017 2:46 PM

### All replies

• x = Application.WorksheetFunction.Round(a / b, 2)

and

y = Application.WorksheetFunction.Ceiling_Math(c / d, 4)

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Monday, July 31, 2017 2:46 PM
• Nice quasar picture, which one is that?

Andreas.

```Private Sub Example_Multiple()
Debug.Print Multiple(2.5, 1)         '3
Debug.Print Multiple(-2.5, -2, -1)   '-4
Debug.Print Multiple(-2.5, 2)        '-2
Debug.Print Multiple(1.5, 0.1)       '1.5
Debug.Print Multiple(0.234, 0.01, 1) '0.24
End Sub

Static Function Multiple(ByVal Value As Variant, ByVal Factor As Variant, _
Optional ByVal UpDown As Integer = 0) As Variant
'Rounds Value to the smallest multiple of Factor
Dim R As Variant, G As Variant
G = Fix(Value / Factor)
R = Abs(Value / Factor - G)
If UpDown = 0 Then
'Round up or down
Multiple = G * Factor + IIf(R >= 0.5, Factor * Sgn(Value), 0)
ElseIf UpDown > 0 Then
'Round up
Multiple = G * Factor + IIf(R > 0, Factor * Sgn(Value), 0)
Else
'Round down
Multiple = G * Factor - IIf(R > Factor, Factor * Sgn(Value), 0)
End If
End Function

Private Sub Example_Round()
Dim Numbers, Number
Numbers = Array( _
-2.6, -2.5, -2.4, -1.6, -1.5, -1.4, -0.6, -0.5, -0.4, _
0.4, 0.5, 0.6, 1.4, 1.5, 1.6, 2.4, 2.5, 2.6)
Debug.Print "Number", "VBA", "Up/Down", "Up", "Down"
For Each Number In Numbers
Debug.Print Number, _
VBA.Round(Number, 0), Round(Number, 0, 0), _
Round(Number, 0, 1), Round(Number, 0, -1)
Next
End Sub

Static Function Round(ByVal Value As Variant, _
Optional ByVal Places As Integer = 0, _
Optional ByVal UpDown As Integer = 0) As Variant
'VBA.Round performs banker's rounding, means Round(1.5) = 2 and Round(2.5) = 2
'http://support.microsoft.com/kb/196652/en-us
'https://support.microsoft.com/en-us/kb/78113
'This function performs a symmetric arithmetic up/down rounding
Dim i As Long
Dim Pot10(-28 To 28) As Variant
Dim Temp As Variant
If i = 0 Then
For i = LBound(Pot10) To UBound(Pot10)
Pot10(i) = CDec(10 ^ i)
Next
End If
If UpDown = 0 Then
'Round up or down
Round = Fix(Value * Pot10(Places) + 0.5 * Sgn(Value)) / Pot10(Places)
ElseIf UpDown > 0 Then
'Round up
Temp = Fix(Value * Pot10(Places))
Round = (Temp + IIf(Value = Temp, 0, Sgn(Value))) / Pot10(Places)
Else
'Round down
Round = Fix(Value * Pot10(Places)) / Pot10(Places)
End If
End Function
```

Monday, July 31, 2017 2:47 PM
• thanks,

lots of good info - very helpful!!

not sure on the Quasar...just like the pic

Thursday, August 3, 2017 11:30 AM