Excel 2019 Power Programming with VBA. Michael Alexander

you create your application, you need to test it. Testing 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 it. 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.

      It's important to test thoroughly any spreadsheet application that you develop for others. And depending on its eventual audience, you may want to make your application bulletproof. In other words, try to anticipate all the errors and screw-ups that could possibly occur and make concerted efforts to avoid them—or, at least, to handle them gracefully. This foresight 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're the ones who will be using your product. (See the upcoming sidebar “What about beta testing?”)

Although you can't 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 the user 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, these types of issues become very familiar, and you account for them without even thinking.

      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 may want to consider a beta test. This test enables your intended users to use your application in its proposed setting on different hardware (usually).

      The beta period should begin after you've completed all of 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, email, 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.

      Making the application bulletproof

      If you think about it, destroying a spreadsheet is fairly easy. 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 may be in trouble, and you will probably be blamed for it.

      Obviously, you can easily 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 users can't change them. Locking 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 users can perform on a protected sheet (see Figure 1.5).FIGURE 1.5 Using the Protect Sheet dialog box to specify what users can and can't 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, hiding 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 ➪ Protect ➪ Protect Workbook command for this purpose.

       Lock objects on the worksheet Use the Properties section in the task pane to lock objects (such as shapes) and prevent them from being moved or changed. To access this section of the task pane, right-click the object and choose Size and Properties. Locking objects takes effect only when the document is protected using the Review ➪ Protect ➪ Protect Sheet command. By default, all objects are locked.

       Hide rows, columns, sheets, and documents You can hide rows, columns, sheets, and entire workbooks. Doing so helps prevent the worksheet from looking cluttered, and it also provides some modest 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 can't be overwritten with any changes. You make this designation in the General Options dialog box. Display this dialog box by choosing File ➪ Save As, choosing a directory, and then clicking the Tools button found on the Save As dialog box. Choose General Options to specify the appropriate password.

       Assign a password You can assign a password to prevent unauthorized users from opening your file. Choose File ➪ Info ➪ Protect Workbook ➪ Encrypt with Password.

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

      Excel passwords are not foolproof

      Be aware that Excel passwords can often be easily circumvented using commercially available password-breaking programs. 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 or she probably can.

      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 of 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.

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

       Strive for consistency When designing dialog boxes, for example, try to emulate the look and feel of Excel's dialog boxes whenever possible. Be consistent with formatting,