Concerning Yourself with the End User

In this section, I discuss the important development issues that surface as your application becomes more and more workable and as the time to package and distribute your work grows nearer.

Testing the application

How many times have you used a commercial software application, only to have it bomb out on you at a crucial moment? Most likely, the problem was caused by insufficient testing that didn't catch all the bugs. All nontrivial software has bugs, but in the best software, the bugs are simply more obscure. As you'll see, you sometimes must work around the bugs in Excel to get your application to perform properly.

After you create your application, you need to test it. This is one of the most crucial steps; it's not uncommon to spend as much time testing and debugging an application as you did creating the application in the first place. Actually, you should be doing a great deal of testing during the development phase. After all, whether you're writing a VBA routine or creating formulas in a worksheet, you want to make sure that the application is working the way it's supposed to work.

Like standard compiled applications, spreadsheet applications that you develop are prone to bugs. A bug can be defined as (1) something that does happen but shouldn't happen while a program (or application) is running, or (2) something that doesn't happen when it should happen. Both species of bugs are equally nasty, and you should plan on devoting a good portion of your development time to testing the application under all reasonable conditions and fixing any problems that you find. In some cases, unfortunately , the problems aren't entirely your fault. Excel, too, has its problems (see the "Bugs? In Excel?" sidebar).

Bugs? In Excel?

You might think that a product like Excel, which is used by millions of people throughout the world, would be relatively free of bugs. Think again. Excel is such a complex piece of software that it is only natural to expect some problems with it. And Excel does have some problems.

Getting a product like Excel out the door is not easy, even for a company like Microsoft with seemingly unlimited resources. Releasing a software product involves compromises and trade-offs. It's commonly known that most major software vendors release their products with full knowledge that they contain bugs. Most of the bugs are considered insignificant enough to ignore. Software companies could postpone their releases by a few months and fix many of them, but software, like everything else, is ruled by economics. The benefits of delaying a product's release often do not exceed the costs involved. Although Excel definitely has its share of bugs, my guess is that the majority of Excel users never encounter one.

In this book, I point out the problems with Excel that I know about. You'll surely discover some more on your own. Some problems occur only with a particular version of Excel - and under a specific configuration involving hardware and/or software. These are the worst bugs of all because they aren't easily reproducible.

So what's a developer to do? It's called a workaround. If something that you try to do doesn't work - and all indications say that it should work - it's time to move on to Plan B. Frustrating? Sure. A waste of your time? Absolutely. It's all part of being a developer.

What about Beta Testing?

Software manufacturers typically have a rigorous testing cycle for new products. After extensive internal testing, the pre-release 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, the 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 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.

I probably don't need to tell you to thoroughly test any spreadsheet application that you develop for others. And depending on its eventual audience, you might want to make your application bulletproof. In other words, try to anticipate all the errors and screw-ups that could possibly occur, making concerted efforts to avoid them - or, at least, to handle them gracefully. This not only helps the end user but also makes it easier on you and protects your reputation. Also consider using beta testing; your end users are likely candidates because they are the ones who will be using your product. See the upcoming sidebar "What about Beta Testing?"

Although you cannot conceivably test for all possibilities, your macros should be able to handle common types of errors. For example, what if the user enters a text string instead of a numeric value? What if the user tries to run your macro when a workbook isn't open ? What if he or she cancels a dialog box without making any selections? What happens if the user presses Ctrl+F6 and jumps to the next window? When you gain experience, issues like these become very familiar, and you account for them without even thinking.

Making the application bulletproof

If you think about it, it's fairly easy to destroy a spreadsheet. Erasing one critical formula or value can cause 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 Review Changes Protect Sheet command. The Protect Sheet dialog box has options that allow you to specify which actions can be performed on a protected sheet. See Figure 6-5.

Figure 6-5: Using the Protect Sheet dialog box to specify what users can and cannot do.
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 Review Changes 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. Use the Review Changes Protect Workbook command for this purpose.
Lock objects on the worksheet: Use the Properties tab in the Size and Properties dialog box to lock objects (such as shapes ) and prevent them from being moved or changed. To access the Size and Properties dialog box, select the object and then click the dialog box launcher in the Drawing Tools Format Size group. Locking objects takes effect only when the document is protected via the Review Changes Protect Sheet command. By default, all objects are locked.
Hide rows, columns , sheets, and documents: You can hide rows, columns, sheets, and entire workbooks. This helps prevent the worksheet from looking cluttered and also provides some protection against prying eyes.

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 circumvent Excel's password system. Several commercial programs are available that can break passwords. Excel 2002 and later versions seem to have stronger security than previous versions, but they can still be cracked by a determined user. 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, he can probably do so.

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 General Options dialog box. Display this dialog box by choosing File Save As. In the Save As dialog box, click the Tools button and choose General Options.
Assign a password: You can assign a password to prevent unauthorized users from opening your file. You do this in the General Options dialog box. Display this dialog box by choosing File Save As. In the Save As dialog box, click the Tools button and choose General Options.
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.

Making the application aesthetically appealing and intuitive

If you've used many different software packages, you've undoubtedly seen examples of poorly designed user interfaces, difficult-to-use programs, and just plain ugly screens. If you're developing spreadsheets for other people, you should pay particular attention to how the application looks.

How a computer program looks can make all the difference in the world to users, and the same is true with the applications that you develop with Excel. Beauty, however, is in the eye of the beholder. If your skills lean more in the analytical direction, consider enlisting the assistance of someone with a more aesthetic sensibility to provide help with design.

