CODEWITHSUNDEEP
vbaexcel
Andrew Perry
Andrew Perry

Reputation: 765

Rounding up to nearest higher integer in VBA

I'm trying to calculate how many layers a commodity will be stacked in. I have a variable quantity (iQty), a given width for the loadbed (dRTW), a width per unit for the commodity (dWidth) and a quantity per layer (iLayerQty).

The quantity per layer is calculated as iLayerQty = Int(dRTW/dWidth)

Now I need to divide the total quantity by the quantity per layer and round up. In an Excel formula it would be easy, but I'm trying to avoid WorksheetFunction calls to minimise A1/R1C1 confusion. At the moment I'm approximating it with this:

(Number of layers) = ((Int(iQty / iLayerQty) + 1)

And that works fine most of the time - except when the numbers give an integer (a cargo width of 0.5 m, for instance, fitting onto a 2.5 m rolltrailer). In those instances, of course, adding the one ruins the result.

Is there any handy way of tweaking that formula to get a better upward rounding?

Upvotes: 10

Views: 37921

Answers (5)

Andrea Omboni
Andrea Omboni

Reputation: 1

This is my Ceiling in VBA:

Function Ceiling(ByVal Number As Double, ByVal Significance As Double) As Double
    
    Dim intVal As Long
    Dim delta As Double
    Dim RoundValue As Double
    Dim PreReturn As Double
    If Significance = 0 Then
        RoundValue = 1
    Else
        RoundValue = 1 / Significance
    End If
    Number = Number * RoundValue
    
    
    intVal = CLng(Number)
    delta = intVal - Number
    
    If delta < 0 Then
        PreReturn = intVal + 1
    Else
        PreReturn = intVal
    End If
    Ceiling = PreReturn / RoundValue

End Function

Upvotes: 0

PJ_Vale
PJ_Vale

Reputation: 1

These are the functions I put together for this purpose.

Function RoundUp(ByVal value As Double) as Integer
    
    Dim intVal As Integer
    Dim delta As Double
       
    intVal = CInt(value)
    delta = intVal - value

    If delta < 0 Then
        RoundUp = intVal + 1
    Else
        RoundUp = intVal
    End If

End Function

Function RoundDown(ByVal value As Double) as Integer

    Dim intVal As Integer
    Dim delta As Double

    intVal = CInt(value) 
    delta = intVal - value

    If delta <= 0 Then
        RoundDown = intVal
    ElseIf delta > 0 Then
        RoundDown = intVal - 1   
    End If

End Function

Upvotes: 0

Marc Meketon
Marc Meketon

Reputation: 2695

I use -int(-x) to get the ceiling.

?-int(-1.1)  ' get ceil(1.1)
2

?-int(1.1)   ' get ceil(-1.1)
-1

?-int(-5)    ' get ceil(5)
5

Upvotes: 12

user4039065
user4039065

Reputation:

If using a WorksheetFunction object to access a ROUNDUP or CEILING function is off the table then the same can be accomplished with some maths.

Number of layers = Int(iQty / iLayerQty) - CBool(Int(iQty / iLayerQty) <> Round(iQty / iLayerQty, 14))

A VBA True is the equivalent of (-1) when used mathematically. The VBA Round is there to avoid 15 digit floating point errors.

Upvotes: 7

D_Bester
D_Bester

Reputation: 5921

I don't see any reason to avoid WorksheetFunction; I don't see any confusion here.

Number_of_layers = WorksheetFunction.RoundUp(iQty / iLayerQty, 0)

You could also roll your own function:

Function RoundUp(ByVal Value As Double)
    If Int(Value) = Value Then
        RoundUp = Value
    Else
        RoundUp = Int(Value) + 1
    End If
End Function

Call it like this:

Number_of_layers = RoundUp(iQty / iLayerQty)

Upvotes: 20

Related Questions