带有两个小数位的Excel数字

I wrote the following simple code which takes the input from user for N1 and N2 and adds them up and gives them as output (N3).There is an issue with this that, it rounds up each of the input instead of just taking the numbers.

我写了下面这个简单的代码,它从用户那里获取N1和N2的输入并将它们加起来并将它们作为输出(N3)。这有一个问题,它将每个输入四舍五入而不是仅仅取数字。

Easy example: N1 = 25.5, N2 = 25.5 Right Answer = 51 but the answer it gives is 52. I am new to programming so would appreciate any help I can get.

简单示例:N1 = 25.5,N2 = 25.5正确答案= 51但它给出的答案是52.我是编程新手,所以我将不胜感激。

I want it to display N3 up to 6 decimal places without rounding individual inputs from N1 and N2 at the time of taking inputs.

我想让它显示N3最多6个小数位,而不需要在输入时从N1和N2舍入单个输入。

Sub InputVariable()

    Dim N1 As Integer
    Dim N2 As Integer
    Dim N3 As Integer
    N1 = InputBox("Enter 1st number")
    N2 = InputBox("Enter 2nd number")
    N3 = N1 + N2
    MsgBox ("Your total is " & N3)
End Sub

2 个解决方案

#1

You need to use the Double data type. If you use an integer type (Integer or Long) it will round the numbers at N1 = InputBox(...) because it can't store non-integer values in the variables.

您需要使用Double数据类型。如果使用整数类型(整数或长整数),它将在N1 = InputBox(...)处舍入数字,因为它不能在变量中存储非整数值。

edit: Double stands for double precision (8 byte) compared to single precision (4 byte). It is interesting to note that because Double uses the binary number format it can't store exact values like 0.1 (just like the decimal system can't express 1/3 no matter how many digits you have).

edit:Double表示双精度(8字节)与单精度(4字节)相比。有趣的是要注意,因为Double使用二进制数字格式,它不能存储精确的值,如0.1(就像十进制系统不管你有多少位数就不能表示1/3)。

If you need to do really precise calculations with decimal numbers, there's the Decimal format that you can use. You can't actually declare it but you can convert a number to decimal and store it in a Variant. See this example:

如果您需要使用十进制数进行精确计算,可以使用Decimal格式。您实际上无法声明它,但您可以将数字转换为十进制并将其存储在Variant中。看这个例子:

Sub precisionTest()
    Dim i As Long
    Dim dbl As Double
    Dim dblResult As Double
    Dim dec As Variant
    Dim decResult As Variant

    dblResult = 0
    decResult = 0
    dbl = 0.00001
    dec = CDec(0.00001)
    For i = 1 To 100000
    dblResult = dblResult + dbl
    decResult = decResult + dec
    Next i
    MsgBox "Double result: " & dblResult & vbCr & "Decimal result: " & decResult
End Sub

edit2: Rounding and formatting of numbers: You can use the Format function to create strings of your number without changing the value (for display purposes only). Valid formats look like "0.##" where 0 means the decimal place is always displayed and # means it's displayed if it's non-zero:

edit2:数字的舍入和格式化:您可以使用Format函数创建数字的字符串而不更改值(仅用于显示目的)。有效格式看起来像“0。##”,其中0表示始终显示小数位,#表示如果它不为零则显示:

Sub formatTest()
    Dim dbl As Double
    Dim dbl2 As Double
    Dim dbl3 As Double
    dbl = 1.234
    dbl2 = 1.2
    dbl3 = 0.1
    MsgBox "Format(1.234,""0.##"") = " & Format(dbl, "0.##") & vbCr _
    & "Format(1.234,""0.00"") = " & Format(dbl, "0.00") & vbCr _
    & "Format(1.2,""0.##"") = " & Format(dbl2, "0.##") & vbCr _
    & "Format(1.2,""0.00"") = " & Format(dbl2, "0.00") & vbCr _
    & "Format(0.1,""#.##"") = " & Format(dbl3, "#.##") & vbCr _
    & "Format(0.1,""0.00"") = " & Format(dbl3, "0.00") & vbCr
End Sub

If you want to actually round your numbers, use Round(number,decimalplaces)

如果要实际舍入数字,请使用Round(数字,小数)

#2

As InWoods mentioned, you need N1, N2 and N3 to be double types, then you can cast the output in the print statement, like this:

正如InWoods所提到的,你需要N1,N2和N3是双重类型,然后你可以在print语句中转换输出,如下所示:

MsgBox ("Your total is " & Format(N3, "Standard"))

The standard formatting includes two decimal places.

标准格式包括两位小数。

#1