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

excel vba
Chris picture Chris · Jun 28, 2013 · Viewed 234.4k times · Source

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

Answer

Charles Williams picture Charles Williams · Jun 28, 2013

.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

Related questions

Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
How do I declare a global variable in VBA?

I wrote the following code: Function find_results_idle() Public iRaw As Integer Public iColumn As Integer iRaw = 1 iColumn = 1 And I get the error message: "invalid attribute in Sub or Function" Do you know what I did wrong? I tried …

Read More
How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

How can I use regular expressions in Excel and take advantage of Excel's powerful grid-like setup for data manipulation? In-cell function to return a matched pattern or replaced value in a string. Sub to loop through a column of data …

Read More