VBA:四舍五入到数字的最接近倍数 [英] VBA: Round up to the nearest multiple of a number

问题描述

此有关C ++的问题语言，但我需要VBA函数.我尝试将C ++函数转换为VBA，但没有返回正确的值.

This question has already been asked for the C++ language but I need a function for VBA. I tried converting the C++ function to VBA but it doesn't return the right values.

我需要一个执行以下操作的函数:

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

这是我到目前为止所拥有的.它在大多数情况下都有效，但是对于小于倍数的.5的数字，返回错误的值.因此，问题似乎是如何计算余数的舍入问题.

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.

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

例如:

RoundUp(49.50, 50)
'Returns 49.50 because rmndr = 0

推荐答案

我只需除以lMultiple，四舍五入并再次相乘即可.

I'd simply divide by the lMultiple, round up and multiply again.

假设您确实总是想四舍五入(对于负数也是如此):

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

我实际上不是VBA程序员，因此这可能需要调整一个或两个逗号.但是重要的是:

I'm not actually a VBA programmer, so this might need a tweaked comma or two. However the important thing is:

• 使用十进制(变量子类型)以提高精度
• 让VB为您做数学

这篇关于VBA:四舍五入到数字的最接近倍数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！