Cover
Introduction
1. Topics Covered
2. What You Need to Know
3. What You Need to Have
4. Conventions Used in This Book
5. What the Icons Mean
6. How This Book Is Organized
7. How to Use This Book
8. What's on the Website
Part I: Introduction to Excel VBA
1. CHAPTER 1: Essentials of Spreadsheet Application Development
  1. What Is a Spreadsheet Application?
  2. Steps for Application Development
  3. Determining User Needs
  4. Planning an Application That Meets User Needs
  5. Determining the Most Appropriate User Interface
  6. Concerning Yourself with the End User
  7. Other Development Issues
2. CHAPTER 2: Introducing Visual Basic for Applications
  1. Getting a Head Start with the Macro Recorder
  2. Working with the Visual Basic Editor
  3. VBA Fundamentals
  4. Deep Dive: Working with Range Objects
  5. Essential Concepts to Remember
  6. Don't Panic—You Are Not Alone
3. CHAPTER 3: VBA Programming Fundamentals
  1. VBA Language Elements: An Overview
  2. Comments
  3. Variables, Data Types, and Constants
  4. Assignment Statements
  5. Arrays
  6. Declaring arrays
  7. Object Variables
  8. User-Defined Data Types
  9. Built-in Functions
  10. Manipulating Objects and Collections
  11. Controlling Code Execution
4. CHAPTER 4: Working with VBA Sub Procedures
  1. About Procedures
  2. Executing Sub Procedures
  3. Passing Arguments to Procedures
  4. Error-Handling Techniques
  5. A Realistic Example That Uses Sub Procedures
  6. Utility availability
  7. Evaluating the project
5. CHAPTER 5: Creating Function Procedures
  1. Sub Procedures vs. Function Procedures
  2. Why Create Custom Functions?
  3. An Introductory Function Example
  4. Function Procedures
  5. Function Arguments
  6. Function Examples
  7. Emulating Excel's SUM Function
  8. Extended Date Functions
  9. Debugging Functions
  10. Dealing with the Insert Function Dialog Box
  11. Using Add-Ins to Store Custom Functions
  12. Using the Windows API
6. CHAPTER 6: Understanding Excel's Events
  1. What You Should Know About Events
  2. Getting Acquainted with Workbook-Level Events
  3. Examining Worksheet Events
  4. Monitoring with Application Events
7. CHAPTER 7: VBA Programming Examples and Techniques
  1. Learning by Example
  2. Working with Ranges
  3. Working with Workbooks and Sheets
  4. VBA Techniques
  5. Some Useful Functions for Use in Your Code
  6. Some Useful Worksheet Functions
  7. Windows API Calls
Part II: Advanced VBA Techniques
1. CHAPTER 8: Working with Pivot Tables
  1. An Introductory Pivot Table Example
  2. Creating a More Complex Pivot Table
  3. Creating Multiple Pivot Tables
  4. Creating a Reverse Pivot Table
2. CHAPTER 9: Working with Charts
  1. Getting the Inside Scoop on Charts
  2. Creating an Embedded Chart
  3. Creating a Chart on a Chart Sheet
  4. Modifying Charts
  5. Using VBA to Activate a Chart
  6. Moving a Chart
  7. Using VBA to Deactivate a Chart
  8. Determining Whether a Chart Is Activated
  9. Deleting from the ChartObjects or Charts Collection
  10. Looping Through All Charts
  11. Sizing and Aligning ChartObjects
  12. Creating Lots of Charts
  13. Exporting a Chart
  14. Changing the Data Used in a Chart
  15. Using VBA to Display Custom Data Labels on a Chart
  16. Displaying a Chart in a UserForm
  17. Understanding Chart Events
  18. Discovering VBA Charting Tricks
  19. Working with Sparkline Charts
3. CHAPTER 10: Interacting with Other Applications
  1. Understanding Microsoft Office Automation
  2. Automating Access from Excel
  3. Automating Word from Excel
  4. Automating PowerPoint from Excel
  5. Automating Outlook from Excel
  6. Starting Other Applications from Excel
