VBA：四舍五入到最接近的数字倍数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18678106/
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
VBA: Round up to the nearest multiple of a number
提问by HK1
This question has already been asked for the C++ languagebut I need a function for VBA. I tried converting the C++ function to VBA but it doesn't return the right values.
这个问题已经被问到 C++ 语言，但我需要一个 VBA 函数。我尝试将 C++ 函数转换为 VBA，但它没有返回正确的值。
I need a function that does the following:
我需要一个执行以下操作的函数：
RoundUp(23.90, 5)
'return 25
RoundUp(23.90, 10)
'return 30
RoundUp(23.90, 20)
'return 40
RoundUp(23.90, 50)
'return 50
RoundUp(102.50, 5)
'return 105
RoundUp(102.50, 20)
'return 120
Here's what I have so far. It works most of the time but returns incorrect values for numbers that are less than .5 less than the multiple. So the problem seems to be a rounding problem with how I'm calculating the remainder value.
这是我到目前为止所拥有的。它在大多数情况下都有效，但对于比倍数小 0.5 的数字返回不正确的值。所以这个问题似乎是我如何计算余数的一个舍入问题。
Public Function RoundUp(dblNumToRound As Double, lMultiple As Long) As Double
Dim rmndr As Long
rmndr = dblNumToRound Mod lMultiple
If rmndr = 0 Then
RoundUp = dblNumToRound
Else
RoundUp = Round(dblNumToRound) + lMultiple - rmndr
End If
End Function
For Example:
例如：
RoundUp(49.50, 50)
'Returns 49.50 because rmndr = 0
回答by sehe
I'd simply divide by the lMultiple, round up and multiply again.
我只是简单地除以lMultiple，向上取整并再次相乘。
Assuming you indeed always want to round up (also for negative numbers):
假设您确实总是想要四舍五入（也适用于负数）：
Public Function RoundUp(dblNumToRound As Double, lMultiple As Long) As Double
Dim asDec as Variant
Dim rounded as Variant
asDec = CDec(dblNumToRound)/lMultiple
rounded = Int(asDec)
If rounded <> asDec Then
rounded = rounded + 1
End If
RoundUp = rounded * lMultiple
End Function
I'm not actually a VBA programmer, so this might need a tweaked comma or two. However the important thing is:
我实际上不是 VBA 程序员，所以这可能需要调整一两个逗号。然而重要的是：
- Use Decimal (variant subtype) for precision
- Let VB do the math for you
- 使用 Decimal（变体子类型）来提高精度
- 让VB为你做数学
回答by Joe Sweeney
A far simpler solution is to add .5 to the number before rounding:
一个更简单的解决方案是在四舍五入之前将数字加 0.5：
1.1 -> Round(1.1+.5, 0) -> 2