The good news is that Excel 2007 makes it relatively easy to create better-looking spreadsheets. If you stick with the pre-designed cell styles, your work stands a good chance of looking good. And, with the click of a mouse, you can apply a new theme that completely transforms the look of the workbook - and still looks good. Unfortunately, Excel 2007 adds nothing new in the area of UserForm design, so you're on your own in that area.

End users appreciate a good-looking user interface, and your applications will have a much more polished and professional look if you devote some additional time to design and aesthetic considerations. An application that looks good demonstrates that its developer cared enough about the product to invest some extra time and effort. Take the following suggestions into account:

Strive for consistency: When designing dialog boxes, for example, try to emulate Excel's dialog box look and feel whenever possible. Be consistent with formatting, fonts, text size, and colors.
Keep it simple: A common mistake that developers make is trying to cram too much information into a single screen or dialog box. A good rule is to present only one or two chunks of information at a time.
Break down input screens: If you use an input screen to solicit information from the user, consider breaking it up into several, less crowded screens. If you use a complex dialog box, you might want to break it up by using a MultiPage control, which lets you create a familiar tabbed dialog box.
Don't overdo color : Use color sparingly. It's very easy to overdo it and make the screen look gaudy.
Monitor typography and graphics: Pay attention to numeric formats and use consistent typefaces , font sizes, and borders.

Evaluating aesthetic qualities is very subjective . When in doubt, strive for simplicity and clarity.

Note	Previous versions of Excel used a pallet of 56 colors. That restriction has been removed, and Excel 2007 supports more than 16 million colors.

Creating a user Help system

With regard to user documentation, you basically have two options: paper-based documentation or electronic documentation. Providing electronic help is standard fare in Windows applications. Fortunately, your Excel applications can also provide help - even contextsensitive help. Developing help text takes quite a bit of additional effort, but for a large project, it may be worth it. Figure 6-6 shows an example of a custom Help system in compiled HTML format.

Figure 6-6: An example of custom help file for an Excel add-in.

Another point to consider is support for your application. In other words, who gets the phone call if the user encounters a problem? If you aren't prepared to handle routine questions, you need to identify someone who is. In some cases, you want to arrange it so that only highly technical or bug- related issues escalate to the developer.

CROSS-REFERENCE

In Chapter 24, I discuss several alternatives for providing help for your applications.

Documenting the development effort

Putting a spreadsheet application together is one thing. Making it understandable for other people is another. As with traditional programming, it's important that you thoroughly document your work. Such documentation helps you if you need to go back to it (and you will), and it helps anyone else whom you might pass it on to.

Tip

You might want to consider a couple of things when you document your project. For example, if you were hired to develop an Excel application, you might not want to share all your hard-earned secrets by thoroughly documenting everything . If this is the case, you should maintain two versions: one thoroughly documented (for your own reference) and the other partially documented (for other users).

How do you document a workbook application? You can either store the information in a worksheet or use another file. You can even use a paper document if you prefer. Perhaps the easiest way is to use a separate worksheet to store your comments and key information for the project. For VBA code, use comments liberally. (VBA text preceded with an apostrophe is ignored because that text is designated a comment.) Although an elegant piece of VBA code can seem perfectly obvious to you today, when you come back to it in a few months, your reasoning might be completely obscured unless you use the VBA comment feature.

Why Is There No Runtime Version of Excel?

When you distribute your application, you need to be sure that each end user has a licensed copy of the appropriate version of Excel. It's illegal to distribute a copy of Excel along with your application. Why, you might ask, doesn't Microsoft provide a runtime version of Excel? A runtime version is an executable program that can load files but not create them. With a runtime version, the end user wouldn't need a copy of Excel to run your application. (This is common with database programs.)

I've never seen a clear or convincing reason why Microsoft does not have a runtime version of Excel, and no other spreadsheet manufacturer offers a runtime version of its product, either. The most likely reason is that spreadsheet vendors fear that doing so would reduce sales of the software. Or, it could be that developing a runtime version would require a tremendous amount of programming that would just never pay off.

On a related note Microsoft does offer an Excel file viewer . This product lets you view Excel files if you don't own a copy of Excel. Macros, however, will not execute. You can get a copy of this free file viewer from the Microsoft Web site (http://www.office.microsoft.com/downloads).

Distributing the application to the user

You've completed your project, and you're ready to release it to the end users. How do you go about doing this? You can choose from many ways to distribute your application, and the method that you choose depends on many factors.

You could just hand over a CD-ROM, scribble a few instructions, and be on your way. Or, you may want to install the application yourself - but this is not always feasible . Another option is to develop an official setup program that performs the task automatically. You can write such a program in a traditional programming language, purchase a generic setup program, or write your own in VBA.

Excel 2000 and later incorporates technology to enable developers to digitally sign their applications. This process is designed to help end users identify the author of an application, to ensure that the project has not been altered , and to help prevent the spread of macro viruses or other potentially destructive code. To digitally sign a project, you first apply for a digital certificate from a formal certificate authority (or, you can self-sign your project by creating your own digital certificate). Refer to the Help system or the Microsoft Web site for additional information.

Updating the application when necessary

After you distribute your application, you're finished with it, right? You can sit back, enjoy yourself, and try to forget about the problems that you encountered (and solved ) during the course of developing your application. In rare cases, yes, you may be finished. More often, however, the users of your application will not be completely satisfied. Sure, your application adheres to all the original specifications, but things change. Seeing an application working frequently causes the user to think of other things that the application could be doing. I'm talking updates.

When you need to update or revise your application, you'll appreciate that you designed it well in the first place and that you fully documented your efforts. If not, well we learn from our experiences.