Microsoft Office Tutorials and References
In Depth Information
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.
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
xlValidateTextLength 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
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.
You want the input box to have a specific title and to be grouped with the validation
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.