vba .text、.value 和 .value2 之间有什么区别？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17359835/
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
What is the difference between .text, .value, and .value2?
提问by Chris
I am not asking for help with any script, but my question is for clarification. Lately I have been doing a lot of VB scripting in Excel, so I am really referring to Excel in this question. What is the difference between .text, .value, and .value2? Like when should I use target.text, target.value, and target.value2? I never used the value2 option but would still like to know what it is used for.
我不是在寻求任何脚本的帮助，但我的问题是为了澄清。最近我在Excel中做了很多VB脚本，所以我在这个问题中真正指的是Excel。.text、.value 和 .value2 之间有什么区别？比如我什么时候应该使用 target.text、target.value 和 target.value2？我从未使用过 value2 选项，但仍然想知道它的用途。
Sometimes if I use .text it gives me an error and I need to use .value when I am only checking or manipulating the text within a cell. Then sometimes when I think I should use .value I get an error and I need to use .text. Usually it accepts either or without an issue, but sometimes it does make a difference. I know there has to be some logic to this, but I can't seem to figure it out.
有时，如果我使用 .text 它会给我一个错误，当我只检查或操作单元格中的文本时，我需要使用 .value。然后有时当我认为我应该使用 .value 时，我会收到一个错误，我需要使用 .text。通常它接受或不接受任何问题，但有时确实会有所作为。我知道这必须有一些逻辑，但我似乎无法弄清楚。
I also found out that if you just leave it as target without specifying .text or .value it will initially work, but then something that someone does will eventually cause the script to error, so it is always best to use a .something on it. I guess what I am asking is if someone can give me some sort of guideline, rule of thumb, as to how to properly use each one and when it needs to be used.
我还发现如果你只是把它作为目标而不指定 .text 或 .value 它最初会工作，但是有人做的事情最终会导致脚本出错，所以最好在它上面使用 .something . 我想我要问的是是否有人可以给我一些指导方针，经验法则，关于如何正确使用每个人以及何时需要使用它。
Thanks for the explanation guys. I kind of understand it better. They are both good explanations. Below is a tiny example of some of my code that does work. I thought it should be target.text, but it would error so when I used target.value it worked.
谢谢各位大佬的解释。我有点理解它了。他们都是很好的解释。下面是我的一些确实有效的代码的一个小例子。我认为它应该是 target.text，但它会出错，所以当我使用 target.value 时它起作用了。
If LCase(Target.Value) = LCase("HLO") And Target.Column = 15 Then
Target.Value = "Higher Level Outage"
End If
I am still slightly confused because when I think of value or value2, especially after your answers that you provided, I think they should only be used for numbers. However, in my example I am talking about strictly text, which is a lot of what my script refers to (text in the cells, more so than numbers).
我仍然有点困惑，因为当我想到 value 或 value2 时，尤其是在您提供的答案之后，我认为它们应该只用于数字。但是，在我的示例中，我谈论的是严格的文本，这是我的脚本所指的很多内容（单元格中的文本，而不是数字）。
回答by Charles Williams
.Textgives you a string representing what is displayed on the screen for the cell. Using .Text is usually a bad idea because you could get ####
.Text为您提供一个字符串，表示该单元格的屏幕上显示的内容。使用 .Text 通常是一个坏主意，因为你可能会得到 ####
.Value2gives you the underlying value of the cell (could be empty, string, error, number (double) or boolean)
.Value2为您提供单元格的基础值（可以为空、字符串、错误、数字（双精度）或布尔值）
.Valuegives you the same as .Value2 except if the cell was formatted as currency or date it gives you a VBA currency (which may truncate decimal places) or VBA date.
.Value为您提供与 .Value2 相同的内容，除非单元格格式设置为货币或日期，否则它会为您提供 VBA 货币（可能会截断小数位）或 VBA 日期。
Using .Value or .Text is usually a bad idea because you may not get the real value from the cell, and they are slower than .Value2
使用 .Value 或 .Text 通常是一个坏主意，因为您可能无法从单元格中获得真正的值，而且它们比 .Value2 慢
For a more extensive discussion see my Text vs Value vs Value2
有关更广泛的讨论，请参阅我的Text vs Value vs Value2
回答by Kazimierz Jawor
回答by Bathsheba
target.Valuewill give you a Varianttype
target.Value会给你一个Variant类型
target.Value2will give you a Varianttype as well but a Dateis coerced to a Double
target.Value2也会给你一个Variant类型，但 aDate被强制为 aDouble
target.Textattempts to coerce to a Stringand will fail if the underlying Variantis not coercable to a Stringtype
target.Text尝试强制转换为 aString并且如果底层Variant不可强制转换为String类型，则会失败
The safest thing to do is something like
最安全的做法是
Dim v As Variant
v = target.Value 'but if you don't want to handle date types use Value2
And check the type of the variant using VBA.VarType(v)before you attempt an explicit coercion.
并VBA.VarType(v)在尝试显式强制之前检查使用的变体类型。
回答by silkfire
Regarding conventions in C#. Let's say you're reading a cell that contains a date, e.g. 2014-10-22.
关于 C# 中的约定。假设您正在读取一个包含日期的单元格，例如 2014-10-22。
When using:
使用时：
.Text, you'll get the formatted representation of the date, as seen in the workbook on-screen:
2014-10-22. This property's type is always stringbut may not always return a satisfactory result.
.Text，您将获得日期的格式化表示，如屏幕上的工作簿所示：
2014-10-22。此属性的类型始终string但可能不总是返回令人满意的结果。
.Value, the compiler attempts to convert the date into a DateTimeobject: {2014-10-22 00:00:00}Most probably only useful when reading dates.
.Value，编译器尝试将日期转换为DateTime对象：{2014-10-22 00:00:00}很可能仅在读取日期时有用。
.Value2, gives you the real, underlying value of the cell. In the case for dates, it's a date serial: 41934. This property can have a different type depending on the contents of the cell. For date serials though, the type is double.
.Value2, 为您提供单元格的真实潜在价值。对于日期，它是一个日期序列：41934。根据单元格的内容，此属性可以具有不同的类型。但是对于日期连续剧，类型是double.
So you can retrieve and store the value of a cell in either dynamic, varor objectbut note that the value will always have some sort of innate type that you will have to act upon.
因此，您可以在任一中检索和存储单元格的值dynamic，var或者object请注意，该值将始终具有某种您必须对其采取行动的固有类型。
dynamic x = ws.get_Range("A1").Value2;
object y = ws.get_Range("A1").Value2;
var z = ws.get_Range("A1").Value2;
double d = ws.get_Range("A1").Value2; // Value of a serial is always a double
回答by silkfire
.Text is the formatted cell's displayed value; .Value is the value of the cell possibly augmented with date or currency indicators; .Value2 is the raw underlying value stripped of any extraneous information.
.Text 是格式化单元格的显示值；.Value 是可能用日期或货币指示符增加的单元格的值；.Value2 是去除了任何无关信息的原始底层价值。
range("A1") = Date
range("A1").numberformat = "yyyy-mm-dd"
debug.print range("A1").text
debug.print range("A1").value
debug.print range("A1").value2
'results from Immediate window
2018-06-14
6/14/2018
43265
range("A1") = "abc"
range("A1").numberformat = "_(_(_(@"
debug.print range("A1").text
debug.print range("A1").value
debug.print range("A1").value2
'results from Immediate window
abc
abc
abc
range("A1") = 12
range("A1").numberformat = "0 \m\m"
debug.print range("A1").text
debug.print range("A1").value
debug.print range("A1").value2
'results from Immediate window
12 mm
12
12
If you are processing the cell's value then reading the raw .Value2 is marginally faster than .Value or .Text. If you are locating errors then .Text will return something like #N/Aas text and can be compared to a string while .Value and .Value2 will choke comparing their returned value to a string. If you have some custom cell formatting applied to your data then .Text may be the better choice when building a report.
如果您正在处理单元格的值，那么读取原始 .Value2 比 .Value 或 .Text 略快。如果您正在定位错误，那么 .Text 将返回类似于#N/A文本的内容，并且可以与字符串进行比较，而 .Value 和 .Value2 将阻塞将其返回值与字符串进行比较。如果您对数据应用了一些自定义单元格格式，那么在构建报表时 .Text 可能是更好的选择。
回答by PGSystemTester
Out of curiosity, I wanted to see how Valueperformed against Value2. After about 12 trials of similar processes, I could not see any significant differences in speed so I would always recommend using Value. I used the below code to run some tests with various ranges.
出于好奇，我想看看Value对抗Value2. 在对类似过程进行了大约 12 次试验后，我看不出速度有任何显着差异，因此我始终建议使用Value. 我使用下面的代码来运行一些不同范围的测试。
If anyone sees anything contrary regarding performance, please post.
如果有人看到任何与性能相反的内容，请发布。
Sub Trial_RUN()
For t = 0 To 5
TestValueMethod (True)
TestValueMethod (False)
Next t
End Sub
Sub TestValueMethod(useValue2 As Boolean)
Dim beginTime As Date, aCell As Range, rngAddress As String, ResultsColumn As Long
ResultsColumn = 5
'have some values in your RngAddress. in my case i put =Rand() in the cells, and then set to values
rngAddress = "A2:A399999" 'I changed this around on my sets.
With ThisWorkbook.Sheets(1)
.Range(rngAddress).Offset(0, 1).ClearContents
beginTime = Now
For Each aCell In .Range(rngAddress).Cells
If useValue2 Then
aCell.Offset(0, 1).Value2 = aCell.Value2 + aCell.Offset(-1, 1).Value2
Else
aCell.Offset(0, 1).Value = aCell.Value + aCell.Offset(-1, 1).Value
End If
Next aCell
Dim Answer As String
If useValue2 Then Answer = " using Value2"
.Cells(Rows.Count, ResultsColumn).End(xlUp).Offset(1, 0) = DateDiff("S", beginTime, Now) & _
" seconds. For " & .Range(rngAddress).Cells.Count & " cells, at " & Now & Answer
End With
End Sub
