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
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.
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
Value2 property of the cell.
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.
It’s a good post.