4. CHAPTER 11: Working with External Data and Files
  1. Working with External Data Connections
  2. Power Query Basics
  3. Using ADO and VBA to Pull External Data
  4. Working with Text Files
  5. Text File Manipulation Examples
  6. Performing Common File Operations
  7. Zipping and Unzipping Files
Part III: Working with UserForms
1. CHAPTER 12: Leveraging Custom Dialog Boxes
  1. Alternatives to UserForms
  2. Using an Input Box
  3. Using the VBA MsgBox Function
  4. Using the Excel GetOpenFilename Method
  5. Using the Excel GetSaveAsFilename Method
  6. Prompting for a Folder
  7. Displaying Excel's Built-in Dialog Boxes
  8. Displaying a Data Form
2. CHAPTER 13: Introducing UserForms
  1. How Excel Handles Custom Dialog Boxes
  2. Inserting a New UserForm
  3. Adding Controls to a UserForm
  4. Toolbox Controls
  5. Adjusting UserForm Controls
  6. Adjusting a Control's Properties
  7. Displaying a UserForm
  8. Closing a UserForm
  9. Creating a UserForm: An Example
  10. Referencing UserForm Controls
  11. Customizing the Toolbox
  12. Creating UserForm Templates
  13. A UserForm Checklist
3. CHAPTER 14: Looking at UserForm Examples
  1. Creating a UserForm “Menu”
  2. Selecting Ranges from a UserForm
  3. Creating a Splash Screen
  4. Disabling a UserForm's Close Button
  5. Changing a UserForm's Size
  6. Zooming and Scrolling a Sheet from a UserForm
  7. Exploring ListBox Techniques
  8. Using the MultiPage Control in a UserForm
  9. Using an External Control
  10. Animating a Label
4. CHAPTER 15: Implementing Advanced UserForm Techniques
  1. A Modeless Dialog Box
  2. Displaying a Progress Indicator
  3. Creating Wizards
  4. Emulating the MsgBox Function
  5. A UserForm with Movable Controls
  6. A UserForm with No Title Bar
  7. Simulating a Toolbar with a UserForm
  8. Emulating a Task Pane with a UserForm
  9. A Resizable UserForm
  10. Handling Multiple UserForm Controls with One Event Handler
  11. Selecting a Color in a UserForm
  12. Displaying a Chart in a UserForm
  13. Making a UserForm Semitransparent
  14. A Puzzle on a UserForm
  15. Video Poker on a UserForm
Part IV: Developing Excel Applications
1. CHAPTER 16: Creating and Using Add-Ins
  1. What Is an Add-In?
  2. Understanding Excel's Add-in Manager
  3. Creating an Add-In
  4. An Add-In Example
  5. Comparing XLAM and XLSM Files
  6. Manipulating Add-Ins with VBA
  7. Optimizing the Performance of Add-Ins
  8. Special Problems with Add-Ins
2. CHAPTER 17: Working with the Ribbon
  1. Ribbon Basics
  2. Customizing the Ribbon
  3. Creating a Custom Ribbon
  4. Using VBA with the Ribbon
  5. Creating an Old-Style Toolbar
3. CHAPTER 18: Working with Shortcut Menus
  1. CommandBar Overview
  2. Referring to Controls in a CommandBar
  3. Properties of CommandBar Controls
  4. Displaying All Shortcut Menu Items
  5. Using VBA to Customize Shortcut Menus
  6. Resetting a Shortcut Menu
  7. Shortcut Menus and Events
4. CHAPTER 19: Providing Help for Your Applications
  1. Help for Your Excel Applications
  2. Help Systems That Use Excel Components
  3. Displaying Help in a Web Browser
  4. Using the HTML Help System
5. CHAPTER 20: Leveraging Class Modules
  1. What Is a Class Module?
  2. Creating a NumLock Class
  3. Coding Properties, Methods, and Events
  4. Exposing a QueryTable Event
  5. Creating a Class to Hold Classes
