What is the difference between .Text, .Value and .Value2

There are three ways to read the value from a cell with VBA in Excel using the Range object. This can be done with the properties .Value, .Value2 and .Text. However, there are some differences between these properties, each with their own advantages and disadvantages.

Range.Value

The property .Value is the default property of the function Range. So if you don't put a property after the function, it defaults to the property .Value applied.
In short: Range("A1").Value = Range("B1").Value is the same as Range("A1") = Range("B1").

Using Range.Value you can generally retrieve the original value from a cell. When a cell is formatted as Currencies a rounding off takes place. This is because the data type Currencies has a range from -922,337,203,685,477.5808 to 922,337,203,685,477.5807. As you can see, this data type has an accuracy of 4 decimal places.

Suppose: In cell A1 we have the value 1.23456789 with the Number format “Currency”. If we with Range.Value retrieve the original value from the relevant cell, it is converted to a Currency value. The original value of 1.23456789 is rounded to 1.2346. When we work with this, rounding errors may occur.

Sub Value() Dim varCurrency As Currency varCurrency = Range("A1").Value Debug.Print "A1: " & Range("A1").Value Debug.Print "Double precesion: " & CDbl(Range("A1" ).Value) varCurrency = CCur(varCurrency) Debug.Print "Currency: " & varCurrency Range("B1").Value = varCurrency Debug.Print "B1: " & Range("B1").Value End Sub

The above code gives the following result:

A1: 123.4568 Double precision: 123.4568 Currency: 123.4568 B1: 123.46

As you can see, the original value is rounded off when using Range.Value.

Range.Text

Unlike Range.Value, Range.Text returns the exact text value of a cell, regardless of cell formatting. This means that if you use Range.Text to retrieve the value from cell A1, you will get the original unchanged value of “1.23456789”, without rounding.

Sub Text() Dim cellText As String cellText = Range("A1").Text Debug.Print "A1 Text: " & cellText End Sub

The above VBA code gives the following result:

A1 Text: 1.23456789

Using Range.Text is useful when you need the exact text value of a cell without any formatting or conversion operations.

Range.Value2

Range.Value2 is similar to Range.Value, but it has an important difference. Range.Value2 does not care about cell formatting and always returns the raw numeric value of a cell, without rounding. It's faster than Range.Value and is recommended if your precision is important and you don't want to see formatting changes.

Sub Value2() Dim value2 As Variant value2 = Range("A1").Value2 Debug.Print "A1 Value2: " & value2 End Sub

The above VBA code gives the following result:

A1 Value2: 1.23456789

Range.Value2 is a good choice when you're manipulating data and precision is essential.

Conclusion

Choosing between .Value, .Value2, and .Text in VBA for Excel depends on your specific needs. If you want the raw numeric value, without formatting or rounding, Range.Value2 is the best option. If you need the exact text value, without formatting, use Range.Text. But be careful with Range.Value, as it can contain cell formatting and rounding.

Understanding these properties and when to apply them will help you handle Excel data more effectively in your VBA projects. Choose the feature that best suits your specific situation and requirements.