Excel® 2013 Power Programming with VBA
Table of Contents
Part I: Some Essential Background
Part II: Understanding Visual Basic for Applications
Part III: Working with UserForms
Part IV: Advanced Programming Techniques
Part V: Developing Applications
About the Power Utility Pak Offer
Part I: Some Essential Background
Chapter 1: Excel in a Nutshell
Shortcut menus and the Mini toolbar
Formulas, Functions, and Names
Protecting formulas from being overwritten
Protecting a workbook's structure
Applying password protection to a workbook
Protecting VBA code with a password
Chapter 2: Formula Tricks and Techniques
Why use references that aren't relative?
Referencing other sheets or workbooks
Applying names to existing references
Identifying cells of a particular type
Fixing circular reference errors
Using the background error-checking feature
Using Excel's Formula Evaluator
Counting and Summing Techniques
Chapter 3: Understanding Excel Files
Recovering versions of the current workbook
Why is the file format important?
Excel Settings in the Registry
Chapter 4: Essentials of Spreadsheet Application Development
What Is a Spreadsheet Application?
Steps for Application Development
Planning an Application That Meets User Needs
Determining the Most Appropriate User Interface
Using ActiveX controls on a worksheet
Executing the development effort
Concerning Yourself with the End User
Making the application bulletproof
Making the application aesthetically appealing and intuitive
Documenting the development effort
Distributing the application to the user
Updating the application when necessary
The user's installed version of Excel
Part II: Understanding Visual Basic for Applications
Chapter 5: Introducing Visual Basic for Applications
Introducing Visual Basic Editor
Displaying Excel's Developer tab
Exporting and importing objects
Minimizing and maximizing windows
Customizing the VBE Environment
What the macro recorder actually records
Relative or absolute recording?
The Comment Object: A Case Study
Viewing Help for the Comment object
Properties of a Comment object
Objects contained in a Comment object
Determining whether a cell has a comment
Some Useful Application Properties
Essential concepts to remember
Learning more about objects and properties
Chapter 6: VBA Programming Fundamentals
VBA Language Elements: An Overview
Variables, Data Types, and Constants
Declaring multidimensional arrays
Manipulating Objects and Collections
Looping blocks of instructions
Chapter 7: Working with VBA Sub Procedures
Executing a procedure with the Run Sub/UserForm command
Executing a procedure from the Macro dialog box
Executing a procedure with a Ctrl+shortcut key combination
Executing a procedure from the Ribbon
Executing a procedure from a customized shortcut menu
Executing a procedure from another procedure
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
A Realistic Example That Uses Sub Procedures
Chapter 8: Creating Function Procedures
Sub Procedures versus Function Procedures
An Introductory Function Example
Using the function in a worksheet
Using the function in a VBA procedure
A function with an array argument
A function with optional arguments
A function that returns a VBA array
A function that returns an error value
A function with an indefinite number of arguments
Emulating Excel's SUM function
Dealing with the Insert Function Dialog Box
Specifying a function category
Adding a function description manually
Using Add-ins to Store Custom Functions
Determining the Windows directory
Learning more about API functions
Chapter 9: VBA Programming Examples and Techniques
Copying a variably sized range
Selecting or otherwise identifying various types of ranges
Entering a value in the next empty cell
Pausing a macro to get a user-selected range
Determining the type of selected range
Looping through a selected range efficiently
Duplicating rows a variable number of times
Determining whether a range is contained in another range
Determining a cell's data type
A better way to write to a range
Transferring one-dimensional arrays
Transferring a range to a variant array
Working with Workbooks and Sheets
Saving and closing all workbooks
Creating a hyperlink table of contents
Some Useful Functions for Use in Your Code
Retrieving a value from a closed workbook
Some Useful Worksheet Functions
Returning cell formatting information
Displaying the date when a file was saved or printed
Counting cells between two values
Determining the last nonempty cell in a column or row
Does a string match a pattern?
Extracting the nth element from a string
Returning the maximum value across all worksheets
Returning an array of nonduplicated random integers
Determining disk drive information
Determining default printer information
Determining video display information
Reading from and writing to the Registry
Part III: Working with UserForms
Chapter 10: Custom Dialog Box Alternatives
Before You Create That UserForm . . .
The Excel GetOpenFilename Method
The Excel GetSaveAsFilename Method
Displaying Excel's Built-In Dialog Boxes
Making the data form accessible
Displaying a data form by using VBA
Chapter 11: Introducing UserForms
How Excel Handles Custom Dialog Boxes
Adjusting a Control's Properties
Adjusting the display position
Displaying a modeless UserForm
Displaying a UserForm based on a variable
About event-handler procedures
Creating a UserForm: An Example
Writing code to display the dialog box
Adding event-handler procedures
Pairing a SpinButton with a TextBox
Adding new pages to the Toolbox
Customizing or combining controls
Using CommandButtons in a UserForm
Selecting Ranges from a UserForm
Disabling a UserForm's Close Button
Zooming and Scrolling a Sheet from a UserForm
Adding items to a ListBox control
Determining the selected item in a ListBox
Determining multiple selections in a ListBox
Multiple lists in a single ListBox
Working with multicolumn ListBox controls
Using a ListBox to select worksheet rows
Using a ListBox to activate a sheet
Using the MultiPage Control in a UserForm
Chapter 13: Advanced UserForm Techniques
Displaying a Progress Indicator
Creating a stand-alone progress indicator
Showing a progress indicator by using a MultiPage control
Showing a progress indicator without using a MultiPage control
Setting up the MultiPage control for the wizard
Adding the buttons to the wizard's UserForm
Programming the wizard's buttons
Programming dependencies in a wizard
Performing the task with the wizard
MsgBox emulation: MyMsgBox code
How the MyMsgBox function works
A UserForm with Movable Controls
Simulating a Toolbar with a UserForm
Emulating a Task Pane with a UserForm
Handling Multiple UserForm Controls with One Event Handler
Selecting a Color in a UserForm
Displaying a Chart in a UserForm
Changing the Image control's Picture property
Making a UserForm Semitransparent
Installing the Enhanced Data Form add-in
Part IV: Advanced Programming Techniques
Chapter 14: Developing Excel Utilities with VBA
Using VBA to Develop Utilities
Text Tools: The Anatomy of a Utility
How the Text Tools utility works
The UserForm for the Text Tools utility
Making the Text Tools utility efficient
Saving the Text Tools utility settings
Understand the Text Tools utility
Chapter 15: Working with Pivot Tables
An Introductory Pivot Table Example
Examining the recorded code for the pivot table
Cleaning up the recorded pivot table code
Creating a More Complex Pivot Table
The code that created the pivot table
How the more complex pivot table works
Creating Multiple Pivot Tables
Creating a Reverse Pivot Table
Chapter 16: Working with Charts
Getting the Inside Scoop on Charts
Creating a Chart on a Chart Sheet
Using VBA to Deactivate a Chart
Determining Whether a Chart Is Activated
Deleting from the ChartObjects or Charts Collection
Sizing and Aligning ChartObjects
Changing the Data Used in a Chart
Changing chart data based on the active cell
Using VBA to determine the ranges used in a chart
Using VBA to Display Arbitrary Data Labels on a Chart
Displaying a Chart in a UserForm
An example of using Chart events
Enabling events for an embedded chart
Example: Using Chart events with an embedded chart
Discovering VBA Charting Tricks
Printing embedded charts on a full page
Hiding series by hiding columns
Displaying text with the MouseOver event
Creating an Interactive Chart without VBA
Getting the data to create an interactive chart
Creating the Option Button controls for an interactive chart
Creating the city lists for the interactive chart
Creating the interactive chart data range
Creating the interactive chart
Chapter 17: Understanding Excel's Events
What You Should Know about Events
Where to put event-handler procedures
Event-handler procedures that use arguments
Getting Acquainted with Workbook-Level Events
Monitoring a specific range for changes
Monitoring with Application Events
Enabling Application-level events
Determining when a workbook is opened
Monitoring Application-level events
Accessing Events Not Associated with an Object
Chapter 18: Interacting with Other Applications
Starting an Application from Excel
Using the Windows ShellExecute API function
Activating an Application with Excel
Activating a Microsoft Office application
Running Control Panel Dialog Boxes
Working with foreign objects using automation
A simple example of late binding
Controlling Excel from another application
Sending Personalized E-Mail via Outlook
Sending E-Mail Attachments from Excel
Chapter 19: Creating and Using Add-Ins
Comparing an add-in with a standard workbook
Understanding Excel's Add-In Manager
Adding descriptive information for the example add-in
XLAM file VBA collection membership
Visibility of XLSM and XLAM files
Worksheets and chart sheets in XLSM and XLAM files
Accessing VBA procedures in an add-in
Accessing an add-in as a workbook
Optimizing the Performance of Add-ins
Ensuring that an add-in is installed
Referencing other files from an add-in
Detecting the proper Excel version for your add-in
Part V: Developing Applications
Chapter 20: Working with the Ribbon
A simple Ribbon example, take 2
Limitations of old-style toolbars in Excel 2007 and later
Chapter 21: Working with Shortcut Menus
Referring to controls in a CommandBar
Properties of CommandBar controls
Displaying all shortcut menu items
Using VBA to Customize Shortcut Menus
What's different in Excel 2013
Adding a new item to the Cell shortcut menu
Adding a submenu to a shortcut menu
Limiting a shortcut menu to a single workbook
Adding and deleting menus automatically
Disabling or hiding shortcut menu items
Creating a context-sensitive shortcut menu
Chapter 22: Providing Help for Your Applications
Help for Your Excel Applications
Help Systems That Use Excel Components
Using a worksheet to display help text
Displaying Help in a Web Browser
Using the Help method to display HTML Help
Associating a help file with your application
Associating a help topic with a VBA function
Chapter 23: Developing User-Oriented Applications
What Is a User-Oriented Application?
Using the Loan Amortization Wizard
The Loan Amortization Wizard workbook structure
How the Loan Amortization Wizard works
Potential enhancements for the Loan Amortization Wizard
Application Development Concepts
Chapter 24: Compatibility Issues
Types of Compatibility Problems
Creating an International Application
Chapter 25: Manipulating Files with VBA
Performing Common File Operations
Using VBA file-related statements
Using the FileSystemObject object
Displaying Extended File Information
Determining or setting the file position
Statements for reading and writing
Text File Manipulation Examples
Exporting a range to a text file
Importing a text file to a range
Exporting a range to HTML format
Exporting a range to an XML file
Chapter 26: Manipulating Visual Basic Components
Displaying All Components in a VBA Project
Listing All VBA Procedures in a Workbook
Replacing a Module with an Updated Version
Adding Controls to a UserForm at Design Time
Design-time versus runtime UserForm manipulations
Adding 100 CommandButtons at design time
Creating UserForms Programmatically
A simple runtime UserForm example
A useful (but not simple) dynamic UserForm example
Chapter 27: Understanding Class Modules
Example: Creating a NumLock Class
Adding VBA code to the class module
Programming properties of objects
Programming methods for objects
Class module–level variables for the CSVFileClass
Property procedures for the CSVFileClass
Method procedures for the CSVFileClass
Chapter 28: Working with Colors
Understanding document theme colors
Chapter 29: Frequently Asked Questions about Excel Programming
Objects, Properties, Methods, and Events