Michael Alexander - Excel 2019 Power Programming with VBA

The four chapters in Part IIIcover custom dialog boxes ( UserForms ). Chapter 12presents some built-in alternatives to creating custom UserForms. Chapter 13provides an introduction to UserForms and the various controls that you can use. Chapters 14and 15present many examples of custom dialog boxes, ranging from basic to advanced.

The chapters in Part IVdeal with important elements of creating user-oriented applications. Chapter 16offers a hands-on discussion of creating add-ins. Chapters 17and 18discuss how to modify Excel's Ribbon and shortcut menus. Chapter 19demonstrates several ways to provide online help for your applications. In Chapter 20, we present a primer on developing user-oriented applications. Chapter 21rounds out your exploration of Excel VBA programming with some information regarding compatibility.

Part Vincludes an appendix that offers a reference guide to all the statements and functions exposed to VBA as keywords.

The topics in this book get more advanced as you progress through it, so you can work through the material from front to back and build your skills as you go. You can also use this book as a reference that you can consult when you need help with the following situations:

You're stuck while trying to do something

You need to do something that you've never done before

You have some time on your hands, and you're interested in learning something new about VBA

The index is comprehensive, and each chapter typically focuses on a single broad topic. Don't be discouraged if some of the material is over your head. Most VBA programmers get by just fine by using only a subset of the language.

Nearly everything discussed in this book has examples with it. You can (and should) download the many useful examples included with this book.

The files are located at www.wiley.com/go/excel2019powerprogramming.

Discovering the basic steps involved in spreadsheet application development

Determining end users' needs

Planning applications to meet users' needs

Developing and testing your applications

Documenting your development efforts and writing user documentation

For the purposes of this book, a spreadsheet application is a spreadsheet file (or group of related files) that is designed so that someone other than the developer can perform specific tasks without extensive training. According to this definition, most of the spreadsheet files that you've developed probably don't qualify as spreadsheet applications. You may have dozens or hundreds of spreadsheet files on your hard drive, but it's a safe bet that most of them aren't designed for others to use.

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.

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.

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 users—you 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.