Excel 2019 Power Programming with VBA. Michael Alexander

at your disposal, and you can choose the one most appropriate for the project at hand. Often, a bit of creative thinking yields an unusual approach that's vastly superior to other methods.

      Consider some general options at the beginning stage of this planning period, such as the following:

       File structure Think about whether you want to use one workbook with multiple sheets, several single-sheet workbooks, or a template file.

       Data structure You should always consider how your data will be structured and also determine whether you will be using external database files, data sources stored on the cloud, or storing everything in worksheets.

       Add-in or workbook file In some cases, an add-in may be the best choice for your final product, or perhaps you might use an add-in with a standard workbook.

       Version of Excel Will your Excel application be used with Excel 2019 only, or will your application also need to run on earlier versions of Excel? What about versions of Excel running on other platforms, such as macOS or mobile devices? These considerations are important because each new version of Excel adds features that aren't available in previous versions.

       Error handling Error handling is a major issue with applications. You need to determine how your application will detect and deal with errors. For example, if your application performs pivot table operations on the active sheet, you need to be able to handle a case in which a pivot table does not exist on the sheet that is active.

       Use of special features If your application needs to summarize a lot of data, you may want to consider using Excel's pivot table feature, or you may want to use Excel's data validation feature as a check for valid data entry.

       Performance issues The time to start thinking about increasing the speed and efficiency of your application is in the development stage, not when the application is complete and users are complaining.

       Level of security As you may know, Excel provides several protection options to restrict access to particular elements of a workbook. For example, you can lock cells so that formulas cannot be changed, and you can assign a password to prevent unauthorized users from viewing or accessing specific files. Determining up front exactly what you need to protect—and what level of protection is necessary—will make your job easier.

      NOTE

      Be aware that Excel's protection features aren't 100 percent effective—far from it. If you desire complete and absolute security for your application, Excel probably isn't the best platform.

      You'll probably have to deal with many other project-specific considerations in this phase. Consider all options, and don't settle on the first solution that comes to mind.

      Another design consideration is remembering to plan for change. You'll do yourself a favor if you make your application as generic as possible. For example, don't write a procedure that works with only a specific range of cells. Rather, write a procedure that accepts any range as an argument. When the inevitable changes are requested, such a design makes it easier for you to carry out the revisions. Also, you may find that the work that you do for one project is similar to the work that you do for another. Keep reusability in mind when you are planning a project.

      Avoid letting the end user completely guide your approach to a problem. For example, suppose that you meet with a manager who tells you that the department needs an application to write text files that will be imported into another application. Don't confuse the user's need with the solution. The user's real need is to share data. Using an intermediate text file to do it is just one possible solution; better ways to approach the problem may exist. In other words, don't let the users define their problem by stating it in terms of a solution approach. Determining the best approach is your job.

Determining the Most Appropriate User Interface

      When you develop spreadsheets that others will use, you need to pay special attention to the user interface. By user interface, we mean the method by which the user interacts with the application and executes your VBA macros.

Since the introduction of Excel 2007, some of these user interface decisions are irrelevant. Custom menus and toolbars are, for all intents and purposes, obsolete. Consequently, developers must learn how to work with the Ribbon.

      Excel provides several features that are relevant to user interface design:

       Ribbon customization

       Shortcut menu customization

       Shortcut keys

       Custom dialog boxes (UserForms)

       Message boxes and input boxes

       Controls (such as a ListBox or a CommandButton) placed directly on a worksheet

      We discuss these features briefly in the following sections and cover them more thoroughly in later chapters.

      Customizing the Ribbon

      As a developer, you have a fair amount of control over the Ribbon including which tabs and commands are available when your Excel application opens. Although Excel allows end users to modify the Ribbon, making UI changes via code isn't a simple task.

      

See Chapter 17, “Working with the Ribbon,” for information about working with the Ribbon.

      Customizing shortcut menus

Excel allows the VBA developer to customize the right-click shortcut menus. Right-click menus can offer users a way to trigger an action easily 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.

FIGURE 1.1 A customized shortcut menu

      

Chapter 18, “Working with Shortcut Menus,” describes how to work with shortcut menus using VBA, including some limitations due to the single document interface introduced in Excel 2013.

      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.

      There are two caveats, however. 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