Michael Alexander - Excel 2019 Power Programming with VBA

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.

Take a minute to skim this section and learn about some of the typographic conventions used throughout this book.

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

You need to use the keyboard to enter data. In addition, you can work with menus and dialog boxes directly from the keyboard—a method that you might find easier if your hands are already positioned over the keys.

Inputs that you are supposed to type from the keyboard will appear in boldface—for 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)

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.

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.

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 SUMPRODUCTfunction used in cell C20.”

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.

Throughout the book, we use icons to call your attention to points that are particularly important.

We use Note icons to tell you that something is important—perhaps a concept that could help you master the task at hand or something fundamental for understanding subsequent material.

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.

We use caution icons when the operation that we're describing can cause problems if you're not careful.

These icons indicate that an example file is available on the book’s website. See the section “What’s on the Website” later in this introduction.

The chapters of this book are grouped into five main parts.

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 1sets the stage with a conceptual overview of Excel application development. Chapters 2through 6cover everything you need to know to start coding in VBA. Chapter 7rounds out your introduction to VBA with many useful examples.

Part IIcovers additional techniques that are often considered advanced. Chapters 8and 9discuss how to use VBA to work with pivot tables and charts (including Sparkline graphics). Chapter 10discusses various techniques that you can use to interact with other applications (such as Word and Outlook). Chapter 11concludes Part IIwith a discussion on how to work with files and external data sources, including how to control Power Query from VBA.