Page 221 - Microsoft Office Excel 2003 Programming Inside Out

Manipulating Data with VBA

0000097239) to the cell, the leading zeros will be deleted. You can ensure Excel will treat your
input as a string by changing the cell’s number format to Text. You would perform this action
in the Excel interface by clicking Format, Cells, displaying the Number tab page, and clicking
Text. You can do the same thing using the Range object’s NumberFormat property.

For example, if you wanted to change the number format of the active cell (which is consid­
ered a range in this context) to Text, you would use the following line of code:

ActiveCell.NumberFormat = "@"
You can change the number format of a range to any of the values displayed in the list of
Custom number formats available in the Format Cells dialog box (shown in Figure 9-1). If
you’re not sure which format to use, assign the format to a cell using the Format Cells dialog
box, and then click Custom to display the code. Be sure to enclose the code in quotes!

Chapter 9

Figure 9-1. You can change the format of your cell to any of the formats in the Custom list.

Preparing String Data for Processing
Once you’ve determined that the data you’re about to perform text operations on is, in fact,
text, you can take additional steps to ensure that the data will look its best when you process
it. There are two functions you can use to process your data: CLEAN and TRIM. The CLEAN
function strips out any nonprinting characters from a string. Nonprinting characters are also
known as control characters, because they’re usually entered by pressing the Ctrl key while
typing another key sequence. Nonprinting characters don’t often show up in text files or
worksheets, but if you import data from another program into Excel you might find them
sneaking in as interpretations of formatting or data structure instructions that weren’t
stripped out when the original data was saved.
195
Part 4 Advanced VBA