What is the difference between .text, .value, and .value2?

What is the difference between .text, .value, and .value2? Such as when should target.text, target.value, and target.value2 be used?

Solution 1:

.Text gives you a string representing what is displayed on the screen for the cell. Using .Text is usually a bad idea because you could get ####

.Value2 gives you the underlying value of the cell (could be empty, string, error, number (double) or boolean)

.Value gives 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.

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

For a more extensive discussion see my Text vs Value vs Value2

Solution 2:

Except first answer form Bathsheba, except MSDN information for:

.Value
.Value2
.Text

you could analyse these tables for better understanding of differences between analysed properties.

Solution 3:

target.Value will give you a Variant type

target.Value2 will give you a Variant type as well but a Date is coerced to a Double

target.Text attempts to coerce to a String and will fail if the underlying Variant is not coercable to a String type

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.

Solution 4:

Regarding conventions in C#. Let's say you're reading a cell that contains a date, e.g. 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 string but may not always return a satisfactory result.

.Value, the compiler attempts to convert the date into a DateTime object: {2014-10-22 00:00:00} Most probably only useful when reading dates.

.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.

So you can retrieve and store the value of a cell in either dynamic, var or object but note that the value will always have some sort of innate type that you will have to act upon.

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