vba round up code example

Example 1: vba round number

Function RoundTo(pNumber As Long, pFactor As Long)
    RoundTo = Round(pNumber / pFactor) * pFactor 
End Function
' ---------------------------------------------------------------
Sub TestMe()
    Debug.Print RoundTo(2543, 10)    			' => 2540
    Debug.Print RoundTo(2546, 10)    			' => 2550
    Debug.Print Application.RoundUp(10.1, 0) 	' => 10
    Debug.Print Application.RoundUp(10.6, 0) 	' => 11
    Debug.Print Application.RoundDown(10.6, 0) 	' => 10
End Sub

Example 2: excel vba ceiling function

'VBA does NOT have a native Ceil() function which is found in many
'programming languages. Ceil() should round up to the next whole
'number unless the value is already EXACTLY a whole number. The most 
'performant way to accomplish this in VBA is with this custom
'function:

Function Ceil&(n#)
    Ceil = -Int(-n)
    If n < 0 Then Ceil = Fix(n)
End Function

'---------------------------------------------------------------------
  
MsgBox Ceil(5.000000000000001)     '<--displays: 6
MsgBox Ceil(5.999999999999999)     '<--displays: 6
MsgBox Ceil(5.000000000000000)     '<--displays: 5
  
'Note: This method is much faster than using the RoundUp() function.

Example 3: excel vba floor function

'VBA does NOT have a native Floor() function which is found in most
'programming languages. Floor() should round down to the next whole
'number. However, VBA does have the Fix() function which does exactly
'the same thing:
 
MsgBox Fix(5.000000000000001)     '<--displays: 5
MsgBox Fix(5.999999999999999)     '<--displays: 5
MsgBox Fix(5.000000000000000)     '<--displays: 5
  
'Note: This method is much faster than using the RoundDown() function.