6. CHAPTER 21: Understanding Compatibility Issues
  1. What Is Compatibility?
  2. Types of Compatibility Problems
  3. Avoid Using New Features
  4. But Will It Work on a Mac?
  5. Dealing with 64-Bit Excel
  6. Creating an International Application
  7. Multilanguage Applications
  8. VBA Language Considerations
  9. Using Local Properties
  10. Identifying System Settings
  11. Date and Time Settings
Part V: Appendix: VBA Statements and Functions Reference
1. APPENDIX: VBA Statements and Functions Reference
  1. VBA Statements
  2. Functions
Index
End User License Agreement

List of Tables

Chapter 1
1. TABLE 1.1 ActiveX Controls versus Form Controls
Chapter 2
1. TABLE 2.1 Some Useful Properties of the Application Object
Chapter 3
1. TABLE 3.1 VBA Built-in Data Types
2. TABLE 3.2 Variable Scope
3. TABLE 3.3 Operator Precedence
4. TABLE 3.4 VBA Logical Operators
Chapter 5
1. TABLE 5.1 Function Categories
Chapter 6
1. TABLE 6.1 Commonly Used Workbook Events
2. TABLE 6.2 Worksheet Events
3. TABLE 6.3 Commonly Used Events Recognized by the Application Object
4. TABLE 6.4 Key Codes for the OnKey Event
Chapter 9
1. TABLE 9.1 Events Recognized by the Chart Object
Chapter 11
1. TABLE 11.1 VBA File-Related Statements
2. TABLE 11.2 File Attribute Constants for the Dir Function
Chapter 12
1. TABLE 12.1 Codes to Determine the Data Type Returned by Excel's InputBox Method...
2. TABLE 12.2 Constants Used for Buttons in the MsgBox Function
3. TABLE 12.3 Constants Used for MsgBox Return Value
Chapter 13
1. TABLE 13.1 SpinButton Events
Chapter 21
1. TABLE 21.1 Excel Country Codes
2. TABLE 21.2 Properties That Have Local Versions
3. TABLE 21.3 Constants for the International Property
Appendix
1. TABLE A.1 Summary of VBA Statements
2. TABLE A.2 Summary of VBA Functions

List of Illustrations

