Оглавление
Michael Alexander. Excel 2019 Power Programming with VBA
Table of Contents
List of Tables
List of Illustrations
Guide
Pages
Excel® 2019 Power Programming with VBA
Introduction
Topics Covered
What You Need to Know
What You Need to Have
Conventions Used in This Book
Excel commands
Visual Basic Editor commands
Keyboard conventions
Input
VBA code
Functions, filenames, and named ranges
Typographical conventions
Mouse conventions
What the Icons Mean
NOTE
TIP
CAUTION
ON THE WEB
How This Book Is Organized
Part I: Introduction to Excel VBA
Part II: Advanced VBA Techniques
Part III: Working with UserForms
Part IV: Developing Excel Applications
Part V: Appendix
How to Use This Book
What's on the Website
Part I Introduction to Excel VBA. IN THIS PART
CHAPTER 1 Essentials of Spreadsheet Application Development. IN THIS CHAPTER
What Is a Spreadsheet Application?
Steps for Application Development
Determining User Needs
Planning an Application That Meets User Needs
NOTE
Determining the Most Appropriate User Interface
Customizing the Ribbon
Customizing shortcut menus
Creating shortcut keys
Creating custom dialog boxes
Using ActiveX controls on a worksheet
ON THE WEB
Executing the development effort
Concerning Yourself with the End User
Testing the application
What about beta testing?
Making the application bulletproof
Excel passwords are not foolproof
Making the application aesthetically appealing and intuitive
Creating a user Help system
Documenting the development effort
Distributing the application to the user
Updating the application when necessary
Other Development Issues
The user's installed version of Excel
Language issues
System speed
TIP
Video modes
CHAPTER 2 Introducing Visual Basic for Applications. IN THIS CHAPTER
Getting a Head Start with the Macro Recorder
Creating your first macro
NOTE
Examining your macro
NOTE
Testing your macro
NOTE
Editing your macro
Comparing absolute and relative macro recording
Recording macros with absolute references
NOTE
Recording macros with relative references
Other macro recording concepts
Macro security in Excel
Trusted locations
Storing macros in your Personal Macro Workbook
NOTE
Assigning a macro to a button and other form controls
NOTE
Placing a macro on the Quick Access toolbar
Working with the Visual Basic Editor
Understanding VBE components
Menu bar
Toolbar
Project window
Code window
Immediate window
Working with the Project window
Adding a new VBA module
Removing a VBA module
NOTE
Working with a Code window
Minimizing and maximizing windows
Getting VBA code into a module
TIP
Customizing the VBA environment
The Editor tab
The Editor Format tab
The General tab
The Docking tab
VBA Fundamentals
Understanding objects
Understanding collections
Understanding properties
Specifying properties for the active object
Understanding methods
More about arguments
Deep Dive: Working with Range Objects
Finding the properties of the Range object
The Range property
The Cells property
NOTE
NOTE
Getting information from a cell
The Offset property
Essential Concepts to Remember
About the code examples
Don't Panic—You Are Not Alone
Read the rest of the book
Let Excel help write your macro
Use the Help system
Location matters when asking for help
You need to be connected to the Internet
Use the Object Browser
Pilfer code from the Internet
Leverage user forums
Visit expert blogs
Mine YouTube for video training
Learn from the Microsoft Office Dev Center
Dissect the other Excel files in your organization
Ask your local Excel genius
CHAPTER 3 VBA Programming Fundamentals. IN THIS CHAPTER
VBA Language Elements: An Overview
NOTE
Entering VBA code
Comments
TIP
Variables, Data Types, and Constants
Defining data types
NOTE
Declaring variables
Determining a data type
Forcing yourself to declare all variables
TIP
Scoping variables
A note about the examples in this chapter
Local variables
NOTE
CAUTION
Module-wide variables
Public variables
Static variables
Working with constants
Declaring constants
NOTE
Using predefined constants
NOTE
Working with strings
Working with dates
NOTE
About Excel's date bug
CAUTION
Assignment Statements
TIP
CAUTION
Arrays
Declaring arrays
TIP
Declaring multidimensional arrays
Declaring dynamic arrays
Object Variables
TIP
User-Defined Data Types
NOTE
Built-in Functions
TIP
The MsgBox function
Manipulating Objects and Collections
With-End With constructs
NOTE
For Each-Next constructs
NOTE
Controlling Code Execution
GoTo statements
If-Then constructs
VBA's IIf function
Select Case constructs
TIP
Looping blocks of instructions
For-Next loops
What is structured programming?
CAUTION
NOTE
Do While loops
NOTE
Do Until loops
NOTE
CHAPTER 4 Working with VBA Sub Procedures. IN THIS CHAPTER
About Procedures
TIP
Declaring a Sub procedure
NOTE
Naming procedures
Scoping a procedure
Public procedures
Private procedures
NOTE
TIP
Executing Sub Procedures
NOTE
Executing a procedure with the Run Sub/UserForm command
Executing a procedure from the Macro dialog box
TIP
Executing a procedure with a Ctrl+shortcut key combination
CAUTION
TIP
Executing a procedure from the Ribbon
Executing a procedure from a customized shortcut menu
Executing a procedure from another procedure
TIP
NOTE
Calling a procedure in a different module
Calling a procedure in a different workbook
NOTE
NOTE
Why call other procedures?
Executing a procedure by clicking an object
Executing a procedure when an event occurs
Executing a procedure from the Immediate window
Passing Arguments to Procedures
NOTE
NOTE
Using public variables vs. passing arguments to a procedure
Error-Handling Techniques
CAUTION
Trapping errors
NOTE
Error-handling examples
NOTE
A Realistic Example That Uses Sub Procedures
ON THE WEB
The goal
Project requirements
What you know
The approach
TIP
Some preliminary recording
Initial setup
Code writing
TIP
Writing the Sort procedure
More testing
Fixing the problems
TIP
CAUTION
Utility availability
Evaluating the project
NOTE
CHAPTER 5 Creating Function Procedures. IN THIS CHAPTER
Sub Procedures vs. Function Procedures
Why Create Custom Functions?
An Introductory Function Example
CAUTION
Using the function in a worksheet
Using the function in a VBA procedure
Analyzing the custom function
ON THE WEB
What custom worksheet functions can't do
Function Procedures
A function's scope
Executing function procedures
From a procedure
In a worksheet formula
In a conditional formatting formula
NOTE
From the VBE Immediate Window
Function Arguments
NOTE
Function Examples
Functions with no argument
ON THE WEB
NOTE
Controlling function recalculation
A function with one argument
Use arguments, not cell references
A function with two arguments
ON THE WEB
A function with an array argument
ON THE WEB
A function with optional arguments
NOTE
ON THE WEB
A function that returns a VBA array
NOTE
NOTE
NOTE
NOTE
NOTE
A function that returns an error value
NOTE
A function with an indefinite number of arguments
NOTE
Emulating Excel's SUM Function
ON THE WEB
LISTING 5.1 MYSUM Function
Extended Date Functions
CAUTION
ON THE WEB
Debugging Functions
Dealing with the Insert Function Dialog Box
NOTE
Using the MacroOptions method
TIP
NOTE
Specifying a function category
TIP
Adding a function description manually
NOTE
Using Add-Ins to Store Custom Functions
CAUTION
Using the Windows API
64-bit Excel and API functions
Windows API examples
Determining the Windows directory
ON THE WEB
CAUTION
Detecting the Shift key
ON THE WEB
Learning more about API functions
CHAPTER 6 Understanding Excel's Events. IN THIS CHAPTER
What You Should Know About Events
Understanding event sequences
NOTE
Where to put event-handler procedures
Disabling events
NOTE
CAUTION
Entering event-handler code
NOTE
Event-handler procedures that use arguments
TIP
Getting Acquainted with Workbook-Level Events
The Open event
NOTE
The Activate event
The SheetActivate event
The NewSheet event
The BeforeSave event
The Deactivate event
The BeforePrint event
Updating a header or footer
Hiding columns before printing
ON THE WEB
The BeforeClose event
ON THE WEB
Examining Worksheet Events
TIP
TIP
The Change event
Monitoring a specific range for changes
Monitoring a range to make formulas bold
ON THE WEB
CAUTION
Monitoring a range to validate data entry
ON THE WEB
NOTE
NOTE
ON THE WEB
The SelectionChange event
ON THE WEB
The BeforeDoubleClick event
The BeforeRightClick event
Using the Object Browser to locate events
Monitoring with Application Events
NOTE
Enabling Application-level events
Determining when a workbook is opened
ON THE WEB
Monitoring Application-Level events
ON THE WEB
TIP
Accessing events not associated with an object
NOTE
The OnTime event
NOTE
ON THE WEB
CAUTION
The OnKey event
CAUTION
An OnKey event example
ON THE WEB
Key Codes
Disabling shortcut menus
ON THE WEB
CHAPTER 7 VBA Programming Examples and Techniques. IN THIS CHAPTER
Learning by Example
Working with Ranges
Copying a range
Moving a range
Copying a variably sized range
NOTE
Tips for working with ranges
Selecting or otherwise identifying various types of ranges
CAUTION
ON THE WEB
Another way to refer to a range
Resizing a range
Prompting for a cell value
ON THE WEB
Entering a value in the next empty cell
ON THE WEB
Pausing a macro to get a user-selected range
NOTE
ON THE WEB
Counting selected cells
CAUTION
Determining the type of selected range
ON THE WEB
NOTE
Looping through a selected range efficiently
NOTE
NOTE
ON THE WEB
Deleting all empty rows
ON THE WEB
Duplicating rows a variable number of times
ON THE WEB
Determining whether a range is contained in another range
ON THE WEB
Determining a cell's data type
ON THE WEB
Reading and writing ranges
A better way to write to a range
NOTE
ON THE WEB
Transferring one-dimensional arrays
Transferring a range to a variant array
ON THE WEB
Selecting cells by value
ON THE WEB
Copying a noncontiguous range
ON THE WEB
Working with Workbooks and Sheets
Saving all workbooks
Saving and closing all workbooks
Hiding all but the selection
ON THE WEB
Creating a hyperlink table of contents
ON THE WEB
Synchronizing worksheets
ON THE WEB
VBA Techniques
Toggling a Boolean property
Displaying the date and time
NOTE
Displaying friendly time
NOTE
Getting a list of fonts
TIP
ON THE WEB
Sorting an array
ON THE WEB
Processing a series of files
ON THE WEB
Some Useful Functions for Use in Your Code
ON THE WEB
The FileExists function
The FileNameOnly function
The PathExists function
The RangeNameExists function
The SheetExists function
The WorkbookIsOpen function
Testing for membership in a collection
Retrieving a value from a closed workbook
NOTE
ON THE WEB
Some Useful Worksheet Functions
ON THE WEB
Returning cell formatting information
CAUTION
A talking worksheet
Displaying the date when a file was saved or printed
NOTE
Understanding object parents
Counting cells between two values
NOTE
Determining the last nonempty cell in a column or row
Does a string match a pattern?
Extracting the nth element from a string
NOTE
Spelling out a number
ON THE WEB
A multifunctional function
The SHEETOFFSET function
Returning the maximum value across all worksheets
NOTE
Returning an array of nonduplicated random integers
Randomizing a range
Sorting a range
Windows API Calls
Understanding API declarations
32-bit vs. 64-bit declarations
Determining file associations
NOTE
ON THE WEB
Determining default printer information
NOTE
ON THE WEB
Determining video display information
ON THE WEB
Reading from and writing to the Registry
ON THE WEB
Reading from the Registry
Writing to the Registry
An easier way to access the Registry
Part II Advanced VBA Techniques. IN THIS PART
CHAPTER 8 Working with Pivot Tables. IN THIS CHAPTER
An Introductory Pivot Table Example
ON THE WEB
Creating a pivot table
Data appropriate for a pivot table
Examining the recorded code for the pivot table
Cleaning up the recorded pivot table code
NOTE
Pivot table compatibility
Creating a More Complex Pivot Table
ON THE WEB
NOTE
The code that created the pivot table
How the more complex pivot table works
NOTE
NOTE
Creating Multiple Pivot Tables
ON THE WEB
Creating a Reverse Pivot Table
ON THE WEB
CHAPTER 9 Working with Charts. IN THIS CHAPTER
Getting the Inside Scoop on Charts
Chart locations
TIP
NOTE
The macro recorder and charts
Compatibility note
The Chart object model
NOTE
Creating an Embedded Chart
Creating a Chart on a Chart Sheet
Modifying Charts
Using VBA to Activate a Chart
Moving a Chart
NOTE
Understanding Chart Names
Using VBA to Deactivate a Chart
Determining Whether a Chart Is Activated
Deleting from the ChartObjects or Charts Collection
Looping Through All Charts
ON THE WEB
Sizing and Aligning ChartObjects
ON THE WEB
Creating Lots of Charts
Exporting a Chart
Exporting all graphics
ON THE WEB
Changing the Data Used in a Chart
NOTE
Understanding a chart's SERIES formula
Changing chart data based on the active cell
ON THE WEB
Using VBA to determine the ranges used in a chart
ON THE WEB
Using VBA to Display Custom Data Labels on a Chart
ON THE WEB
Displaying a Chart in a UserForm
ON THE WEB
Understanding Chart Events
An example of using Chart events
NOTE
ON THE WEB
NOTE
Enabling events for an embedded chart
Create a class module
Declare a public Chart object
Connect the declared object with your chart
Write event-handler procedures for the chart class
ON THE WEB
Example: Using Chart events with an embedded chart
ON THE WEB
Discovering VBA Charting Tricks
Printing embedded charts on a full page
Creating unlinked charts
ON THE WEB
Displaying text with the MouseOver event
TIP
ON THE WEB
Scrolling a chart
ON THE WEB
Working with Sparkline Charts
ON THE WEB
CHAPTER 10 Interacting with Other Applications. IN THIS CHAPTER
Understanding Microsoft Office Automation
Understanding the concept of binding
NOTE
Early binding
Late binding
GetObject vs. CreateObject
A simple automation example
NOTE
Automating Access from Excel
Running an Access query from Excel
ON THE WEB
NOTE
Running an Access macro from Excel
ON THE WEB
NOTE
Automating Word from Excel
Sending Excel data to a Word document
ON THE WEB
NOTE
Simulating Mail Merge with a Word document
ON THE WEB
NOTE
Automating PowerPoint from Excel
Sending Excel data to a PowerPoint presentation
ON THE WEB
NOTE
Sending all Excel charts to a PowerPoint presentation
ON THE WEB
NOTE
Convert a workbook into a PowerPoint Presentation
ON THE WEB
NOTE
Automating Outlook from Excel
Mailing the active workbook as an attachment
ON THE WEB
NOTE
Mailing a specific range as an attachment
ON THE WEB
NOTE
Mailing a Single Sheet as an Attachment
ON THE WEB
NOTE
Mailing All E-mail Addresses in Your Contact List
ON THE WEB
NOTE
Starting Other Applications from Excel
Using the VBA Shell function
ON THE WEB
TIP
Displaying a folder window
Using the Windows ShellExecute API function
ON THE WEB
Using AppActivate
ON THE WEB
Running Control Panel Dialog Boxes
ON THE WEB
CHAPTER 11 Working with External Data and Files. IN THIS CHAPTER
Working with External Data Connections
Power Query Basics
Understanding query steps
NOTE
Refreshing Power Query data
Managing existing queries
Using VBA to create dynamic connections
ON THE WEB
Iterating through all connections in a workbook
Using ADO and VBA to Pull External Data
NOTE
The connection string
Declaring a Recordset
Referencing the ADO object library
NOTE
NOTE
Putting it all together in code
Using ADO with the active workbook
Querying data from an Excel workbook
ON THE WEB
Appending records to an existing Excel table
Working with Text Files
NOTE
Opening a text file
Reading a text file
Writing a text file
Getting a file number
Determining or setting the file position
Excel's text file import and export features
Statements for reading and writing
Text File Manipulation Examples
Importing data in a text file
Exporting a range to a text file
ON THE WEB
Importing a text file to a range
NOTE
Logging Excel usage
ON THE WEB
Filtering a text file
ON THE WEB
Performing Common File Operations
CAUTION
Using VBA file-related statements
A VBA function to determine whether a file exists
A VBA function to determine whether a path exists
ON THE WEB
A VBA procedure to display a list of files in a directory
NOTE
ON THE WEB
CAUTION
A recursive VBA procedure to display a list of files in nested directories
ON THE WEB
Using the FileSystemObject object
CAUTION
Using FileSystemObject to determine whether a file exists
Using FileSystemObject to determine whether a path exists
Using FileSystemObject to list information about all available disk drives
ON THE WEB
Zipping and Unzipping Files
ON THE WEB
Zipping files
CAUTION
Unzipping a file
Part III Working with UserForms. IN THIS PART
CHAPTER 12 Leveraging Custom Dialog Boxes. IN THIS CHAPTER
Alternatives to UserForms
Using an Input Box
Using the VBA InputBox function
ON THE WEB
Using the Application.InputBox method
NOTE
NOTE
ON THE WEB
Using the VBA MsgBox Function
TIP
ON THE WEB
Using the Excel GetOpenFilename Method
ON THE WEB
Using the Excel GetSaveAsFilename Method
Prompting for a Folder
Displaying Excel's Built-in Dialog Boxes
NOTE
Executing an old menu item directly
Displaying a Data Form
Making the data form accessible
Adding the Form Command to the Quick Access Toolbar
Displaying a data form by using VBA
ON THE WEB
CHAPTER 13 Introducing UserForms. IN THIS CHAPTER
How Excel Handles Custom Dialog Boxes
Inserting a New UserForm
TIP
Adding Controls to a UserForm
TIP
Toolbox Controls
ON THE WEB
TIP
CheckBox
ComboBox
CommandButton
Frame
Image
CAUTION
Label
ListBox
MultiPage
OptionButton
RefEdit
ScrollBar
SpinButton
TabStrip
TextBox
ToggleButton
Using controls on a worksheet
Adjusting UserForm Controls
TIP
TIP
Adjusting a Control's Properties
Using the Properties window
TIP
TIP
NOTE
TIP
Common properties
TIP
Using a naming convention
Accommodating keyboard users
Changing the tab order of controls
NOTE
Setting hot keys
TIP
Testing a UserForm
Displaying a UserForm
Adjusting the display position
Displaying a modeless UserForm
NOTE
Displaying a UserForm based on a variable
Loading a UserForm
About event-handler procedures
NOTE
Closing a UserForm
NOTE
Creating a UserForm: An Example
Creating the UserForm
Writing code to display the dialog box
Testing the dialog box
NOTE
NOTE
Adding event-handler procedures
The finished dialog box
ON THE WEB
Learning about events
CAUTION
NOTE
ON THE WEB
SpinButton events
ON THE WEB
Mouse-initiated events
Keyboard-initiated events
What about code-initiated events?
Pairing a SpinButton with a TextBox
ON THE WEB
About the Tag property
Referencing UserForm Controls
TIP
Understanding the controls collection
Customizing the Toolbox
Adding new pages to the Toolbox
Customizing or combining controls
TIP
ON THE WEB
Adding other ActiveX controls
CAUTION
Creating UserForm Templates
Emulating Excel's dialog boxes
A UserForm Checklist
CHAPTER 14 Looking at UserForm Examples. IN THIS CHAPTER
Creating a UserForm “Menu”
Using CommandButtons in a UserForm
Using a ListBox in a UserForm
ON THE WEB
Selecting Ranges from a UserForm
NOTE
ON THE WEB
Creating a Splash Screen
ON THE WEB
CAUTION
TIP
Disabling a UserForm's Close Button
ON THE WEB
Preventing Breaking Out of the Macro
Changing a UserForm's Size
NOTE
ON THE WEB
Zooming and Scrolling a Sheet from a UserForm
ON THE WEB
TIP
Exploring ListBox Techniques
NOTE
Adding items to a ListBox control
Adding items to a list box at design time
Ensuring the Proper Range Is Used
Adding items to a ListBox at runtime
CAUTION
ON THE WEB
Adding only unique items to a list box
ON THE WEB
Determining the selected item in a list box
NOTE
NOTE
Determining multiple selections in a list box
ON THE WEB
Multiple lists in a single ListBox
ON THE WEB
List box item transfer
ON THE WEB
Moving items in a list box
ON THE WEB
Working with multicolumn ListBox controls
ON THE WEB
ON THE WEB
NOTE
Using a list box to select worksheet rows
ON THE WEB
Using a list box to activate a sheet
ON THE WEB
Using a text box to filter a list box
ON THE WEB
Using the MultiPage Control in a UserForm
ON THE WEB
NOTE
Using an External Control
CAUTION
ON THE WEB
Animating a Label
ON THE WEB
CHAPTER 15 Implementing Advanced UserForm Techniques. IN THIS CHAPTER
A Modeless Dialog Box
ON THE WEB
ON THE WEB
Modeless UserForms in Excel 2019
Displaying a Progress Indicator
CAUTION
Displaying progress in the status bar
Creating a stand-alone progress indicator
ON THE WEB
Building the stand-alone progress indicator UserForm
Creating the code that increments the progress bar
TIP
Calling the stand-alone progress indicator from your code
Benefits of a stand-alone progress indicator
Showing a progress indicator that's integrated into a UserForm
ON THE WEB
Modifying your UserForm for a progress indicator with a MultiPage control
TIP
Inserting the UpdateProgress procedure for a progress indicator with a MultiPage control
Modifying your procedure for a progress indicator with a MultiPage control
How a progress indicator with a MultiPage control works
Showing a progress indicator without using a MultiPage control
Creating a nongraphical progress indicator
ON THE WEB
Creating the UserForm to display the steps
Modifying the calling procedure to use the progress indicator
Creating Wizards
ON THE WEB
Setting up the MultiPage control for the wizard
TIP
Adding the buttons to the wizard's UserForm
NOTE
Programming the wizard's buttons
Programming dependencies in a wizard
Performing the task with the wizard
Emulating the MsgBox Function
NOTE
NOTE
MsgBox emulation: MyMsgBox code
ON THE WEB
How the MyMsgBox function works
NOTE
Using the MyMsgBox function
A UserForm with Movable Controls
ON THE WEB
A UserForm with No Title Bar
ON THE WEB
NOTE
Simulating a Toolbar with a UserForm
ON THE WEB
Emulating a Task Pane with a UserForm
ON THE WEB
A Resizable UserForm
NOTE
ON THE WEB
NOTE
Handling Multiple UserForm Controls with One Event Handler
ON THE WEB
TIP
Selecting a Color in a UserForm
ON THE WEB
Displaying a Chart in a UserForm
ON THE WEB
Saving a chart as a GIF file
Changing the Image control's Picture property
NOTE
Making a UserForm Semitransparent
ON THE WEB
ON THE WEB
A Puzzle on a UserForm
ON THE WEB
Video Poker on a UserForm
ON THE WEB
Part IV Developing Excel Applications. IN THIS PART
CHAPTER 16 Creating and Using Add-Ins. IN THIS CHAPTER
What Is an Add-In?
Comparing an add-in with a standard workbook
NOTE
Why create add-ins?
NOTE
About COM add-ins
Understanding Excel's Add-in Manager
CAUTION
NOTE
Creating an Add-In
CAUTION
A few words about passwords
An Add-In Example
ON THE WEB
Adding descriptive information for the example add-in
Creating an add-in
About Excel's Add-in Manager
Installing an add-in
Testing the add-in
Distributing an add-in
Modifying an add-in
Creating an add-in: a checklist
Comparing XLAM and XLSM Files
XLAM file VBA collection membership
Visibility of XLSM and XLAM files
Worksheets and chart sheets in XLSM and XLAM files
NOTE
Accessing VBA procedures in an add-in
TIP
NOTE
Sleuthing a protected add-in
Manipulating Add-Ins with VBA
NOTE
Adding an item to the AddIns collection
CAUTION
NOTE
CAUTION
Removing an item from the AddIns collection
AddIn object properties
The Name property of an AddIn object
The Path property of an AddIn object
The FullName property of an AddIn object
The Title property of an AddIn object
The Comments property of an AddIn object
The Installed property of an AddIn object
CAUTION
ON THE WEB
NOTE
Accessing an add-in as a workbook
CAUTION
AddIn object events
CAUTION
Optimizing the Performance of Add-Ins
Special Problems with Add-Ins
Ensuring that an add-in is installed
ON THE WEB
Referencing other files from an add-in
CHAPTER 17 Working with the Ribbon. IN THIS CHAPTER
Ribbon Basics
Customizing the Ribbon
Adding a button to the Ribbon
ON THE WEB
Adding your macro to a custom group on the Ribbon
Adding a button to the Quick Access toolbar
Understanding the limitations of Ribbon customization
Creating a Custom Ribbon
Adding a button to an existing tab
ON THE WEB
See your errors
Using RibbonX code to modify the Ribbon
ON THE WEB
TIP
The RibbonX code
ON THE WEB
NOTE
Callback procedures
The CUSTOM UI part
Using imageMso images
Adding a check box to an existing tab
NOTE
The RibbonX code
The VBA code
ON THE WEB
Ribbon controls demo
ON THE WEB
Creating a new tab
TIP
Creating a Ribbon group
Creating controls
ON THE WEB
A dynamicMenu control example
ON THE WEB
More on Ribbon customization
Using VBA with the Ribbon
Accessing a Ribbon control
Working with the Ribbon
Activating a tab
CAUTION
Creating an Old-Style Toolbar
Limitations of old-style toolbars
Code to create a toolbar. NOTE
ON THE WEB
TIP
CHAPTER 18 Working with Shortcut Menus. IN THIS CHAPTER
CommandBar Overview
CommandBar types
Listing shortcut menus
ON THE WEB
Referring to CommandBars
CAUTION
Referring to Controls in a CommandBar
Finding a control
Properties of CommandBar Controls
Displaying All Shortcut Menu Items
ON THE WEB
Using VBA to Customize Shortcut Menus
Shortcut menu and the single-document interface
Customizing shortcut menus with RibbonX code
Resetting a Shortcut Menu
ON THE WEB
Disabling a shortcut menu
Disabling shortcut menu items
Adding a new item to the Cell shortcut menu
ON THE WEB
Adding a submenu to a shortcut menu
ON THE WEB
Limiting a shortcut menu to a single workbook
Finding FaceID images
Shortcut Menus and Events
Adding and deleting menus automatically
Disabling or hiding shortcut menu items
Creating a context-sensitive shortcut menu
ON THE WEB
CHAPTER 19 Providing Help for Your Applications. IN THIS CHAPTER
Help for Your Excel Applications
About the examples in this chapter
ON THE WEB
Help Systems That Use Excel Components
Using cell comments for help
ON THE WEB
TIP
Using a text box for help
TIP
ON THE WEB
Using a worksheet to display help text
ON THE WEB
Displaying help in a UserForm
Using Label controls to display help text
ON THE WEB
Using Control tips in a UserForm
Using a scrolling label to display help text
ON THE WEB
Using a ComboBox control to select a help topic
ON THE WEB
Displaying Help in a Web Browser
Using HTML files
ON THE WEB
Using an MHTML file
ON THE WEB
CAUTION
Using the HTML Help System
NOTE
ON THE WEB
Using the Help method to display HTML Help
Associating a help file with your application
NOTE
Associating a help topic with a VBA function
Activity
NOTE
ON THE WEB
CHAPTER 20 Leveraging Class Modules. IN THIS CHAPTER
What Is a Class Module?
Built-in class modules
Custom class modules
Classes and objects
Objects, properties, and methods
Creating a NumLock Class
Inserting a class module
Adding VBA code to the class module
Using the CNumLock class
ON THE WEB
Coding Properties, Methods, and Events
Programming properties of objects
NOTE
NOTE
Programming methods for objects
Class module events
Exposing a QueryTable Event
Activity
ON THE WEB
Creating a Class to Hold Classes
ON THE WEB
Creating the CSalesRep and CSalesReps classes
Creating the CInvoice and CInvoices classes
Filling the parent classes with objects
Calculating the commissions
CHAPTER 21 Understanding Compatibility Issues. IN THIS CHAPTER
What Is Compatibility?
Types of Compatibility Problems
NOTE
NOTE
TIP
Microsoft Office Compatibility Pack
Avoid Using New Features
Determining Excel's version number
But Will It Work on a Mac?
ON THE WEB
Dealing with 64-Bit Excel
Creating an International Application
Multilanguage Applications
ON THE WEB
VBA Language Considerations
Using Local Properties
Identifying System Settings
Date and Time Settings
APPENDIX VBA Statements and Functions Reference
VBA Statements
Functions
NOTE
Index. Symbols and Numerics
A
B
C
D
E
F
G
H
I
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z
About the Authors
About the Technical Editors
Credits
Acknowledgments
WILEY END USER LICENSE AGREEMENT
