如何在微软访问，VBA

Whats the best way to round in VBA Access?

在VBA访问中，最好的方法是什么?

My current method utilizes the Excel method

我目前的方法使用的是Excel方法

Excel.WorksheetFunction.Round(...

But I am looking for a means that does not rely on Excel.

但我正在寻找一种不依赖Excel的方法。

12 个解决方案

#1

Be careful, the VBA Round function uses Banker's rounding, where it rounds .5 to an even number, like so:

要小心，VBA的圆形功能使用了银行家的四舍五入，在那里，它会轮到一个偶数，就像这样:

Round (12.55, 1) would return 12.6 (rounds up) 
Round (12.65, 1) would return 12.6 (rounds down) 
Round (12.75, 1) would return 12.8 (rounds up)   

Whereas the Excel Worksheet Function Round, always rounds .5 up.

而Excel工作表函数则是四舍五入。

I've done some tests and it looks like .5 up rounding (symmetric rounding) is also used by cell formatting, and also for Column Width rounding (when using the General Number format). The 'Precision as displayed' flag doesn't appear to do any rounding itself, it just uses the rounded result of the cell format.

我已经做了一些测试，看起来.5的舍入(对称舍入)也被单元格格式化使用，也被用于列宽度舍入(当使用一般的数字格式时)。“Precision as display”标志似乎不做任何舍入操作，它只使用单元格格式的圆形结果。

I tried to implement the SymArith function from Microsoft in VBA for my rounding, but found that Fix has an error when you try to give it a number like 58.55; the function giving a result of 58.5 instead of 58.6. I then finally discovered that you can use the Excel Worksheet Round function, like so:

我试图在VBA中实现来自微软的SymArith函数来实现我的四舍五入，但是当你试图给它一个数字，比如58.55时，我发现Fix有一个错误;函数给出的结果是58.5，而不是58.6。然后我终于发现你可以使用Excel工作表圆函数，如下所示:

Application.Round(58.55, 1)

Application.Round(58.55,1)

This will allow you to do normal rounding in VBA, though it may not be as quick as some custom function. I realize that this has come full circle from the question, but wanted to include it for completeness.

这将允许您在VBA中执行正常的舍入，尽管它可能没有一些自定义函数那么快。我意识到这个问题已经绕了一个大圈子，但我想把它包含进去，作为完整性。

#2

To expand a little on the accepted answer:

在接受的答案上增加一点:

"The Round function performs round to even, which is different from round to larger."
--Microsoft

“圆函数从圆到偶数，这与圆到大是不同的。”——微软

Format always rounds up.

格式总是围捕。

  Debug.Print Round(19.955, 2)
  'Answer: 19.95

  Debug.Print Format(19.955, "#.00")
  'Answer: 19.96

ACC2000: Rounding Errors When You Use Floating-Point Numbers: http://support.microsoft.com/kb/210423

ACC2000:使用浮点数时的舍入错误:http://support.microsoft.com/kb/210423

ACC2000: How to Round a Number Up or Down by a Desired Increment: http://support.microsoft.com/kb/209996

ACC2000:如何通过一个期望的增量来增加或减少一个数字:http://support.microsoft.com/kb/209996。

Round Function: http://msdn2.microsoft.com/en-us/library/se6f2zfx.aspx

圆的函数:http://msdn2.microsoft.com/en-us/library/se6f2zfx.aspx

How To Implement Custom Rounding Procedures: http://support.microsoft.com/kb/196652

如何实现自定义舍入过程:http://support.microsoft.com/kb/196652

#3

In Switzerland and in particulat in the insurance industry, we have to use several rounding rules, depending if it chash out, a benefit etc.

在瑞士，尤其是在保险业，我们必须采用一些四舍五入的规则，这取决于它是否能带来好处等等。

I currently use the function

我目前使用这个函数

Function roundit(value As Double, precision As Double) As Double
    roundit = Int(value / precision + 0.5) * precision
End Function

which seems to work fine

哪一种似乎有效

#4

Int and Fix are both useful rounding functions, which give you the integer part of a number.

Int和Fix都是有用的舍入函数，它们提供数字的整数部分。

Int always rounds down - Int(3.5) = 3, Int(-3.5) = -4

Int总是向下四舍五入- Int(3.5) = 3, Int(-3.5) = -4

Fix always rounds towards zero - Fix(3.5) = 3, Fix(-3.5) = -3

Fix总是趋向于0 - Fix(3.5) = 3, Fix(-3.5) = -3

There's also the coercion functions, in particular CInt and CLng, which try to coerce a number to an integer type or a long type (integers are between -32,768 and 32,767, longs are between-2,147,483,648 and 2,147,483,647). These will both round towards the nearest whole number, rounding away from zero from .5 - CInt(3.5) = 4, Cint(3.49) = 3, CInt(-3.5) = -4, etc.

还有强制函数，特别是CInt和CLng，它们试图将一个数字强制为整数类型或长类型(整数在-32,768和32,767之间，long在2,147,483,648和2,147,483,647之间)。它们都将四舍五入到最接近的整数，从0到。5 - CInt(3.5) = 4, CInt(3.49) = 3, CInt(-3.5) = -4，等等。

#5

1 place = INT(number x 10 + .5)/10
3 places = INT(number x 1000 + .5)/1000

and so on.You'll often find that apparently kludgy solutions like this are much faster than using Excel functions, because VBA seems to operate in a different memory space.

等等。您经常会发现，像这样看似笨拙的解决方案比使用Excel函数要快得多，因为VBA似乎在不同的内存空间中运行。

eg If A > B Then MaxAB = A Else MaxAB = B is about 40 x faster than using ExcelWorksheetFunction.Max

如果一个> B，那么MaxAB = A Else MaxAB = B比使用excelworksheetfunction快40倍

#6

Unfortunately, the native functions of VBA that can perform rounding are either missing, limited, inaccurate, or buggy, and each addresses only a single rounding method. The upside is that they are fast, and that may in some situations be important.

不幸的是，可以执行四舍五入的VBA的本机函数要么是缺失的，要么是有限的，要么是不准确的，要么是bug，并且每个都只处理一个四舍五入的方法。好处是它们速度很快，这在某些情况下可能很重要。

However, often precision is mandatory, and with the speed of computers today, a little slower processing will hardly be noticed, indeed not for processing of single values. All the functions at the links below run at about 1 µs.

但是，通常精度是必须的，并且随着计算机的速度的提高，很少会注意到处理速度稍微慢一点，实际上对单个值的处理也是如此。所有的功能在下面的链接运行约1µs。

The complete set of functions - for all common rounding methods, all data types of VBA, for any value, and not returning unexpected values - can be found here:

完整的函数集——对于所有常见的舍入方法，所有数据类型的VBA，对于任何值，而不返回意外值——可以在这里找到:

Rounding values up, down, by 4/5, or to significant figures (EE)

将值向上、向下、向上或向下舍入4/5，或舍入重要数字(EE)

or here:

或者在这里:

Rounding values up, down, by 4/5, or to significant figures (CodePlex)

将值向上，向下，4/5，或重要的数字(CodePlex)

Code only at GitHub:

代码只有在GitHub:

VBA.Round

VBA.Round

They cover the normal rounding methods:

它们包括正常的舍入方法:

• Round down, with the option to round negative values towards zero

  四舍五入，可以选择将负值四舍五入到0

• Round up, with the option to round negative values away from zero

  将负值从零附近移开。

• Round by 4/5, either away from zero or to even (Banker's Rounding)

  四舍五入，从0到偶数(银行家四舍五入)

• Round to a count of significant figures

  四舍五入数到重要数字

The first three functions accept all the numeric data types, while the last exists in three varieties - for Currency, Decimal, and Double respectively.

前三个函数接受所有的数字数据类型，而最后一个函数以三种形式存在——分别是货币、十进制和Double。

They all accept a specified count of decimals - including a negative count which will round to tens, hundreds, etc. Those with Variant as return type will return Null for incomprehensible input

它们都接受一个指定的小数计数——包括一个负数计数，它将四舍五入到十位、百位等等。那些以返回类型不同的输入将返回空值

A test module for test and validating is included as well.

还包括测试和验证的测试模块。

An example is here - for the common 4/5 rounding. Please study the in-line comments for the subtle details and the way CDec is used to avoid bit errors.

这里有一个例子——对于常见的4/5四舍五入。请仔细研究内联注释，以获得更细微的细节，以及使用CDec避免位错误的方法。

' Common constants.
'
Public Const Base10     As Double = 10

' Rounds Value by 4/5 with count of decimals as specified with parameter NumDigitsAfterDecimals.
'
' Rounds to integer if NumDigitsAfterDecimals is zero.
'
' Rounds correctly Value until max/min value limited by a Scaling of 10
' raised to the power of (the number of decimals).
'
' Uses CDec() for correcting bit errors of reals.
'
' Execution time is about 1µs.
'
Public Function RoundMid( _
    ByVal Value As Variant, _
    Optional ByVal NumDigitsAfterDecimals As Long, _
    Optional ByVal MidwayRoundingToEven As Boolean) _
    As Variant

    Dim Scaling     As Variant
    Dim Half        As Variant
    Dim ScaledValue As Variant
    Dim ReturnValue As Variant

    ' Only round if Value is numeric and ReturnValue can be different from zero.
    If Not IsNumeric(Value) Then
        ' Nothing to do.
        ReturnValue = Null
    ElseIf Value = 0 Then
        ' Nothing to round.
        ' Return Value as is.
        ReturnValue = Value
    Else
        Scaling = CDec(Base10 ^ NumDigitsAfterDecimals)

        If Scaling = 0 Then
            ' A very large value for Digits has minimized scaling.
            ' Return Value as is.
            ReturnValue = Value
        ElseIf MidwayRoundingToEven Then
            ' Banker's rounding.
            If Scaling = 1 Then
                ReturnValue = Round(Value)
            Else
                ' First try with conversion to Decimal to avoid bit errors for some reals like 32.675.
                ' Very large values for NumDigitsAfterDecimals can cause an out-of-range error 
                ' when dividing.
                On Error Resume Next
                ScaledValue = Round(CDec(Value) * Scaling)
                ReturnValue = ScaledValue / Scaling
                If Err.Number <> 0 Then
                    ' Decimal overflow.
                    ' Round Value without conversion to Decimal.
                    ReturnValue = Round(Value * Scaling) / Scaling
                End If
            End If
        Else
            ' Standard 4/5 rounding.
            ' Very large values for NumDigitsAfterDecimals can cause an out-of-range error 
            ' when dividing.
            On Error Resume Next
            Half = CDec(0.5)
            If Value > 0 Then
                ScaledValue = Int(CDec(Value) * Scaling + Half)
            Else
                ScaledValue = -Int(-CDec(Value) * Scaling + Half)
            End If
            ReturnValue = ScaledValue / Scaling
            If Err.Number <> 0 Then
                ' Decimal overflow.
                ' Round Value without conversion to Decimal.
                Half = CDbl(0.5)
                If Value > 0 Then
                    ScaledValue = Int(Value * Scaling + Half)
                Else
                    ScaledValue = -Int(-Value * Scaling + Half)
                End If
                ReturnValue = ScaledValue / Scaling
            End If
        End If
        If Err.Number <> 0 Then
            ' Rounding failed because values are near one of the boundaries of type Double.
            ' Return value as is.
            ReturnValue = Value
        End If
    End If

    RoundMid = ReturnValue

End Function

#7

If you're talking about rounding to an integer value (and not rounding to n decimal places), there's always the old school way:

如果你在谈论舍入到一个整数值(而不是舍入到小数点后n位)，有一种老的方法:

return int(var + 0.5)

(You can make this work for n decimal places too, but it starts to get a bit messy)

(你也可以把它写成n个小数位，但它开始变得有点乱)

#8

Lance already mentioned the inherit rounding bug in VBA's implementation. So I need a real rounding function in a VB6 app. Here is one that I'm using. It is based on one I found on the web as is indicated in the comments.

Lance已经在VBA的实现中提到了遗传四舍五入错误。所以我需要一个真正的舍入函数在VB6应用中。它基于我在web上找到的一个在评论中所指出的。

' -----------------------------------------------------------------------------
' RoundPenny
'
' Description:
'    rounds currency amount to nearest penny
'
' Arguments:
'    strCurrency        - string representation of currency value
'
' Dependencies:
'
' Notes:
' based on RoundNear found here:
' http://advisor.com/doc/08884
'
' History:
' 04/14/2005 - WSR : created
'
Function RoundPenny(ByVal strCurrency As String) As Currency

         Dim mnyDollars    As Variant
         Dim decCents      As Variant
         Dim decRight      As Variant
         Dim lngDecPos     As Long

1        On Error GoTo RoundPenny_Error

         ' find decimal point
2        lngDecPos = InStr(1, strCurrency, ".")

         ' if there is a decimal point
3        If lngDecPos > 0 Then

            ' take everything before decimal as dollars
4           mnyDollars = CCur(Mid(strCurrency, 1, lngDecPos - 1))

            ' get amount after decimal point and multiply by 100 so cents is before decimal point
5           decRight = CDec(CDec(Mid(strCurrency, lngDecPos)) / 0.01)

            ' get cents by getting integer portion
6           decCents = Int(decRight)

            ' get leftover
7           decRight = CDec(decRight - decCents)

            ' if leftover is equal to or above round threshold
8           If decRight >= 0.5 Then

9              RoundPenny = mnyDollars + ((decCents + 1) * 0.01)

            ' if leftover is less than round threshold
10          Else

11             RoundPenny = mnyDollars + (decCents * 0.01)

12          End If

         ' if there is no decimal point
13       Else

            ' return it
14          RoundPenny = CCur(strCurrency)

15       End If

16       Exit Function

RoundPenny_Error:

17       Select Case Err.Number

            Case 6

18             Err.Raise vbObjectError + 334, c_strComponent & ".RoundPenny", "Number '" & strCurrency & "' is too big to represent as a currency value."

19          Case Else

20             DisplayError c_strComponent, "RoundPenny"

21       End Select

End Function
' ----------------------------------------------------------------------------- 

#9

VBA.Round(1.23342, 2) // will return 1.23

#10

To solve the problem of penny splits not adding up to the amount that they were originally split from, I created a user defined function.

为了解决便士分割的问题，我创建了一个用户定义的函数。

Function PennySplitR(amount As Double, Optional splitRange As Variant, Optional index As Integer = 0, Optional n As Integer = 0, Optional flip As Boolean = False) As Double
' This Excel function takes either a range or an index to calculate how to "evenly" split up dollar amounts
' when each split amount must be in pennies.  The amounts might vary by a penny but the total of all the
' splits will add up to the input amount.

' Splits a dollar amount up either over a range or by index
' Example for passing a range: set range $I$18:$K$21 to =PennySplitR($E$15,$I$18:$K$21) where $E$15 is the amount and $I$18:$K$21 is the range
'                              it is intended that the element calling this function will be in the range
' or to use an index and total items instead of a range: =PennySplitR($E$15,,index,N)
' The flip argument is to swap rows and columns in calculating the index for the element in the range.

' Thanks to: http://*.com/questions/5559279/excel-cell-from-which-a-function-is-called for the application.caller.row hint.
Dim evenSplit As Double, spCols As Integer, spRows As Integer
If (index = 0 Or n = 0) Then
    spRows = splitRange.Rows.count
    spCols = splitRange.Columns.count
    n = spCols * spRows
    If (flip = False) Then
       index = (Application.Caller.Row - splitRange.Cells.Row) * spCols + Application.Caller.Column - splitRange.Cells.Column + 1
     Else
       index = (Application.Caller.Column - splitRange.Cells.Column) * spRows + Application.Caller.Row - splitRange.Cells.Row + 1
    End If
 End If
 If (n < 1) Then
    PennySplitR = 0
    Return
 Else
    evenSplit = amount / n
    If (index = 1) Then
            PennySplitR = Round(evenSplit, 2)
        Else
            PennySplitR = Round(evenSplit * index, 2) - Round(evenSplit * (index - 1), 2)
    End If
End If
End Function

#11

I used the following simple function to round my currencies as in our company we always round up.

我使用了下面的简单函数来循环我的货币，因为在我们公司我们总是进行循环。

Function RoundUp(Number As Variant)
   RoundUp = Int(-100 * Number) / -100
   If Round(Number, 2) = Number Then RoundUp = Number
End Function

but this will ALWAYS round up to 2 decimals and may also error.

但这总是整数到2个小数也可能出错。

even if it is negative it will round up (-1.011 will be -1.01 and 1.011 will be 1.02)

即使它是负数，它也会四舍五入(-1。011是-1。01,1。011是1。02)

so to provide more options for rounding up (or down for negative) you could use this function:

因此，为了提供更多的舍入选项(或负数舍入选项)，您可以使用这个函数:

Function RoundUp(Number As Variant, Optional RoundDownIfNegative As Boolean = False)
On Error GoTo err
If Number = 0 Then
err:
    RoundUp = 0
ElseIf RoundDownIfNegative And Number < 0 Then
    RoundUp = -1 * Int(-100 * (-1 * Number)) / -100
Else
    RoundUp = Int(-100 * Number) / -100
End If
If Round(Number, 2) = Number Then RoundUp = Number
End Function

(used in a module, if it isn't obvious)

(在模块中使用，如果不明显)

#12

Here is easy way to always round up to next whole number in Access 2003:

这里有一个简单的方法，总是在存取2003的下一个整数:

BillWt = IIf([Weight]-Int([Weight])=0,[Weight],Int([Weight])+1)

For example:

例如:

• [Weight] = 5.33 ; Int([Weight]) = 5 ; so 5.33-5 = 0.33 (<>0), so answer is BillWt = 5+1 = 6.
• (重量)= 5.33;Int((重量))= 5;5。33-5 = 0.33(<>)所以答案是BillWt = 5+1 = 6。
• [Weight] = 6.000, Int([Weight]) = 6 , so 6.000-6 = 0, so answer is BillWt = 6.
• [Weight] = 6.000, Int([Weight]) = 6，因此6000 -6 = 0，所以答案是BillWt = 6。