By Chris


2013-06-28 07:51:16 8 Comments

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.

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.

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.

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.

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

5 comments

@user4039065 2018-06-15 01:25:23

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

@silkfire 2015-09-08 10:32:21

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

@Charles Williams 2013-06-28 11:17:34

.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

@lee_mcmullen 2015-01-21 11:27:32

I have the following value in a cell: 1420470000000000. I want to get it as a string. Using .Value and Value2 both return the following for me: 1.42047E+15. What I actually want is the full value as a string i.e. 1420470000000000. I'm using .Formula for the time being but would be keen to know of a more appropriate solution?

@Charles Williams 2015-01-22 13:16:11

I would probably use Format to control how the number gets converted to a string: var = Format(Range("a1").Value2, "#")

@Martin F 2015-04-24 22:27:57

I hope this is not a separate question but: What is the default? The OP claims vaguely that leaving out text/value/value2 is problematic, but surely it defaults to one of them?

@Charles Williams 2015-04-25 07:15:48

The default is .Value

@Slai 2017-01-31 14:54:24

interesting difference about .Text is that it returns either String if the Range contains the same texts (even if different values), or Null otherwise

@Mathieu Guindon 2017-08-30 18:41:15

Sorry to wake up this oooooold post, but I'm failing to see the advantage of coercing a Date into a Double (using .Value2) when what you need is a Date. Shouldn't .Value be preferred over .Value2 when you're looking at a Date value? The linked article does not make that very clear either. Performance because no conversion? Sure, but then if your VBA code is working with a Date, then you'll lose that edge performing the conversion yourself, implicitly or explicitly... (context - feel free to weight in)

@Charles Williams 2017-08-30 22:13:53

@Mat's Mug - the problem is that Excel does not have a true Date data-type - excel dates and times are just doubles that depend on whatever format has been applied or changed by the user to appear as dates, times or currency or just a number. So Value is coercing an Excel double to a VBA date but Value2 is not doing any coercing ... For dates coercing the double to a date is probably not doing any damage as long as the code understands that its dependent on a changeable format: pros and cons either way - what we really need is more native Excel data types to avoid this problem.

@blobbles 2018-06-19 22:16:28

Another oddity I came across is in VBA setting one range equal to another. The range in question had numbers stored as text (with the little green triangle indicating this). Setting a range equal to these numbers stored as text using .Values/.Values2 causes the values to be converted to numbers (which I didn't want). The only way I found around this was using .Formula, just to throw another spanner in the works.

@ChrisB 2018-06-19 22:54:21

When I want to set a cell value equal to another cell without a type conversion (for example, without converting a number stored as text to a number) I use this: Format$(Range.Value2, Range.NumberFormat).

@Kazimierz Jawor 2013-06-28 08:12:16

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.

enter image description here

@Kazimierz Jawor 2013-06-28 09:21:00

@Chris, use .Value as standard property all the time- for text and numbers. Use .Value2 when you think of date and some numbers. And use .Text always if you need to keep formatting of anything you have in cell/range. So, your question example if correct!

@Katrin 2018-03-26 04:04:07

why the date changed from 10:12 to 10:05? typo?

@Kazimierz Jawor 2018-03-26 05:13:51

I think it is just time elapsed between generating results and time of making screen shot

@Bathsheba 2013-06-28 07:57:45

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.

Related Questions

Sponsored Content

1 Answered Questions

[SOLVED] What is the underlying type of Value2?

  • 2018-05-10 17:35:57
  • Snowflake
  • 193 View
  • 1 Score
  • 1 Answer
  • Tags:   c# excel excel-2016

6 Answered Questions

[SOLVED] What is a correct mime type for docx, pptx etc?

3 Answered Questions

[SOLVED] Auto-tabbing between fields in Excel Workbook

  • 2018-11-27 00:23:19
  • Danimov82
  • 66 View
  • 0 Score
  • 3 Answer
  • Tags:   excel vba excel-vba

0 Answered Questions

2 Answered Questions

2 Answered Questions

[SOLVED] Input Box to Select a Range or Enter Text

2 Answered Questions

[SOLVED] Displaying a cell value in a text box and moving around in excel using vba

  • 2016-05-21 15:43:09
  • Naveen Kumar
  • 4480 View
  • -2 Score
  • 2 Answer
  • Tags:   excel vba excel-vba

2 Answered Questions

[SOLVED] Format cell from general to text

1 Answered Questions

Excel VBA problems with locked sheet and macros

  • 2015-04-10 12:22:27
  • david loudfoot
  • 704 View
  • 0 Score
  • 1 Answer
  • Tags:   excel vba excel-vba

20 Answered Questions

[SOLVED] Import and Export Excel - What is the best library?

Sponsored Content