Chapter 1
1. FIGURE 1.1 A customized shortcut menu
2. FIGURE 1.2 A dialog box created with Excel's UserForm feature
3. FIGURE 1.3 You can add UserForm controls to worksheets and link them to cells...
4. FIGURE 1.4 Using the Ribbon to add controls to a worksheet
5. FIGURE 1.5 Using the Protect Sheet dialog box to specify what users can and c...
Chapter 2
1. FIGURE 2.1 The Record Macro dialog box
2. FIGURE 2.2 Your pretotaled worksheet containing two tables
3. FIGURE 2.3 Your post-totaled worksheet
4. FIGURE 2.4 The Excel Macro dialog box
5. FIGURE 2.5 Recording a macro with relative references
6. FIGURE 2.6 The Trusted Locations tab allows you to add directories that are c...
7. FIGURE 2.7 You can find the form controls on the Developer tab.
8. FIGURE 2.8 Assign a macro to the newly added button.
9. FIGURE 2.9 Adding a macro to the Quick Access toolbar
10. FIGURE 2.10 The VBE with significant elements identified
11. FIGURE 2.11 This Project window lists two projects. They are expanded to show...
12. FIGURE 2.12 Code modules are visible in the Project window in a folder called...
13. FIGURE 2.13 The Editor tab in the Options dialog box
14. FIGURE 2.14 Change the VBE's looks with the Editor Format tab.
15. FIGURE 2.15 The General tab of the Options dialog box
16. FIGURE 2.16 The Docking tab of the Options dialog box
17. FIGURE 2.17 The Object Browser is a great reference source.
Chapter 3
1. FIGURE 3.1 VBA's way of telling you that your procedure contains an undeclare...
2. FIGURE 3.2 VBA displays a list of constants that you can assign to a property...
3. FIGURE 3.3 Displaying a list of VBA functions in VBE
Chapter 4
1. FIGURE 4.1 The Macro dialog box
2. FIGURE 4.2 The Macro Options dialog box lets you assign a Ctrl key shortcut a...
3. FIGURE 4.3 The References dialog box lets you establish a reference to anothe...
4. FIGURE 4.4 Assigning a macro to a button
5. FIGURE 4.5 Executing a procedure by entering its name in the Immediate window...
6. FIGURE 4.6 VBA error messages aren't always user friendly.
7. FIGURE 4.7 You can create a message box to display the error code and descrip...
8. FIGURE 4.8 The SpecialCells method generates this error if no cells are found...
9. FIGURE 4.9 Using the VBE Immediate window to test a statement
10. FIGURE 4.10 An empty procedure in a module located in the Personal Macro Work...
11. FIGURE 4.11 Using a temporary procedure to test the BubbleSort code
12. FIGURE 4.12 This message box tells the user that the sheets cannot be sorted....
13. FIGURE 4.13 This message box appears before the sheets are sorted.
14. FIGURE 4.14 Adding a new command to the Ribbon
Chapter 5
1. FIGURE 5.1 Using a custom function in a worksheet formula
2. FIGURE 5.2 Using a custom function in a VBA procedure
3. FIGURE 5.3 Using a custom VBA function for conditional formatting
4. FIGURE 5.4 Calling a Function procedure from the Immediate window
5. FIGURE 5.5 Using a function to display the result of a calculation
6. FIGURE 5.6 Different ways of passing an array or a single value to a workshee...
7. FIGURE 5.7 Comparing SUM with MYSUM
8. FIGURE 5.8 The Extended Date functions used in formulas
9. FIGURE 5.9 Use the Immediate window to display results while a function is ru...
10. FIGURE 5.10 The Insert Function and Function Arguments dialog boxes for a cus...
11. FIGURE 5.11 Using Windows API functions to determine which keys were pressed ...
Chapter 6
1. FIGURE 6.1 The components for each VBA project are listed in the Project wind...
2. FIGURE 6.2 The best way to create an event procedure is to let the VBE do it ...
3. FIGURE 6.3 This message box was triggered by a SheetActivate event.
4. FIGURE 6.4 Clicking No cancels the print operation by changing the Cancel arg...
5. FIGURE 6.5 When this message appears, Workbook_BeforeClose has already done i...
6. FIGURE 6.6 A message displayed by the Workbook_BeforeClose event procedure
7. FIGURE 6.7 This message box describes the problem when the user makes an inva...
8. FIGURE 6.8 The Worksheet_Change procedure ensures that data validation isn't ...
9. FIGURE 6.9 Moving the cell cursor shades the active cell's row and column.
10. FIGURE 6.10 This workbook uses a class module to monitor all Application-leve...
11. FIGURE 6.11 This message box was programmed to display at a particular time o...
12. FIGURE 6.12 Pressing Shift+F10 displays this message.
Chapter 7
1. FIGURE 7.1 The number of rows in the data range changes every week.
2. FIGURE 7.2 This workbook uses a custom shortcut menu to demonstrate how to se...
3. FIGURE 7.3 The InputBox function gets a value from the user to be inserted in...
4. FIGURE 7.4 Validate a user's entry with the VBA InputBox function.
5. FIGURE 7.5 A macro for inserting data into the next empty row in a worksheet...
6. FIGURE 7.6 Use an input box to pause a macro.
7. FIGURE 7.7 A VBA procedure analyzes the currently selected range.
8. FIGURE 7.8 Using the intersection of the used range and the selected ranged r...
9. FIGURE 7.9 The goal is to duplicate rows based on the value in column B.
10. FIGURE 7.10 New rows were added, according to the value in column B.
11. FIGURE 7.11 Using a function to determine the type of data in a cell
12. FIGURE 7.12 Displaying the time to write to a range and read from a range, us...
13. FIGURE 7.13 Using Excel's InputBox method to prompt for a cell location
14. FIGURE 7.14 All rows and columns are hidden, except for a range (G7:L19).
15. FIGURE 7.15 Hyperlinks to each worksheet, created by a macro
16. FIGURE 7.16 A message box displaying the date and time
17. FIGURE 7.17 Using a function to display time differences in a friendly manner...
18. FIGURE 7.18 Listing font names in the actual fonts
19. FIGURE 7.19 Comparing the time required to perform sorts of various array siz...
20. FIGURE 7.20 Examples of the SPELLDOLLARS function
21. FIGURE 7.21 Determining the path and name of the application associated with ...
Chapter 8
1. FIGURE 8.1 This table is a good candidate for a pivot table.
2. FIGURE 8.2 A pivot table created from the data in Figure 8.1
3. FIGURE 8.3 The data in this workbook will be summarized in a pivot table.
4. FIGURE 8.4 A pivot table created from the budget data
5. FIGURE 8.5 The Pivot Table Fields task pane
6. FIGURE 8.6 Several pivot tables created by a VBA procedure
7. FIGURE 8.7 The summary table on the left will be converted to the table on th...
8. FIGURE 8.8 This dialog box asks the user for the ranges.
Chapter 9
1. FIGURE 9.1 These charts use different formatting.
2. FIGURE 9.2 A simple macro applied consistent formatting to the four charts.
3. FIGURE 9.3 Each row of data will be used to create a chart.
4. FIGURE 9.4 A sampling of the 50 charts created by the macro
5. FIGURE 9.5 This chart always displays the data from the row of the active cel...
6. FIGURE 9.6 Data labels from an arbitrary range show the percent change for ea...
7. FIGURE 9.7 Data labels created from a range of data are not compatible with v...
8. FIGURE 9.8 An XY chart that would benefit by having data labels
9. FIGURE 9.9 This XY chart has data labels, thanks to a VBA procedure.
10. FIGURE 9.10 A chart within a UserForm
11. FIGURE 9.11 Selecting an event in the code module for a Chart object
12. FIGURE 9.12 This chart serves as a clickable image map.
13. FIGURE 9.13 After converting a chart to a picture, you can manipulate it by u...
14. FIGURE 9.14 A text box displays information about the data point under the mo...
15. FIGURE 9.15 Range B7:C9 contains data point information that's displayed in t...
16. FIGURE 9.16 An example of a scrollable chart
17. FIGURE 9.17 Sparkline examples
18. FIGURE 9.18 The result of running the SparklineReport procedure
Chapter 10
1. FIGURE 10.1 Add a reference to the object library for the application that yo...
Chapter 11
1. FIGURE 11.1 Starting a Power Query web query
2. FIGURE 11.2 Enter the target URL containing the data you need.
3. FIGURE 11.3 Select the correct data source and then click the Edit button.
4. FIGURE 11.4 The Power Query Editor window allows you to shape, clean, and tra...
5. FIGURE 11.5 Select the columns that you want to keep and then select Remove Ot...
6. FIGURE 11.6 You can click the Column Actions icon to select actions (such as R...
7. FIGURE 11.7 The Power Query Editor can be used to apply transformation actions...
8. FIGURE 11.8 The Import Data dialog box gives you more control over how the re...
9. FIGURE 11.9 Your final query pulled from the Internet: transformed, put into ...
10. FIGURE 11.10 Query steps can be viewed and managed in the Applied Steps secti...
11. FIGURE 11.11 The Advanced Editor window
12. FIGURE 11.12 Designate a cell that will trap the criteria selection.
13. FIGURE 11.13 Select the latest version of the Microsoft ActiveX Data Objects ...
Chapter 12
1. FIGURE 12.1 The VBA InputBox function at work
2. FIGURE 12.2 Using the VBA InputBox function with a long prompt
3. FIGURE 12.3 Using the InputBox method to specify a range
4. FIGURE 12.4 Excel's InputBox method performs validation automatically
5. FIGURE 12.5 Another example of validating an entry in Excel's InputBox
6. FIGURE 12.6 The button argument of the MsgBox function determines which buttons...
7. FIGURE 12.7 Displaying lengthy text in a message box
8. FIGURE 12.8 This message box displays text with tabs and line breaks.
9. FIGURE 12.9 The GetOpenFilename method displays a dialog box used to specify a ...
10. FIGURE 12.10 This dialog box was displayed with a VBA statement.
11. FIGURE 12.11 Using the Customize Ribbon panel to identify a command name
12. FIGURE 12.12 Some users prefer to use Excel's built-in data form for data-entry...
Chapter 13
1. FIGURE 13.1 The Properties window for an empty UserForm
2. FIGURE 13.2 Use the Toolbox to add controls to a UserForm.
3. FIGURE 13.3 This UserForm displays all of the controls.
4. FIGURE 13.4 Use the Format ➪ Align command to change the alignment of controls....
5. FIGURE 13.5 The OptionButton controls, aligned and evenly spaced
6. FIGURE 13.6 The Properties window for an OptionButton control
7. FIGURE 13.7 Use the Tab Order dialog box to specify the tab order of the contro...
8. FIGURE 13.8 This dialog box asks the user to enter a name and a gender.
9. FIGURE 13.9 The CommandButton1_Click procedure is executed when the button on t...
10. FIGURE 13.10 The CommandButton's Click event procedure displays the...
11. FIGURE 13.11 The event list for a CheckBox control
12. FIGURE 13.12 This SpinButton is paired with a TextBox.
13. FIGURE 13.13 The Toolbox, with a new page of controls
14. FIGURE 13.14 The Additional Controls dialog box lets you add other ActiveX cont...
Chapter 14
1. FIGURE 14.1 This dialog box uses command buttons as a menu.
2. FIGURE 14.2 This dialog box uses a list box as a menu.
3. FIGURE 14.3 The RefEdit control allows the user to select a range.
4. FIGURE 14.4 This splash screen is displayed briefly when the workbook is opened...
5. FIGURE 14.5 A dialog box before and after displaying options
6. FIGURE 14.6 Here, scroll bars allow zooming and scrolling of the worksheet.
7. FIGURE 14.7 Setting the RowSource property at design time
8. FIGURE 14.8 A Collection object is used to fill a list box with the unique item...
9. FIGURE 14.9 This message box displays a list of items selected in a list box.
10. FIGURE 14.10 The contents of this list box depend on the option button selected...
11. FIGURE 14.11 Building a list from another list
12. FIGURE 14.12 The buttons allow the user to move items up or down in the ListBox...
13. FIGURE 14.13 This ListBox displays a three-column list with column headers.
14. FIGURE 14.14 A two-column ListBox filled with data stored in an array
15. FIGURE 14.15 This list box makes selecting rows in a worksheet easy.
16. FIGURE 14.16 This dialog box lets the user activate a sheet.
17. FIGURE 14.17 Use a text box to filter a list box.
18. FIGURE 14.18 MultiPage groups your controls on pages, making them accessible fr...
19. FIGURE 14.19 The Windows Media Player control in a UserForm
20. FIGURE 14.20 The Windows Media Player control
21. FIGURE 14.21 Generating a random number
22. FIGURE 14.22 A random number has been chosen.
Chapter 15
1. FIGURE 15.1 This modeless dialog box remains visible while the user continues...
2. FIGURE 15.2 This modeless UserForm displays various pieces of information abo...
3. FIGURE 15.3 A UserForm displays the progress of a macro.
4. FIGURE 15.4 This UserForm will serve as a progress indicator.
5. FIGURE 15.5 The user specifies the number of rows and columns for the random ...
6. FIGURE 15.6 Page2 of the MultiPage control will display the progress indicato...
7. FIGURE 15.7 The progress indicator will be hidden by reducing the height of t...
8. FIGURE 15.8 The progress indicator in action
9. FIGURE 15.9 The steps are listed in a ListBox control.
10. FIGURE 15.10 Files are added to the list to show progress.
11. FIGURE 15.11 This four-step wizard uses a MultiPage control.
12. FIGURE 15.12 Clicking the Cancel button displays a confirmation message box....
13. FIGURE 15.13 The result of the MsgBox emulation function
14. FIGURE 15.14 The UserForm for the MyMsgBox function
15. FIGURE 15.15 You can drag and rearrange the three Image controls by using the...
16. FIGURE 15.16 This UserForm lacks a title bar.
17. FIGURE 15.17 Another UserForm without a title bar
18. FIGURE 15.18 A UserForm set up to function as a toolbar
19. FIGURE 15.19 The UserForm that simulates a toolbar
20. FIGURE 15.20 A UserForm designed to look like a task pane
21. FIGURE 15.21 This UserForm is resizable.
22. FIGURE 15.22 The UserForm after it was increased
23. FIGURE 15.23 The VBA code converts Label control movements into new Width and...
24. FIGURE 15.24 Multiple command buttons with a single event-handler procedure
25. FIGURE 15.25 The ButtonGroup_Click procedure describes the button that was cl...
26. FIGURE 15.26 This dialog box lets the user select a color by specifying the r...
27. FIGURE 15.27 The user's scroll bar values are stored in the Windows Registry ...
28. FIGURE 15.28 A UserForm can display "live" charts.
29. FIGURE 15.29 A semitransparent UserForm
30. FIGURE 15.30 Creating a light-box effect in Excel
31. FIGURE 15.31 A sliding tile puzzle in a UserForm
32. FIGURE 15.32 A feature-packed video poker game
Chapter 16
1. FIGURE 16.1 Excel warns you if an add-in uses a nonstandard file extension.
2. FIGURE 16.2 These settings affect whether add-ins can be used.
3. FIGURE 16.3 The Add-ins dialog box
4. FIGURE 16.4 The Export Charts workbook will make a useful add-in.
5. FIGURE 16.5 The Add-ins dialog box with the new add-in selected
6. FIGURE 16.6 Making an add-in not an add-in
7. FIGURE 16.7 One way to remove a member of the AddIns collection
8. FIGURE 16.8 A table that lists information about all members of the AddIns co...
9. FIGURE 16.9 When attempting to open the add-in incorrectly, the user sees thi...
Chapter 17
1. FIGURE 17.1 The Page Layout tab contains many different control types.
2. FIGURE 17.2 The Customize Ribbon tab allows you to add macros to the Ribbon....
3. FIGURE 17.3 The Rename dialog lets you choose an icon for your Ribbon button....
4. FIGURE 17.4 The custom Ribbon button executes the HelloWorld macro.
5. FIGURE 17.5 You can add a macro to the Quick Access toolbar.
6. FIGURE 17.6 The new QAT button executes your macro.
7. FIGURE 17.7 Excel can't find the macro associated with the Ribbon button.
8. FIGURE 17.8 XML to create two buttons in a custom group
9. FIGURE 17.9 The editor generates VBA code to use in your workbook.
10. FIGURE 17.10 Modify the callback procedures in the VBE.
11. FIGURE 17.11 Two new buttons added to the Data tab
12. FIGURE 17.12 This check box control is always in sync with the page break dis...
13. FIGURE 17.13 A new Ribbon tab with five groups of controls
14. FIGURE 17.14 A Ribbon group with two labels.
15. FIGURE 17.15 An editBox control in a custom Ribbon group
16. FIGURE 17.16 Three controls in a custom Ribbon group.
17. FIGURE 17.17 This group contains built-in controls.
18. FIGURE 17.18 This Ribbon group contains two galleries.
19. FIGURE 17.19 A gallery that displays month names, plus a button
20. FIGURE 17.20 A gallery of images
21. FIGURE 17.21 The dynamicMenu control lets you create a menu that varies depen...
22. FIGURE 17.22 Using the Customize Ribbon tab of the Excel Options dialog box t...
23. FIGURE 17.23 An old-style toolbar, located in the Custom Toolbars group of th...
Chapter 18
1. FIGURE 18.1 A simple macro generates a list of all shortcut menus.
2. FIGURE 18.2 Displaying the Caption property for controls
3. FIGURE 18.3 Listing the items in all shortcut menus
4. FIGURE 18.4 The Cell shortcut menu with a custom menu item
5. FIGURE 18.5 This shortcut menu has a submenu with three submenu items.
6. FIGURE 18.6 A new shortcut menu appears only when the user right-clicks a cel...
Chapter 19
1. FIGURE 19.1 Using cell comments to display help
2. FIGURE 19.2 Using a shape object with text to display help for the user
3. FIGURE 19.3 An easy method is to put user help in a separate worksheet.
4. FIGURE 19.4 Clicking one of the arrows on the SpinButton changes the text dis...
5. FIGURE 19.5 Inserting a Label control inside a Frame control adds scrolling t...
6. FIGURE 19.6 Using a drop-down list control to select a help topic
7. FIGURE 19.7 Displaying help in a web browser
8. FIGURE 19.8 Displaying an MHTML file in a web browser
9. FIGURE 19.9 An example of HTML Help
10. FIGURE 19.10 Using HTML Help Workshop to create a help file
11. FIGURE 19.11 Specify a context ID for a custom function.
Chapter 20
1. FIGURE 20.1 An empty class module named CNumLock
2. FIGURE 20.2 A message box shows the change in status of the Num Lock key.
3. FIGURE 20.3 A web query for financial information
4. FIGURE 20.4 The code pane lists available events.
5. FIGURE 20.5 After a web query is refreshed, the last update time is recor...
6. FIGURE 20.6 Excel tables hold the information for the objects.
7. FIGURE 20.7 The commission calculation is output to the Immediate Window.
Chapter 21
1. FIGURE 21.1 Compatibility Checker
2. FIGURE 21.2 A summary report from Microsoft Office Code Compatibility Inspect...
3. FIGURE 21.3 The wizard demo in English, Spanish, and German

Introduction

For most of us, the path to Excel VBA programming starts with the need to perform some task that can't be done with the standard tools in Excel. That task is different for each of us. Maybe that task is to create separate workbooks automatically for all the rows in a data set. Maybe that task is to automate the emailing of dozens of reports. Whatever that task is for you, you can bet that someone has started their own journey into Excel VBA with the same need.

The beautiful thing about Excel VBA is that you don't have to be an expert to start solving problems with it. You can learn just enough to solve a particular problem, or you can go further and discover ways to handle all kinds of automation scenarios.

Whatever your goals may be, Excel 2019 Power Programming with VBA will help you harness the power of the VBA language to automate tasks, work smarter, and be more productive.

Topics Covered

This book focuses on Visual Basic for Applications (VBA), the programming language built into Excel (and other applications that make up Microsoft Office). More specifically, it shows you how to write programs that automate various tasks in Excel. This book covers everything from recording simple macros through creating sophisticated user-oriented applications and utilities.

You can approach this book in any way you please. You can read it from cover to cover, or you can skip around, picking up useful tidbits here and there. VBA programming is often a task-oriented endeavor. So, if you're faced with a challenging task, you might try the index first to see where the book might specifically address your problem.

This book does not cover Microsoft Visual Studio Tools for Office (VSTO), a technology that uses Visual Basic .NET and Microsoft Visual C#. VSTO can also be used to control Excel and other Microsoft Office applications.

As you may know, Excel 2019 is available for other platforms. For example, you can use Microsoft's Excel Web App in your browser and even iPads and tablets. These versions 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 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.

What the Icons Mean

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

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.

Excel® 2019 Power Programming with VBA

About the Authors

About the Technical Editors

Credits

Acknowledgments