Whether to use Value, Value2 or Text when trying to get the value in a cell

The range propertiesValue and Value2 generally return the same value except in case of a Date or Currency. The code and output below show different results given a cell A1 that contains a formula =1/7. This example shows that Value2 returns the most accurate value and should therefore often be preferred when used in calculations. Value2 also works faster. Using Value to retrieve cells formatted as dates into a variant can be useful because then you can use IsDate() to detect that the cell was a date.

Text gives you a string representing what is displayed on the screen for the cell. Using .Text is risky because you could get #### if there is insufficient space in the cell to display its content.

Dim rng As Range: Set rng = Application.Range("$A$1")
Debug.Print rng.Value
Debug.Print rng.Value2
Debug.Print rng.Text

'output
'0.1429
'0.142857142857143
'$0.14