vba ceil code example

Example 1: 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 2: 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.