CheckBox, OptionButton, ListBox, DropDown (ComboBox), ScrollBar, Spinner
CommandButton1_Click)
Executing the development effort
After you identify user needs, determine the approach you'll take to meet those needs, and decide on the components that you'll use for the user interface. Next, it's time to get down to the nitty-gritty and start creating the application. This step, of course, constitutes a great deal of the total time you spend on a particular project.
How you go about developing the application depends on your personal style and the nature of the application. Except for simple fill-in-the-blanks template workbooks, your application will probably use macros. Creating macros in Excel is easy, but creating good macros is difficult.
Concerning Yourself with the End User
In this section, we 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 of 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. 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
