Excel 2019 Power Programming with VBA - Michael Alexander 7 стр.


A good spreadsheet application does the following:

 Enables the end user to perform a task that he or she probably would not be able to do otherwise.

 Provides the appropriate solution to the problem. (A spreadsheet environment isn't always the optimal approach.)

 Accomplishes what it is supposed to do. This prerequisite may be obvious, but it's not at all uncommon for applications to fail this test.

 Produces accurate results and is free of bugs.

 Uses appropriate and efficient methods and algorithms to accomplish its job.

 Traps errors before the user is forced to deal with them.

 Does not allow the user to delete or modify important components accidentally (or intentionally).

 Has a clear and consistent user interface so that the user always knows how to proceed.

 Has well-documented formulas, macros, and user interface elements that allow for subsequent changes, if necessary.

 Is designed so that it can be modified in simple ways without making major changes. A basic fact is that a user's needs change over time.

 Has an easily accessible help system that provides useful information on at least the major procedures.

 Is designed to be portable and to run on any system that has the proper software (in this case, a copy of a supported version of Excel).

It should come as no surprise that it is possible to create spreadsheet applications for many different usage levels, ranging from a simple fill-in-the-blank template to an extremely complex application that uses a custom interface and may not even look like a spreadsheet.

Steps for Application Development

There is no simple, surefire recipe for developing an effective spreadsheet application. Everyone has his or her own style for creating such applications. In addition, every project is different and therefore requires its own approach. Finally, the demands and technical expertise of the people with whom (or for whom) you work also play a role in how the development process proceeds.

Spreadsheet developers typically perform the following activities:

 Determine the needs of the user(s)

 Plan an application that meets these needs

 Determine the most appropriate user interface

 Create the spreadsheet, formulas, macros, and user interface

 Test and debug the application

 Attempt to make the application bulletproof

 Make the application aesthetically appealing and intuitive

 Document the development effort

 Develop user documentation and Help systems

 Distribute the application to the user

 Update the application when necessary

Not all of these steps are required for each application, and the order in which these activities are performed varies from project to project. We describe each of these activities in the pages that follow. For most of these items, we cover the technical details in subsequent chapters.

Determining User Needs

When you undertake a new Excel project, one of your first steps is to identify exactly what the end users require. Failure to assess the end users' needs thoroughly early on often results in additional work later when you have to adjust the application so that it does what it was supposed to do in the first place.

In some cases, you'll be intimately familiar with the end usersyou may even be an end user yourself. In other cases (for example, if you're a consultant developing a project for a new client), you may know little or nothing about the users or their situations.

How do you determine the needs of the user? If you've been asked to develop a spreadsheet application, it's a good idea to meet with the end users and ask specific questions. Better yet, get everything in writing, create flow diagrams, pay attention to minor details, and do anything else to ensure that the product you deliver is the product that is needed.

Here are some guidelines that may help make this phase easier:

 Don't assume that you know what the user needs. Second-guessing at this stage almost always causes problems later.

 If possible, talk directly to the end users of the application, not just their supervisor or manager.

 Learn what, if anything, is currently being done to meet the users' needs. You might be able to save some work by simply adapting an existing application. At the very least, looking at current solutions will familiarize you with the operation.

 Identify the resources available at the users' site. For example, try to determine whether you must work around any hardware or software limitations.

 If possible, determine the specific hardware systems that will be used. If your application will be used on slow systems, you need to take that into account.

 Identify which versions of Excel are in use. Keep in mind that users can have versions of Excel running on macOS, mobile platforms, and Windows. These have to be taken into account when planning an automated Excel solution. Although Microsoft does everything in its power to urge users to upgrade to the latest version of the software, the majority of Excel users don't.

 Understand the skill levels of the end users. This information will help you design the application appropriately.

 Determine how long the application will be used and whether any changes are anticipated during the lifetime of the project. Knowing this information may influence the amount of effort that you put into the project and help you plan for changes.

Planning an Application That Meets User Needs

After you determine the end users' needs, it's tempting to jump right in and start fiddling around in Excel. Take it from those who suffer from this problem: try to restrain yourself. Builders don't construct a house without a set of blueprints, and you shouldn't build a spreadsheet application without some type of plan. The formality of your plan depends on the scope of the project and your general style of working, but you should spend at least some time thinking about what you're going to do and coming up with a plan of action.

Before rolling up your sleeves and settling down at your keyboard, you'll benefit by taking some time to consider the various ways that you can approach the problem. This planning period is where a thorough knowledge of Excel pays off. Avoiding blind alleys rather than stumbling into them is always a good idea.

If you ask a dozen Excel experts to design an application based on precise specifications, chances are that you'll get a dozen different implementations of the project that meet those specifications. Of those solutions, some will be better than the others because Excel often provides several options to accomplish a task. If you know Excel inside and out, you'll have a good idea of the potential methods 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.

Before rolling up your sleeves and settling down at your keyboard, you'll benefit by taking some time to consider the various ways that you can approach the problem. This planning period is where a thorough knowledge of Excel pays off. Avoiding blind alleys rather than stumbling into them is always a good idea.

If you ask a dozen Excel experts to design an application based on precise specifications, chances are that you'll get a dozen different implementations of the project that meet those specifications. Of those solutions, some will be better than the others because Excel often provides several options to accomplish a task. If you know Excel inside and out, you'll have a good idea of the potential methods 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 protectand what level of protection is necessarywill make your job easier.

NOTE

Be aware that Excel's protection features aren't 100 percent effectivefar 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.

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


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.

Назад Дальше