Introducing Visual Basic for Applications

• Getting a Head Start with the Macro Recorder
• Creating your first macro
• Comparing absolute and relative macro recording
• Other macro recording concepts
• Working with the Visual Basic Editor
• Understanding VBE components
• Working with the Project window
• Working with a Code window
• Customizing the VBA environment
• The Editor Format tab
• The General tab
• The Docking tab
• VBA Fundamentals
• Understanding objects
• Understanding collections
• Understanding properties
• Deep Dive: Working with Range Objects
• Finding the properties of the Range object
• The Range property
• The Cells property
• The Offset property
• Essential Concepts to Remember
• Don t Panic You Are Not Alone
• Read the rest of the book
• Let Excel help write your macro
• Use the Help system
• 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

VBA Programming Fundamentals

• VBA Language Elements: An Overview
• Comments
• Variables, Data Types, and Constants
• Defining data types
• Declaring variables
• Scoping variables
• Working with constants
• Working with strings
• Working with dates
• Assignment Statements
• Arrays
• Declaring arrays
• Declaring multidimensional arrays
• Declaring dynamic arrays
• Object Variables
• User-Defined Data Types
• Built-In Functions
• Manipulating Objects and Collections
• With-End With constructs
• For Each-Next constructs
• Controlling Code Execution
• GoTo statements
• If-Then constructs
• Select Case constructs
• Looping blocks of instructions

Working with VBA Sub Procedures

• About Procedures
• Declaring a Sub procedure
• Scoping a procedure
• Executing 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
• Error-Handling Techniques
• Trapping errors
• Error-handling examples
• A Realistic Example That Uses Sub Procedures
• The goal
• Project requirements
• What you know
• The approach
• Some preliminary recording
• Initial setup
• Code writing
• Writing the Sort procedure
• More testing
• Fixing the problems
• Utility availability
• Evaluating the project

Creating Function Procedures

• Sub Procedures versus Function Procedures
• Why Create Custom Functions
• An Introductory Function Example
• Using the function in a worksheet
• Using the function in a VBA procedure
• Analyzing the custom function
• Function Procedures
• A function s scope
• Executing function procedures
• Function Arguments
• Function Examples
• Functions with no argument
• A function with one argument
• A function with two arguments
• 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
• Extended Date Functions
• Debugging Functions
• Dealing with the Insert Function Dialog Box
• Using the MacroOptions method
• Specifying a function category
• Adding a function description manually
• Using Add-Ins to Store Custom Functions
• Using the Windows API
• Windows API examples
• Determining the Windows directory
• Detecting the Shift key
• Learning more about API functions

Understanding Excel's Events

• What You Should Know about Events
• Understanding event sequences
• Where to put event-handler procedures
• Disabling events
• Entering event-handler code
• Event-handler procedures that use arguments
• Getting Acquainted with Workbook-Level Events
• The Open event
• The Activate event
• The SheetActivate event
• The NewSheet event
• The BeforeSave event
• The Deactivate event
• The BeforePrint event
• The BeforeClose event
• Examining Worksheet Events
• The Change event
• Monitoring a specific range for changes
• The SelectionChange event
• The BeforeDoubleClick event
• The BeforeRightClick event
• 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
• The OnTime event
• The OnKey event

VBA Programming Examples and Techniques

• Learning by Example
• Working with Ranges
• Copying a range
• Moving a range
• Copying a variably sized range
• Selecting or otherwise identifying various types of ranges
• Resizing a range
• Prompting for a cell value
• Entering a value in the next empty cell
• Pausing a macro to get a user-selected range
• Counting selected cells
• Determining the type of selected range
• Looping through a selected range efficiently
• Deleting all empty rows
• Duplicating rows a variable number of times
• Determining whether a range is contained in another range
• Determining a cell s data type
• Reading and writing ranges
• A better way to write to a range
• Transferring one-dimensional arrays
• Transferring a range to a variant array
• Selecting cells by value
• Copying a noncontiguous range
• Working with Workbooks and Sheets
• Saving all workbooks
• Saving and closing all workbooks
• Hiding all but the selection
• Creating a hyperlink table of contents
• Synchronizing worksheets
• VBA Techniques
• Toggling a Boolean property
• Displaying the date and time
• Displaying friendly time
• Getting a list of fonts
• Sorting an array
• Processing a series of files
• Some Useful Functions for Use in Your Code
• The FileExists function
• The FileNameOnly function
• The PathExists function
• The RangeNameExists function
• The SheetExists function
• The WorkbookIsOpen function
• Retrieving a value from a closed workbook
• Some Useful Worksheet Functions
• Returning cell formatting information
• A talking worksheet
• Displaying the date when a file was saved or printed
• Understanding object parents
• 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
• Spelling out a number
• A multifunctional function
• The SHEETOFFSET function
• Returning the maximum value across all worksheets
• Returning an array of nonduplicated random integers
• Randomizing a range
• Sorting a range
• Windows API Calls
• Understanding API Declarations
• Determining file associations
• Determining default printer information
• Determining video display information
• Reading from and writing to the Registry

Working with External Data and Files

• Working with External Data Connections
• Manually creating a connection
• Manually editing data connections
• Using VBA to create dynamic connections
• Iterating through all connections in a workbook
• Using ADO and VBA to Pull External Data
• The connection string
• Declaring a Recordset
• Referencing the ADO object library
• Putting it all together in code
• Using ADO with the active workbook
• Working with Text Files
• Opening a text file
• Reading a text file
• Writing a text file
• Getting a file number
• Determining or setting the file position
• Statements for reading and writing
• Text File Manipulation Examples
• Importing data in a text file
• Exporting a range to a text file
• Importing a text file to a range
• Logging Excel usage
• Filtering a text file
• Performing Common File Operations
• Using VBA file-related statements
• Using the FileSystemObject object
• Zipping and Unzipping Files
• Zipping files
• Unzipping a file

Leveraging Custom Dialog Boxes

• Before You Create That UserForm
• Using an Input Box
• The VBA InputBox function
• The Application.InputBox method
• The VBA MsgBox Function
• The Excel GetOpenFilename Method
• The Excel GetSaveAsFilename Method
• Prompting for a Directory
• Displaying Excel s Built-In Dialog Boxes
• Displaying a Data Form
• Making the data form accessible
• Displaying a data form by using VBA

Introducing UserForms

• How Excel Handles Custom Dialog Boxes
• Inserting a New UserForm
• Adding Controls to a UserForm
• Toolbox Controls
• CheckBox
• ComboBox
• CommandButton
• Frame
• Image
• Label
• ListBox
• MultiPage
• OptionButton
• RefEdit
• ScrollBar
• SpinButton
• TabStrip
• TextBox
• ToggleButton
• Adjusting UserForm Controls
• Adjusting a Control s Properties
• Using the Properties window
• Common properties
• Accommodating keyboard users
• Displaying a UserForm
• Adjusting the display position
• Displaying a modeless UserForm
• Displaying a UserForm based on a variable
• Loading a UserForm
• About event-handler procedures
• Closing a UserForm
• Creating a UserForm: An Example
• Creating the UserForm
• Writing code to display the dialog box
• Testing the dialog box
• Adding event-handler procedures
• The finished dialog box
• Understanding UserForm Events
• Learning about events
• UserForm events
• SpinButton events
• Pairing a SpinButton with a TextBox
• Referencing UserForm Controls
• Customizing the Toolbox
• Adding new pages to the Toolbox
• Customizing or combining controls
• Adding new ActiveX controls
• Creating UserForm Templates
• A UserForm Checklist