Page 220 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

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
name now.

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­
mentary check.
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.

Chapter 9
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
Function Returns True If This Condition Is Met
ISBLANK The value refers to an empty cell.
ISERR The value refers to any error value except #N/A (value not available).
ISERROR The value refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!,
#NUM!, #NAME?, or #NULL!).
ISLOGICAL The value refers to a logical value.
ISNA The value refers to the #N/A (value not available) error value.
ISNONTEXT The value refers to any item that is not text. (Note that this function
returns TRUE if value refers to a blank cell.)
ISNUMBER The value refers to a number.
ISREF The value refers to a reference.
ISTEXT 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,

194
Part 4: Advanced VBA