Don’t be confused if you look at the official documentation. The Text Property of an Excel Range Object is a read-only string. It returns or sets the text for the Range Object. This is a real concise description, isn’t it? Don’t use the Text property of a Range object could be the message. But, let’s take a closer look at the Text property.

Dim s As String
  s = ActiveCell.Text

This returns in most cases the text of the active cell. But

ActiveSheet.Cells(1, 1).Text = "blablub"

Raises a run-time error 1004 – unable to set the Text Property. The Office 11 Language Reference gives some advice. There is a more precise description. The Text property is read-only for the Range object and read/write for all other objects in the Excel Object Model. Since then nothing changed but the description was shortened. And more, both versions do not explain what the text of a range is.

The type of Range.Text is Variant. It is not a real String type property. For a Range that represents a row or a column, a range of cells or multiple ranges, Range.Text returns a Null-Value. If the Range represents a single cell, the Text property returns the displayed text. In this case, the Text property returns a Variant value of type String.

The displayed text is the text that is displayed in your application. Every cell has its own number format. It is kept in the NumberFormat property of the Range object. Excel displays the values of cells on screen depending on the given format and your local settings. But, there are more relations.

The Text property returns all characters if the type of the underlying value is String. It returns only 1024 characters of a cell’s value for the Excel versions of Office 97-2003. If you need all characters, you have to use the Value or Value2 property of the cell.

The Text property returns the displayed text for all other value types. Excel displays a repeated #-character if the width of the formatted text exceeds the width of the cell. In this situation, the Text property returns a "#####"-String. If you hide the whole column, Excel displays nothing. The Text property consequently returns an empty string ("").

A special behavior exists for character values formatted with the text number format (@). The normal behavior is that Excel displays the first characters of text values and cut on a cells border but the Text property returns all characters except the limitations mentioned before. If a cell contains a string value with more than 256 characters and the number format is "@", Excel displays a "#####"-String and the Text property returns this string. This is a bug that is resolved in Excel 14. For earlier version you can use the General number format as a workaround.

Your displayed text changes if you use the Show Formulas feature of Excel. You will see the cell value or the underlying formula if the value is calculated. In this case, the Text property returns the full text representation of values even if it is not displayed. If the cell contains a formula, the Text property returns only approximately 255 characters of the formula text.

It could be so easy. Assumed that the Text property would be the text of a range, we’d have some pros. For example, you could do a simple check for empty cells

Dim r As Range
  Set r = ActiveCell
  If r.Text = "" Then
    MsgBox "Active cell contains nothing or an empty string."
  End If

A runtime-error 13 will appear in this code fragment if you try r.Value instead. Even if you’re sure that the full value is displayed, many constructs are inappropriate. For example

Set r = ActiveCell
If r.Text = "#VALUE!" Then
  MsgBox "Active cell contains Value Error."
End If
If r.Text = "FALSE" Then
  MsgBox "Active cell contains FALSE."
End If

The displayed text depends on the displayed language of your Excel application. Thus, the code of the example will not have the expected result for every language.

We arrive at the conclusion that you should not use the Text property of an Excel Range if you don’t know what it returns. And, if you know what it returns, you won’t use it in most cases.