.Value仅以货币格式返回值的4位小数

I have the following VBA function to retrieve a currency value from somewhere else with the same column but a different row:

我有以下VBA函数来从具有相同列但不同行的其他地方检索货币值:

Function GetValue(RefCell As Range) As Double

    Dim i As Integer

    i = Application.Caller.Column - RefCell.Column

    Obtain = RefCell.Offset(0, i).Value

End Function

And here's the setup:

这是设置:

Column1 | Column2
Test 1  | 0.11111
Test 2  | $0.11111

Using GetValue(Test1) gave me $0.11111 with all 5 decimal places. However, GetValue(Test2) yield $0.11110 with only the first 4 decimals. How come the function only returns 4 decimal places if the input is in Currency format as opposed to Number format? I want to retrieve the most accurate number with all the decimal places from cells with Currency format - ie $0.11111 as opposed to $0.11110.

使用GetValue(Test1)给了我$ 0.11111,所有5位小数。但是,GetValue(Test2)仅产生前4个小数,产生0.11110美元。为什么如果输入是货币格式而不是数字格式,函数只返回4位小数?我想从货币格式的单元格中检索所有小数位的最准确数字 - 即$ 0.11111而不是$ 0.11110。

1 个解决方案

#1

The Range.Value property holds some regional currency idiosyncrasies; one of these could be a 4 digit precision (common for some regional currency variable types).

Range.Value属性包含一些区域货币特性;其中一个可能是4位精度(对于某些区域货币变量类型通用)。

Use the Range.Value2 property for the raw double value.

将Range.Value2属性用于原始double值。

GetValue = RefCell.Offset(0, i).Value2

The returned value may have to be manually formatted as currency; any regional information has been stripped.

返回的值可能必须手动格式化为货币;任何区域信息都被剥夺了。

#1