Excel 2019 Power Programming with VBA. Michael Alexander
Читать онлайн книгу.
do not support VBA. In other words, this book is for the desktop version of Excel 2019 for Windows.
What You Need to Know
This is not a book for beginning Excel users. If you have no experience with Excel, a better choice might be the Excel 2019 Bible (Wiley, 2018), which provides comprehensive coverage of all the features of Excel and is meant for users of all levels.
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 keyboard—a 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 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)
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