在 VBA 中四舍五入到最接近的更高整数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35746905/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Rounding up to nearest higher integer in VBA
提问by Andrew Perry
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).
我正在尝试计算商品将堆放多少层。我有一个可变数量 ( iQty)、给定的装载台宽度 ( dRTW)、商品的单位宽度 ( dWidth) 和每层的数量 ( iLayerQty)。
The quantity per layer is calculated as iLayerQty = Int(dRTW/dWidth)
每层的数量计算为 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:
现在我需要将总量除以每层的数量并向上取整。在 Excel 公式中，这很容易，但我试图避免调用 WorksheetFunction 以最大程度地减少 A1/R1C1 混淆。目前我正在用这个来近似它：
(Number of layers) = ((Int(iQty / iLayerQty) + 1)
And that works fine mostof 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.
这在大多数情况下都可以正常工作- 除非数字给出整数（例如，货物宽度为 0.5 m，适合安装在 2.5 m 的滚动拖车上）。在这些情况下，当然，添加一个会破坏结果。
Is there any handy way of tweaking that formula to get a better upward rounding?
是否有任何方便的方法可以调整该公式以获得更好的向上舍入？
回答by D_Bester
I don't see any reason to avoid WorksheetFunction; I don't see any confusion here.
我看不出有任何理由避免WorksheetFunction；我在这里看不到任何混乱。
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)
回答by Andrew Perry
If using a WorksheetFunction objectto access a ROUNDUPor CEILINGfunction is off the table then the same can be accomplished with some maths.
如果使用WorksheetFunction 对象来访问ROUNDUP或CEILING函数不在表中，那么同样可以通过一些数学来完成。
Number of layers = Int(iQty / iLayerQty) - CBool(Int(iQty / iLayerQty) <> Round(iQty / iLayerQty, 14))
A VBA Trueis the equivalent of (-1) when used mathematically. The VBA Round is there to avoid 15 digit floating point errors.
在数学上使用时，VBA True等效于 (-1)。VBA Round 是为了避免 15 位浮点错误。
