To get the most out of this book, you should be a relatively experienced Excel user who knows how to do the following:
Create workbooks, insert sheets, save files, and so on
Navigate through a workbook
Use the Excel Ribbon user interface
Enter formulas
Use Excel's worksheet functions
Name cells and ranges
Use basic Windows features, such as file management techniques and the Clipboard
What You Need to Have
Excel is available in several versions, including a web version and a version for tablets and phones. This book was written exclusively for the desktop version of Microsoft Excel 2019 for Windows. If you plan to develop applications that will be used in earlier versions of Excel, we strongly suggest you use the earliest version of Excel that your target audience will be using. Over the last few years, Microsoft has adopted an agile release cycle for the web version of Excel with Office 365, generating release updates practically on a monthly basis.
It is important to have a full installation of Excel, and if you want to try the more advanced chapters involving communication between Excel and other Office applications, you will need a full installation of Office.
The version of Windows you use is not important. Any computer system that can run Windows will suffice, but you'll be much better off with a fast machine with plenty of memory. Excel is a large program, and using it on a slower system or a system with minimal memory can be extremely frustrating.
Please note that this book is not applicable to Microsoft Excel for Mac.
Conventions Used in This Book
Take a minute to skim this section and learn about some of the typographic conventions used throughout this book.
Excel commands
Excel uses a context-sensitive Ribbon menu system. The words along the top (such as Insert and View) are known as tabs. Click a tab, and the Ribbon of icons displays the commands that are most suited to the task at hand. Each icon has a name that is (usually) displayed next to or below the icon. The icons are arranged in groups, and the group name appears below the icons.
The convention used in this book is to indicate the tab name, followed by the group name, followed by the icon name. So, for example, the command used to toggle word wrap in a cell is indicated as follows:
Home Alignment Wrap Text
Clicking the first tab, labeled File, takes you to the Backstage window. The Backstage window has commands along the left side of the window. To indicate Backstage commands, we use the word File, followed by the command. For example, the following command displays the Excel Options dialog box:
File Options
Visual Basic Editor commands
Visual Basic Editor is the window in which you will work with your VBA code. VB Editor uses the traditional menu-and-toolbar interface. A command like the following means to click the Tools menu and select the References menu item:
Tools References
Keyboard conventions
You need to use the keyboard to enter data. In addition, you can work with menus and dialog boxes directly from the keyboarda method that you might find easier if your hands are already positioned over the keys.
Input
Inputs that you are supposed to type from the keyboard will appear in boldfacefor example, enter =SUM(B2: B50) in cell B51.
Lengthier inputs will appear on a separate line in a monospace font. For example, we might instruct you to enter the following formula:
=VLOOKUP(StockNumber,PriceList,2)
VBA code
This book contains many snippets of VBA code, as well as complete procedure listings. Each listing appears in a monospace font, and each line of code occupies a separate line. (We copied these listings directly from the VBA module and pasted them into our word processors.) To make the code easier to read, we often use one or more tabs to create indentations. Indentation is optional, but it does help to delineate statements that go together.
If a line of code doesn't fit on a single line in this book, we use the standard VBA line continuation sequence: at the end of a line, a space followed by an underscore character indicates that the line of code extends to the next line. For example, the following two lines are a single code statement:
columnCount = Application.WorksheetFunction. _ CountA(Range("A:A")) + 1
You can enter this code either on two lines, exactly as shown, or on a single line without the space and underscore character.
Functions, filenames, and named ranges
Excel's worksheet functions appear in uppercase font, like so: Enter a SUM formula in cell C20. For VBA procedure names, properties, methods, and objects, we often use mixed uppercase and lowercase letters to make these names easier to read.
Typographical conventions
Anything that you're supposed to type using the keyboard appears in bold. Lengthy input usually appears on a separate line. Here's an example:
="Part Name: " &VLOOKUP(PartNumber,PartList,2)
Names of the keys on your keyboard appear in normal type. When two keys should be pressed simultaneously, they're connected with a plus sign, like this: Press Ctrl+C to copy the selected cells.
The four arrow keys are collectively known as the navigation keys.
Excel built-in worksheet functions appear in monofont in uppercase like this: Note the SUMPRODUCT function used in cell C20.
Mouse conventions
You'll come across some of the following mouse-related terms, which are all standard fare.
Mouse pointer This is the small graphic figure that moves on-screen when you move your mouse. The mouse pointer is usually an arrow, but it changes shape when you move to certain areas of the screen or when you're performing certain actions.
Point Move the mouse so that the mouse pointer is on a specific item; for example, Point to the Save button on the toolbar.
Click Press the left mouse button once and release it immediately.
Right-click Press the right mouse button once and release it immediately. The right mouse button is used in Excel to open shortcut menus that are appropriate for whatever is currently selected.
Double-click Press the left mouse button twice in rapid succession.
Drag Press the left mouse button and keep it pressed while you move the mouse. Dragging is often used to select a range of cells or to change the size of an object.
Double-click Press the left mouse button twice in rapid succession.
Drag Press the left mouse button and keep it pressed while you move the mouse. Dragging is often used to select a range of cells or to change the size of an object.
What the Icons Mean
Throughout the book, we use icons to call your attention to points that are particularly important.
NOTE
We use Note icons to tell you that something is importantperhaps a concept that could help you master the task at hand or something fundamental for understanding subsequent material.
TIP
Tip icons indicate a more efficient way of doing something or a technique that might not be obvious.
These icons are used to refer to other section or chapters that have more to say on a subject.
CAUTION
We use caution icons when the operation that we're describing can cause problems if you're not careful.
ON THE WEB
These icons indicate that an example file is available on the books website. See the section Whats on the Website later in this introduction.
How This Book Is Organized
The chapters of this book are grouped into five main parts.
Part I: Introduction to Excel VBA
In Part I, we introduce you to VBA, providing the programming fundamentals that you will need to create and manage Excel subroutines and functions. Chapter 1 sets the stage with a conceptual overview of Excel application development. Chapters 2 through 6 cover everything you need to know to start coding in VBA. Chapter 7 rounds out your introduction to VBA with many useful examples.
Part II: Advanced VBA Techniques
Part II covers additional techniques that are often considered advanced. Chapters 8 and 9 discuss how to use VBA to work with pivot tables and charts (including Sparkline graphics). Chapter 10 discusses various techniques that you can use to interact with other applications (such as Word and Outlook). Chapter 11 concludes Part II with a discussion on how to work with files and external data sources, including how to control Power Query from VBA.
Part III: Working with UserForms
The four chapters in Part III cover custom dialog boxes (UserForms). Chapter 12 presents some built-in alternatives to creating custom UserForms. Chapter 13 provides an introduction to UserForms and the various controls that you can use. Chapters 14 and 15 present many examples of custom dialog boxes, ranging from basic to advanced.
Part IV: Developing Excel Applications
The chapters in Part IV deal with important elements of creating user-oriented applications. Chapter 16 offers a hands-on discussion of creating add-ins. Chapters 17 and 18 discuss how to modify Excel's Ribbon and shortcut menus. Chapter 19 demonstrates several ways to provide online help for your applications. In Chapter 20, we present a primer on developing user-oriented applications. Chapter 21 rounds out your exploration of Excel VBA programming with some information regarding compatibility.
Part V: Appendix
Part V includes an appendix that offers a reference guide to all the statements and functions exposed to VBA as keywords.
How to Use This Book
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.
What's on the Website
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.
CHAPTER 1 Essentials of Spreadsheet Application Development
IN THIS CHAPTER
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
What Is a Spreadsheet Application?
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.