Excel 2019 Power Programming with VBA. Michael Alexander

Чтение книги онлайн.

СКАЧАТЬ (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, 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 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) СКАЧАТЬ