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?

回答1:

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)

回答2:

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.

回答3:

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