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

Question

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

Answer 1

In addition to the answer from Bathsheba and the MSDN information for:

.Value
.Value2
.Text

you could analyze the following tables for better understanding of differences between these three properties.

Answer 2

Value2 is almost always the best choice to read from or write to an Excel cell or a range... from VBA.

Range.Value2 '<------Best way

Each of the following can be used to read from a range:

v = [a1]
v = [a1].Value
v = [a1].Value2
v = [a1].Text 
v = [a1].Formula
v = [a1].FormulaR1C1

Each of the following can be used to write to a range:

[a1] = v
[a1].Value = v
[a1].Value2 = v
[a1].Formula = v
[a1].FormulaR1C1 = v

To read many values from a large range, or to write many values, it can be orders of magnitude faster to do the entire operation in one go instead of cell by cell:

arr = [a1:z999].Value2

If arr is a variable of type Variant, the above line actually creates an OLE SAFEARRAY structure of variants 26 columns wide and 999 rows tall and points the Variant arr at the SAFEARRAY structure in memory.

[a1].Resize(UBound(arr), UBound(arr, 2).Value2 = arr

The above line writes the entire array to the worksheet in one go no matter how big the array is (as long as it will fit in the worksheet).

The default property of the range object is the Value property. So if no property is specified for the range, the Value property is silently referenced by default.

However, Value2 is the quickest property to access range values and when reading it returns the true underlying cell value. It ignores Number Formats, Dates, Times, and Currency and returns numbers as the VBA Double data type, always. Since Value2 attempts to do less work, it executes slightly more quickly than does Value.

The Value property, on the other hand, checks if a cell value has a Number Format of Date or Time and will return a value of the VBA Date data type in these cases. If your VBA code will be working with the Date data type, it may make sense to retrieve them with the Value property. And writing a VBA Date data type to a cell will automatically format the cell with the corresponding date or time number format. And writing a VBA Currency data type to a cell will automatically apply the currency Number Format to the appropriate cells.

Similarly, Value checks for cell currency formatting and then returns values of the VBA Currency data type. This can lead to loss of precision as the VBA Currency data type only recognizes four decimal places (because the VBA Currency data type is really just a 64-bit Integer scaled by 10000) and so values are rounded to four places, at most. And strangely, that precision is cut to just two decimal places when using Value to write a VBA Currency variable to a worksheet range.

The read-only Text property always returns a VBA String data type. The value returned by Range.Text is a textual representation of what is displayed in each cell, inclusive of Number Formats, Dates, Times, Currency, and Error text. This is not an efficient way to get numerical values into VBA as implicit or explicit coercion is required. Text will return ####### when columns are too thin and it will slow down even more when some row heights are adjusted. Text is always VERY slow compared to Value and Value2. However, since Text retains the formatted appearance of cell values, Text may be useful, especially for populating userform controls with properly formatted text values.

Similarly, both Formula and FormulaR1C1 always return values as a VBA String data type. If the cell contains a formula then Formula returns its A1-style representation and FormulaR1C1 returns its R1C1 representation. If a cell has a hard value instead of a formula then both Formula and FormulaR1C1 ignore all formatting and return the true underlying cell value exactly like Value2 does... and then take a further step to convert that value to a string. Again, this is not an efficient way to get numerical values into VBA as implicit or explicit coercion is required. However, Formula and FormulaR1C1 must be used to read cell formulas. And they should be used to write formulas to cells.

If cell A1 contains the numeric value of 100.25 with a currency number formatting of $#,##0.00_);($#,##0.00) consider the following:

MsgBox [a1].Value                   'Displays:  100.25
MsgBox TypeName([a1].Value)         'Displays: Currency
 
MsgBox [a1].Value2                  'Displays:  100.25
MsgBox TypeName([a1].Value2)        'Displays: Double
 
MsgBox [a1].Text                    'Displays: $ 100.25
MsgBox TypeName([a1].Text)          'Displays: String
 
MsgBox [a1].Formula                 'Displays: 100.25
MsgBox TypeName([a1].Formula)       'Displays: String
 
MsgBox [a1].FormulaR1C1             'Displays: 100.25
MsgBox TypeName([a1].FormulaR1C1)   'Displays: String
  

Answer 3

.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

Answer 4

Out of curiosity, I wanted to see how Value performed 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.

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

Answer 5

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

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/A as 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.

Answer 6

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

Answer 7

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.