Making the application bulletproof

If you think about it, it's fairly easy to destroy a spreadsheet. Erasing one critical formula or value often causes errors throughout the entire worksheet — and perhaps even other dependent worksheets. Even worse, if the damaged workbook is saved, it replaces the good copy on disk. Unless a backup procedure is in place, the user of your application could be in trouble, and you'll probably be blamed for it.

Obviously, it's easy to see why you need to add some protection when users — especially novices — will be using your worksheets. Excel provides several techniques for protecting worksheets and parts of worksheets:

♦ Lock specific cells: You can lock specific cells (by using the Protection tab in the Format Cells dialog box) so that they cannot be changed. This takes effect only when the document is protected with the Tools ^ Protection ^ Protect Sheet command.

♦ Protect an entire workbook: You can protect an entire workbook — the structure of the workbook, the window position and size, or both. Choose the Tools ^ Protection ^ Protect Workbook command for this purpose.

What about Beta Testing?

Software manufacturers typically have a rigorous testing cycle for new products. After extensive internal testing, the prerelease product is usually sent to a group of interested users for beta testing. This phase often uncovers additional problems that are usually corrected before the product's final release.

If you're developing an Excel application that more than a few people will use, you might want to consider a beta test. This enables your application to be used in its intended setting on different hardware (usually) and by the intended users.

The beta period should begin after you've completed all your own testing and you feel that the application is ready to distribute. You'll need to identify a group of users to help you. The process works best if you distribute everything that will ultimately be included in your application: user documentation, installation program, help, and so on. You can evaluate the beta test in a number of ways, including face-to-face discussions, questionnaires, and phone calls.

You will almost always become aware of problems that you need to correct or improvements that you need to make before you undertake a widespread distribution of the application. Of course, a beta testing phase takes additional time, and not all projects can afford that luxury.

How Secure Are Excel's Passwords?

As far as I know, Microsoft has never advertised Excel as a secure program. And for good reason: It's actually quite easy to thwart Excel's password system. Several commercial programs are available that can break passwords. Excel 2002 and later seem to have stronger security than previous versions. Bottom line? Don't think of password protection as foolproof. Sure, it will be effective for the casual user. But if someone really wants to break your password, it can probably be done.

♦ Hide the formulas in specific cells: You can hide the formulas in specific cells (by using the Protection tab in the Format Cells dialog box) so that others can't see them. Again, this takes effect only when the document is protected by choosing the Tools ^ Protection ^ Protect Sheet command.

♦ Lock objects on the worksheet: You can lock objects on the worksheet (by using the Protection tab in the Format Object dialog box). This takes effect only when the document is protected via the Tools ^ Protection ^ Protect Sheet command.

♦ Hide rows, columns, sheets, and documents: You can hide rows (choose Format ^ Row ^ Hide), columns (choose Format ^ Column ^ Hide), sheets (choose Format ^ Sheet ^ Hide), and documents (choose Window ^ Hide). This helps prevent the worksheet from looking cluttered and also provides some protection against prying eyes.

♦ Designate an Excel workbook as read-only recommended: You can designate an Excel workbook as read-only recommended (and use a password) to ensure that the file cannot be overwritten with any changes. You do this in the Save Options dialog box. Display this dialog box by choosing Tools ^ General Options in the Save As dialog box.

♦ Assign a password: You can assign a password to prevent unauthorized users from opening your file. You do this in the Save Options dialog box. Display this dialog box by choosing Tools ^ General Options in the Save As dialog box.

♦ Use a password-protected add-in: You can use a password-protected add-in, which doesn't allow the user to change anything on its worksheets.

Beginning with Excel 2002,you have several new options in the area of sheet protection. Choose Tools ^ Protection ^ Protect Sheet, and you'll see the Protect Sheet dialog box. This allows you to specify exactly which actions can be performed on a protected sheet. Also, the Save Options dialog box has an Advanced button. Clicking this button allows you to set the encryption level of the workbook.

+1 0

Post a comment