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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   amp                  seconds  For    amp   Range rngAddress  Cells Count  amp    cells  at    amp  Now  amp  Answer   End With   End Sub

User · Answer

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

User · Answer

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

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

Examples related to excel

Examples related to vba