One of you raised a question about which property should I be using to get the value of a cell or range – . Text, .Value or .Value2 Most of the time you might have seen all 3 methods being used interchangeably. Rather than answering them in a comment, I thought of writing an article here so that you all can benefit from it and learn something interesting and new.
Before we look into the differences between these 3 – let’s what is common about them. : Well, they all 3 are used to fetch the data from a cell or cell range.
What is .Text property in Excel VBA
In simple language, .text() brings the string that is literally visible in a cell.The reason why I say literally visible is because .Text returns the formatted value from a cell. Note that .Text is a read-only property. It means you can not set it programmatically. Let’s take an example:
Example:
If cell B1 has a long date format but the column width is not adjusted to fit the entire date, what we see is a # hash symbol filled in the cell. Even though if we click on the cell we can see the correct date inside. That means, in such a case, if you are using the following statement in your VBA code Range("B1").Text will return the exact number of # keys that are visible on the screen as you can see in the below picture.
What is returned by .Text
From the above picture it is quite clear what .Text returns in Excel VBA programming.
As I mentioned before, .Text returns the formatted value of a cell, that means, if your cell is formatted for Long Date and it is visible on the screen, then it will return the same long format value.
One of the major drawbacks of .Text property is that it can return all ##### values if a column or row width and height is not adjusted according to the data and cell is displaying ### keys. We have already seen that in the above example.
What is .Value property in Excel VBA
To understand the difference, it is easier, if we take the same example and run our same code, this time with .Value
.Value returns the exact value what is stored in the cell. Unlike .Text property, .Value does not care about the formatting of the cell rather it returns the exact value [Native excel value] that is stored in the cell. You can see that in the below picture:
What is .Value in Excel VBA
.Value property returns values stored in Native Excel data types which are: Decimal [double precision], Boolean, Empty, Text and Error.But in case of Currency and Date formatting,
.Value property returns the value in date and currency format after converting its native type Double precision numbers to Data or currency.The drawback of using
.Value in the case of currency is that: In excel currency has a limit of 4 decimal points and therefore, you might lose precision beyond 4 digits after the decimal.For example: If you have a number 3.68686868, stored in a cell formatted as currency, then
.Value will return 3.6869 only. It will round up the value up to 4 digits after decimal before passing it.
What is .Value2 property in Excel VBA
.Value2 returns mostly the same result as .Value except the fact that it does not convert to Currency or Date format too. That means .Value2 is the property that always returns the exact value which is stored in a Native Excel format which is either of the following: Double Precision, Text, Boolean, Error, or Empty.
You can refer the bellow image to see the difference between .Value and .Value2 properties of Excel VBA.
What is Value2 in Excel VBA
Bonus tip
Apart from all the above differences, there is one more difference related to their performance. That means how fast do they return the value?
.Text: This is the slowest and the reason is obvious. Before returning the values it looks into formatting of the cell and then converts it accordingly before returning it.
.Value : This is faster than .Text because it converts only in case of formatting is done for currency or date. In all other cases, it returns the value as it is. Therefore, It is little faster than .Text
.Value2 This is the fastest and reason is obvious too. This does not convert any value at all. It returns every cell value in its native data type irrespective of any formatting done on a cell.
0 Comments