Excel 2019 Power Programming with VBA. Michael Alexander

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 may 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 easy to overdo color 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 subjective. When in doubt, strive for simplicity and clarity.

      Creating a user Help system

      With regard to user documentation, it's a best practice to provide users with paper-based documentation or electronic documentation (or both). Providing electronic help is standard fare in Windows applications. Fortunately, your Excel applications can also provide help—even context-sensitive help. Developing help text takes quite a bit of additional effort, but for a large project it may be worth it.

      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.

      

In Chapter 19, “Providing Help for Your Applications,” we 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 if you need to go back to it (and you will), and it helps anyone else whom you might pass it onto.

      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 as 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 may be completely obscured unless you use the VBA comment feature.

      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 distributing it? 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 download link or thumb drive, scribble a few instructions, and be on your way. Or, you may want to install the application yourself—but this approach isn't 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 incorporates technology to enable developers to sign their applications digitally. 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 sign a project digitally, 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 website 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 development. In rare cases, yes, you may be finished. More often, however, the users of your application won't be completely satisfied. Sure, your application adheres to all of the original specifications, but things change. Seeing an application working often causes the user to think of other things that the application could be doing.

      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.

Other Development Issues

      You need to keep several other issues in mind when developing an application—especially if you don't know exactly who will be using the application. If you're developing an application that will have widespread use (a shareware application, for example), you have no way of knowing how the application will be used, what type of system it will run on, or what other software will be running concurrently.

      The user's installed version of Excel

      Although Excel 2019 is available, many large corporations are still using earlier versions of Excel. Unfortunately, there is no guarantee that an application developed for, say, Excel 2010 will work perfectly with later versions of Excel. If you need your application to work with a variety of Excel versions, the best approach is to work with the lowest version—and then test it thoroughly with all other versions.

      Also, be aware of any security updates or new changes to Excel released with service packs (for stand-alone versions of Excel). If some of your users are on Office 365, be aware that Microsoft has adopted an agile release cycle, allowing it to release updates to Office 365 practically on a monthly basis. This is great news for those who love seeing new features added to Excel. It's not so great if you're trying to manage compatibility with your application. Although rare, some changes introduced in these releases can cause certain components of your application no longer to work as designed.

      

Compatibility issues are discussed in Chapter 21, “Understanding Compatibility Issues.”

      Language issues

Consider yourself fortunate if all of your end users have the English language version of Excel. Non-English versions of Excel aren't always 100 percent compatible, so that means additional testing on your part. In addition, keep in mind that two users can both be using the English language version of Excel yet use different Windows regional settings. In some cases, you may need to be aware of potential problems.

      

We briefly discuss language issues in Chapter 21.

      System speed

      Although system speed and processing power has become less of an issue on modern PCs and devices, testing your application for performance and speed is still a recommended best practice. A procedure that executes almost instantaneously on your system may take several seconds to execute on another system. In the world of computers, several seconds may be unacceptable.

      TIP

      When you gain more experience with