Page 223 - Microsoft Office Excel 2003 Programming Inside Out

Manipulating Data with VBA

untranslated formatting instructions, or the extra space might have been added because the
fields in the file had fixed lengths and the originating program padded the string with spaces
to make up the difference.
Because CLEAN and TRIM are useful worksheet functions, the fine minds at Microsoft
decided to let you use them in Excel VBA. You can call the CLEAN and TRIM functions, plus
a host of other useful worksheet functions, by adding the name of the desired function as a
property of the Application.WorksheetFunction object. Such calls would look like this:

ActiveCell.Value = Application.WorksheetFunction.Clean(ActiveCell.Value)
ActiveCell.Value = Application.WorksheetFunction.Trim(ActiveCell.Value)

Determining the Number of Characters in a String
Another of the basic text-processing operations that you can perform in Excel VBA is to
determine the number of characters in a string, which you do using the LEN function. And,
just as the ISTEXT function is useful for validating data, you can use the LEN function to
ensure the data you’re inputting, importing, or exporting is of the expected length. One
example of an occasion when determining that data is of a particular length is if you’re typing
in International Standard Book Numbers (ISBNs), which are used to identify books. An Chapter 9
ISBN is exactly 10 characters long, so if you or a colleague is typing in the title, the authors,
the ISBN, and the price of every book in your company’s library, you should make sure the
ISBNs are of the correct length. Although ISBNs are one example when verifying the length
of worksheet or UserForm data would come in handy, the applications are nearly infinite. If
your order or customer numbers are all of a specified length, if your product codes are eight
characters long but your customer codes are nine characters long, or if you want to make sure
no stray digits were mistakenly added to a phone number, you can use the LEN function to
verify that your rules are being followed.
The following code verifies the value in the active cell to ensure the product code contained
in the cell is exactly 10 characters in length:
If LEN(ActiveCell.Value) <> 10 Then
MsgBox ("The product code in this cell is not of the required length.")
ActiveCell.Value = "Error"
End If

If you work with older database management systems, or with a database that has set charac­
ter lengths for each of its fields, you should be sure to add a validation rule to a column so
that you can ensure every entry was read in correctly. And, although many databases are
small enough that you can waste a bit of storage by allocating more space than is strictly nec­
essary to hold the field’s value, it’s a good idea to limit the size of every field (with the possible
exception of a comment field) to the minimum possible number of characters.
Another good use for the LEN function is to guarantee that the passwords your colleagues
assign to workbooks and worksheets are of a minimum length. As mentioned in Chapter 7,
the Excel password protection scheme won’t prevent your data from being compromised, but
you can make an attacker’s job much more time-consuming by assigning longer passwords.

197
Part 4 Advanced VBA