Excel VBA：获取单元格的值，与单元格的格式无关
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10020783/
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
Excel VBA: get value of cell, independent of cell's formatting
提问by Joe
In Excel VBA, Range("A1").Valueshould return the underlying value of the range A1 on the worksheet. But I'm getting a different value if the cell is formatted as Accounting.
在 Excel VBA 中，Range("A1").Value应返回工作表上区域 A1 的基础值。但是如果单元格的格式设置为会计，我会得到一个不同的值。
How do I get the actual underlying value of the cell?
如何获得单元格的实际基础值？
Worksheet
工作表
Make a new document, enter the following values in cells:
创建一个新文档，在单元格中输入以下值：
- A1: 0.00001
- A2: =A1
- A3: =A1=A2
- A1：0.00001
- A2：=A1
- A3：=A1=A2
As you'd expect, A3 results in TRUE. Now change the formatting of A2 to Accounting, using 2 decimal places. A2 now reads $ 0.00, but the underlying value is still 0.00001, so A3 is still TRUE.
如您所料，A3 结果为TRUE. 现在将 A2 的格式更改为 Accounting，使用 2 个小数位。A2 现在读取$ 0.00，但基础值仍然是0.00001，所以 A3 仍然是TRUE。
VBA
VBA
Make a new module and add in the following function:
创建一个新模块并添加以下功能：
Function f(addr As String)
f = Range(addr).Value
End Function
As you can see, this just gets the value of a range using the Valuemethod of the Rangeobject.
如您所见，这只是使用对象的Value方法获取范围的值Range。
Worksheet
工作表
Back to the worksheet. Enter the following values:
回到工作表。输入以下值：
- B1: =f("A1")
- B2: =f("A2")
- B3: =B1=B2
- B1: =f("A1")
- B2: =f("A2")
- B3：=B1=B2
A1and A2have the same underlying value, but B1and B2don't, even though they're both calculated using the Valuemethod of A1and A2.
A1并且A2有相同的基本价值，但B1并B2没有，即使它们都计算使用Value的方法A1和A2。
The expression in A3(=A1=A2) is accessing the actual underlying value of A1and A2. How do I access these values in VBA?
A3( =A1=A2) 中的表达式正在访问A1and的实际基础值A2。如何在 VBA 中访问这些值？
回答by DJ.
It initally came up TRUE for me as well because I added the formatting after I entered the formulas.
它最初对我来说也是正确的，因为我在输入公式后添加了格式。
To repro - re-edit B2.
复制 - 重新编辑 B2。
To get the underlying value you need to use the VALUE2 property which seems to ignore the formatting:
要获取基础值，您需要使用 VALUE2 属性，该属性似乎忽略了格式：
Function f(addr As String)
f = Range(addr).Value2
End Function
回答by brettdj
The issue with using VBA and Value with currency formatted cells beyond 4 decimals points is covered well by this post at Dick's blog
Dick 的博客上的这篇文章很好地涵盖了将 VBA 和 Value 与超过 4 个小数点的货币格式单元格一起使用的问题
If you type a number into an unformatted cell in Excel, that number is stored in the Double data type. When you format that number, you show it in a specific way, but you don't change the number. For instance, if you format the number 1 as a Comma style, you get 1.00. The underlying data is still a 1, you're just showing it differently. The Date format and the Currency format are two exceptions to this rule.
When you format something as a Date or Currency, you actually change the underlying data type (of the value stored in the Value property). When it comes to the Date format, this is semantics because you can switch between the Date and Double data types without any change to the data. Not so much with the Currency data type. Currency only supports four decimal places, so jamming a Double with, say, five decimals into a Currency data type will result in a different number.
如果您在 Excel 中的无格式单元格中键入数字，则该数字将以 Double 数据类型存储。格式化该数字时，您会以特定方式显示它，但不会更改该数字。例如，如果将数字 1 格式化为逗号样式，则会得到 1.00。基础数据仍然是 1，您只是以不同的方式显示它。日期格式和货币格式是此规则的两个例外。
当您将某些内容格式化为日期或货币时，您实际上更改了基础数据类型（存储在 Value 属性中的值）。对于 Date 格式，这是语义，因为您可以在 Date 和 Double 数据类型之间切换，而无需对数据进行任何更改。与货币数据类型无关。Currency仅支持四位小数，因此将 Double 与五位小数干扰到 Currency 数据类型将导致不同的数字。
