Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

It's often difficult to follow the logic in an application developed by someone other than yourself. To help you understand my work, I included lots of comments in the code and described the general program flow in the preceding sections. But, if you really want to understand this application, I suggest that you use the Debugger to step through the code.

At the very least, the Loan Amortization Wizard demonstrates some useful techniques and concepts that are important for Excel developers:

• Modifying the Ribbon.

• Using a wizard-like UserForm to gather information.

• Setting the Enabled property of a control dynamically.

• Linking a TextBox and a SpinButton control.

• Displaying online help to a user .

• Naming cells with VBA.

• Writing and copying formulas with VBA.

• Reading from and writing to the Windows Registry.

When developing user-oriented applications, you need to keep in mind many things. Let the following checklist serve as a reminder:

• Do the dialog boxes all work from the keyboard ? Don't forget to add hot keys and check the tab order carefully .

• Did you make any assumptions about directories ? If your application reads or writes files, you can't assume that a particular directory exists or that it's the current directory.

• Did you make provisions for canceling all dialog boxes ? You can't assume that the user will end a dialog box by clicking the OK button.

• Did you assume that no other worksheets are open ? If your application is the only workbook open during testing, you could overlook something that happens when other workbooks are open.

• Did you assume that a workbook is visible ? It's possible, of course, to use Excel with no workbooks visible.

• Did you attempt to optimize the speed of your application ? For example, you often can speed up your application by declaring variable types and defining object variables .

• Are your procedures adequately documented ? Will you understand your code if you revisit it in six months?

• Did you include appropriate end-user documentation ? Doing so often eliminates (or at least reduces ) the number of follow-up questions.

• Did you allow time to revise your application ? Chances are the application won't be perfect the first time out. Build in some time to fix it.

Developing user-oriented applications in Excel is not easy. You must be keenly aware of how people will use (and abuse) the application in real life. Although I tried to make this application completely bulletproof, I did not do extensive real-world testing, so I wouldn't be surprised if it fails under some conditions.