Microsoft Office Tutorials and References
In Depth Information
new form into use—the data entry folks (and it could be you) get so used to typing a cus
tomer’s identification code first that they forget they’re supposed to start with the company
ISTEXT is shown in all capital letters because it is one of many worksheet functions you can call using
VBA code. For more information on calling worksheet functions such as ISTEXT in your VBA code, see
the Inside Out titled "Is a Function Built into VBA or Not?" later in this chapter.
You can also use the ISTEXT function to ensure that data imported from an external source
is formatted as you expected it to be before you run a set of procedures. If you’ve ever run a
text-processing routine on non-text data, you know precisely the type of chaos a little checking
can prevent. You can also use ISTEXT as a basic function when you might not be sure pre
cisely what sort of text data you’ll receive, but so long as you do know it’s text you can write a
procedure to cycle through the non-empty cells in a worksheet and perform at least this rudi
So what do you do if the data you want to work with as a string is actually a number? In that
case, you can use the STR function to represent the number as a string. The STR function’s
syntax is minimal: STR(number) , where number is the variable name or address of the cell
that contains the number you’re changing to text.
For more information on validating cell data, see "Getting Data Entry Right the First Time" on page 187.
ISTEXT is not the only function available in the IS family; Table 9-1 lists the worksheet func
tions you can use to determine whether a value fits a given category.
Table 9-1. The IS Family of Functions
Returns True If This Condition Is Met
The value refers to an empty cell.
The value refers to any error value except #N/A (value not available).
The value refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!,
#NUM!, #NAME?, or #NULL!).
The value refers to a logical value.
The value refers to the #N/A (value not available) error value.
The value refers to any item that is not text. (Note that this function
returns TRUE if value refers to a blank cell.)
The value refers to a number.
The value refers to a reference.
The value refers to text.
When you’re ready to write your string data to a cell, you need to be sure the cell is prepared
to accept text data. For example, if the cells are formatted using the General number format
(the default) and you try to write a string that appears to be a number (for example,