Accepting Worksheet Input

When you go to accept worksheet-based input, you'll encounter many of the issues that surround structured output. Chiefly, you need to develop a strategy for ensuring that the input is in the location you expect and that any supporting objects, such as specific worksheets or named ranges, are also present.

Another facet of accepting worksheet-based input makes accepting input slightly more difficult than displaying output. Usually your procedures expect input of a specific data type such as a date, a monetary amount, or text. If these procedures get an input that uses a different data type, a run-time error could occur. Therefore, you need to develop a way to either enforce the type of data that can be entered, account for the possibility of other data types in your procedures or, preferably, use a combination of these two strategies.

WARNING You can't rely on Excel's Data Validation feature alone for validating input. You can easily circumvent the Data Validation feature by entering an invalid value in a cell that doesn't have any validation rules and copying/ pasting into the cell containing validation. Because many people use copy/paste to enter values in Excel, this occurs more than you might think.

One way in which you can enforce the value that can be entered in a worksheet is to write a procedure to validate the cell and hook that procedure up to the worksheet change event. For an extra level of protection, validate the cell's value before you use it in other procedures. For example, if you have a procedure that expects a currency value and you need to obtain this value from a worksheet cell, you may want to use a procedure to validate that the range has an appropriate value (see Listing 8.12).

0 0

Post a comment