Customizing the Ribbon

The Ribbon UI introduced in Excel 2007 is a dramatic shift in user interface design. Fortunately, the developer has a fair amount of control over the Ribbon. Although Excel allows the end user to modify the Ribbon, making UI changes via code isn’t a simple task.

Customizing shortcut menus

Excel allows the VBA developer to customize the right-click shortcut menus. Right-click menus can offer users a way to easily trigger an action without having to move too far from the range in which they are working. Figure 1.1 illustrates a customized shortcut menu that appears when a cell is right-clicked.

Creating shortcut keys

Another user interface option at your disposal is a custom shortcut key. Excel lets you assign a Ctrl key (or Shift+Ctrl key) combination to a macro. When the user presses the key combination, the macro executes.

Be aware, however, of two caveats. First, make it clear to the user which keys are active and what they do. Second, do not assign a key combination that’s already used for something else. A key combination that you assign to a macro takes precedence over the built-in shortcut keys. For example, Ctrl+S is a built-in Excel shortcut key used to save the current file. If you assign this key combination to a macro, you lose the capability to save the file with Ctrl+S. Remember that shortcut keys are case sensitive, so you can use a combination such as Ctrl+Shift+S.

Creating custom dialog boxes

Anyone who has used a personal computer for any length of time is undoubtedly familiar with dialog boxes. Consequently, custom Excel dialog boxes can play a major role in the user interfaces that you design for your applications. Figure 1.2 shows an example of a custom dialog box.

A custom dialog box is known as a UserForm. A UserForm can solicit user input, get a user’s options or preferences, and direct the flow of your entire application. The elements that make up a UserForm (buttons, drop-down lists, check boxes, and so on) are called controls — more specifically, ActiveX controls. Excel provides a standard assortment of ActiveX controls, and you can also incorporate third-party controls.

After adding a control to a dialog box, you can link it to a worksheet cell so that it doesn’t require any macros (except a simple macro to display the dialog box). Linking a control to a cell is easy, but it’s not always the best way to get user input from a dialog box. Most of the time, you want to develop VBA macros that work with your custom dialog boxes.

Using ActiveX controls on a worksheet

Excel also lets you add UserForm ActiveX controls to a worksheet’s drawing layer (an invisible layer on top of a sheet that holds pictures, charts, and other objects). Figure 1.3 shows a simple worksheet model with several UserForm controls inserted directly in the worksheet. This sheet contains the following ActiveX controls: a CheckBox, a ScrollBar, and two sets of OptionButtons. This workbook uses no macros. Rather, the controls are linked to worksheet cells.

Perhaps the most common control is a CommandButton. By itself, a CommandButton doesn’t do anything, so you need to attach a macro to each CommandButton.

Using dialog box controls directly in a worksheet often eliminates the need for custom dialog boxes. You can often greatly simplify the operation of a spreadsheet by adding a few ActiveX controls (or Form controls) to a worksheet. These ActiveX controls let the user make choices by operating familiar controls rather than making entries in cells.

Access these controls by using the Developer ➜ Controls ➜ Insert command (see Figure 1.4). If the Developer tab isn’t on the Ribbon, add it by using the Customize Ribbon tab of the Excel Options dialog box.

The controls come in two types: Form controls and ActiveX controls. Both sets of controls have their advantages and disadvantages. Generally, Form controls are easier to use, but ActiveX controls are a bit more flexible. Table 1.1 summarizes these two classes of controls.

Executing the development effort

After you identify user needs, determine the approach that you’ll take to meet those needs, and decide on the components that you’ll use for the user interface, it’s time to get down to the nitty-gritty and start creating the application. This step, of course, comprises a great deal of the total time that 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.

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 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 the application. 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 thoroughly test 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 he 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.

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’ll 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).
• 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 ➜ Changes ➜ 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 ➜ Changes ➜ 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 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, clicking the Tools button, and then choosing General Options.
• 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 its worksheets.

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

The good news is that features found in Excel 2007 and later make creating better-looking spreadsheets a relatively easy task. If you stick with predesigned cell styles, your work stands a good chance of looking good. And, with the click of a mouse, you can apply a new theme that transforms the look of the workbook — and still looks good.

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 very 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, you basically have two options: paper-based documentation or electronic documentation. 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.

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 you if you need to go back to it (and you will), and it helps anyone else whom you might pass it on to.

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 CD-ROM 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 digitally sign their applications. 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 digitally sign a project, 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 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.

The user’s installed version of Excel

Although Excel 2016 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 service packs and security updates Microsoft releases. Although rare, some changes introduced in these releases can cause certain components of your application to no longer work as designed.

Language issues

Consider yourself fortunate if all 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.

System speed

You’re probably a fairly advanced computer user and tend to keep your hardware reasonably up to date. In other words, you have a fairly powerful system that is probably better than the average user’s system. In some cases, you’ll know exactly what hardware the end users of your applications are using. If so, testing your application on that system is vital. A procedure that executes almost instantaneously on your system may take several seconds on another system. In the world of computers, several seconds may be unacceptable.

Video modes

As you probably know, users’ video displays vary widely. Currently, the most commonly used video resolution is 1280 x 1024, followed closed by 1024 x 768. Systems with a resolution of 800 x 600 are becoming much less common, but quite a few are still in use. Higher resolution displays and even dual displays are becoming increasingly common. Just because you have a super-high-resolution monitor, you can’t assume that everyone else does.

Video resolution can be a problem if your application relies on specific information being displayed on a single screen. For example, if you develop an input screen that fills the screen in 1280 x 1024 mode, users with a 1024 x 768 display won’t be able to see the whole input screen without scrolling or zooming.

Also, it’s important to realize that a restored (that is, not maximized or minimized) workbook is displayed at its previous window size and position. In the extreme case, it’s possible that a window saved by using a high-resolution display may be completely off the screen when opened on a system running in a lower resolution.

Unfortunately, you can’t automatically scale things so that they look the same regardless of the display resolution. In some cases, you can zoom the worksheet (using the Zoom control in the status bar), but doing so reliably may be difficult. Unless you’re certain of the video resolution that the users of your application will use, you should probably design your application so it works with the lowest common denominator — 800 x 600 or 1024 x 768 mode.

As you discover later in the book, you can determine the user’s video resolution by using Windows API calls from VBA. In some cases, you may want to programmatically adjust things, depending on the user’s video resolution.