Page 224 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

An Excel password can be up to 15 characters in length, but it’s a good idea to require users
to use passwords that are at least 8 characters long.

For more information on using the LEN to verify that a password contains at least one non-alphanumeric
character, see “Returning Characters from Arbitrary Positions in a String” later in this chapter.

Inside Out

When to Validate, and When to Use an If…Then Statement
When you’re working with values that need to be a certain length, you have a choice of
methods to make certain everything falls into line. Way back in Chapter 8, you learned how
to use the Range object’s Validation property to establish criteria that a cell’s value must
meet before being accepted. You can set the Validation object’s Type parameter to xlValidate-
TextLength to have Excel check a cell’s value to ensure it is of the proper length. Of course,
you can use an If…Then rule to the same effect. So why would you choose one method over
the other?

● You should use a Range object’s Validation property when You want to create a
single rule that is easily expressed using the Validation property’s parameters.
Chapter 9
● You want the input box to have a specific title and to be grouped with the validation
rules.
● You want to display a specific type of message box (information, warning, or stop)
and have the behavior (whether to move to the next cell or not) programmed as part
of the message box type.
● You want to have all the criteria stored in a single object.
● You should use an If…Then…Else statement when You want to have multiple crite­
ria and find that using multiple If...Then…Else constructions is easier than using the
Validation object’s Modify method.
● You want to have conditional criteria that change depending on a set of circum­
stances. For example, the maximum credit limit an employee can assign to a cus­
tomer could vary by employee.

Concatenating Text from Two or More Cells or Variables

Some of the procedures you’ve encountered so far in this book have generated message boxes
using the text from one or more variables or worksheet cells as part of the message box’s
prompt. The authors of this book admit to playing a bit fast and loose with the order of top­
ics, but now is the time to bring everything up to date by showing you how to add text from
a cell, a variable, or a literal into a single output. You use the & operator.

198
Part 4: Advanced VBA