| |
| |
| Introduction | |
| |
| |
| |
| Getting Started with VBA | |
| |
| |
| |
| Recording Your First Macro | |
| |
| |
| What Is a Macro? | |
| |
| |
| What Does VBA Have to Do with Macros? | |
| |
| |
| Understanding VBA Procedures | |
| |
| |
| Recording a VBA Macro | |
| |
| |
| Viewing the Resulting Module | |
| |
| |
| Editing a Recorded Macro | |
| |
| |
| |
| Writing Your Own Macros | |
| |
| |
| Displaying the Visual Basic Editor | |
| |
| |
| Touring the Visual Basic Editor | |
| |
| |
| Opening an Existing Module | |
| |
| |
| Creating a New Module | |
| |
| |
| Writing Your Own Command Macro | |
| |
| |
| Running a VBA Macro | |
| |
| |
| Creating User-Defined Functions with VBA | |
| |
| |
| Understanding User-Defined Functions | |
| |
| |
| Writing User-Defined Functions | |
| |
| |
| Employing User-Defined Functions | |
| |
| |
| Working with Procedures | |
| |
| |
| The Structure of a Procedure | |
| |
| |
| Calling a Procedure | |
| |
| |
| Taking Advantage of IntelliSense | |
| |
| |
| Working with Comment Blocks | |
| |
| |
| Working with Modules | |
| |
| |
| Renaming a Module | |
| |
| |
| Exporting a Module | |
| |
| |
| Importing a Module | |
| |
| |
| Removing a Module | |
| |
| |
| Shutting Down the Visual Basic Editor | |
| |
| |
| |
| Understanding Program Variables | |
| |
| |
| Declaring Variables | |
| |
| |
| Avoiding Variable Errors | |
| |
| |
| Variable Data Types | |
| |
| |
| Using Array Variables | |
| |
| |
| Working with Constants | |
| |
| |
| Using Built-In Constants | |
| |
| |
| Creating User-Defined Constants | |
| |
| |
| |
| Building VBA Expressions | |
| |
| |
| Working with VBA Operators | |
| |
| |
| Arithmetic Operators | |
| |
| |
| The Concatenation Operator | |
| |
| |
| Comparison Operators | |
| |
| |
| Logical Operators | |
| |
| |
| Understanding Operator Precedence | |
| |
| |
| The Order of Precedence | |
| |
| |
| Controlling the Order of Precedence | |
| |
| |
| Working with Numeric Expressions | |
| |
| |
| VBA's Math Functions | |
| |
| |
| VBA's Financial Functions | |
| |
| |
| Working with String Expressions | |
| |
| |
| Working with Logical Expressions | |
| |
| |
| The And Operator | |
| |
| |
| The Or Operator | |
| |
| |
| The Xor Operator | |
| |
| |
| The Not Operator | |
| |
| |
| Working with Date Expressions | |
| |
| |
| |
| Working with Objects | |
| |
| |
| The Object Hierarchy | |
| |
| |
| Working with Object Properties | |
| |
| |
| Setting the Value of a Property | |
| |
| |
| Returning the Value of a Property | |
| |
| |
| Working with Object Methods | |
| |
| |
| Handling Object Events | |
| |
| |
| Working with Object Collections | |
| |
| |
| Assigning an Object to a Variable | |
| |
| |
| The Is Operator | |
| |
| |
| Working with Multiple Properties or Methods | |
| |
| |
| Example: The Application Object | |
| |
| |
| Properties of the Application Object | |
| |
| |
| Methods of the Application Object | |
| |
| |
| Example: The Window Object | |
| |
| |
| Specifying a Window Object | |
| |
| |
| Opening a New Window | |
| |
| |
| Window Object Properties | |
| |
| |
| Window Object Methods | |
| |
| |
| |
| Controlling Your VBA Code | |
| |
| |
| Code That Makes Decisions | |
| |
| |
| Using If...Then to Make True/False Decisions | |
| |
| |
| Using If...Then...Else to Handle a False Result | |
| |
| |
| Making Multiple Decisions | |
| |
| |
| Using the And and Or Operators | |
| |
| |
| Using Multiple If...Then...Else Statements | |
| |
| |
| Using the Select Case Statement | |
| |
| |
| Functions That Make Decisions | |
| |
| |
| The IIf Function | |
| |
| |
| The Choose Function | |
| |
| |
| The Switch Function | |
| |
| |
| Code That Loops | |
| |
| |
| Using Do...Loop Structures | |
| |
| |
| Using For...Next Loops | |
| |
| |
| Using For Each...Next Loops | |
| |
| |
| Using Exit For or Exit Do to Exit a Loop | |
| |
| |
| |
| Putting VBA to Work | |
| |
| |
| |
| Programming Word | |
| |
| |
| Working with Documents | |
| |
| |
| Specifying a Document Object | |
| |
| |
| Opening a Document | |
| |
| |
| The RecentFiles Object | |
| |
| |
| Creating a New Document | |
| |
| |
| Saving a Document | |
| |
| |
| Closing a Document | |
| |
| |
| Example: Making Document Backups | |
| |
| |
| More Useful Document Object Methods | |
| |
| |
| Working with Text | |
| |
| |
| The Range Object | |
| |
| |
| The Range Method | |
| |
| |
| The Range Property | |
| |
| |
| Reading and Changing Range Text | |
| |
| |
| Formatting Text | |
| |
| |
| Some Useful Range Object Methods | |
| |
| |
| The Selection Object | |
| |
| |
| Checking the Selection Type | |
| |
| |
| Selection Object Methods | |
| |
| |
| The Characters Object | |
| |
| |
| The Words Object | |
| |
| |
| The Sentences Object | |
| |
| |
| The Paragraph Object | |
| |
| |
| Some Useful Paragraph Object Properties | |
| |
| |
| Some Useful Paragraph Object Methods | |
| |
| |
| |
| Programming Excel | |
| |
| |
| Excel's Application Object | |
| |
| |
| Accessing Worksheet Functions | |
| |
| |
| Methods of Excel's Application Object | |
| |
| |
| Some Event-Like Methods | |
| |
| |
| Manipulating Workbook Objects | |
| |
| |
| Specifying a Workbook Object | |
| |
| |
| Opening a Workbook | |
| |
| |
| Creating a New Workbook | |
| |
| |
| Workbook Object Properties | |
| |
| |
| Workbook Object Methods | |
| |
| |
| Dealing with Worksheet Objects | |
| |
| |
| Specifying a Worksheet Object | |
| |
| |
| Creating a New Worksheet | |
| |
| |
| Properties of the Worksheet Object | |
| |
| |
| Methods of the Worksheet Object | |
| |
| |
| Working with Range Objects | |
| |
| |
| Returning a Range Object | |
| |
| |
| Using the Offset Method | |
| |
| |
| Selecting a Cell or Range | |
| |
| |
| Defining a Range Name | |
| |
| |
| More Range Object Properties | |
| |
| |
| More Range Object Methods | |
| |
| |
| |
| Programming PowerPoint | |
| |
| |
| PowerPoint's Application Object | |
| |
| |
| PowerPoint's Presentation Object | |
| |
| |
| Specifying a Presentation Object | |
| |
| |
| Opening a Presentation | |
| |
| |
| Creating a New Presentation | |
| |
| |
| Presentation Object Properties | |
| |
| |
| Presentation Object Methods | |
| |
| |
| The Juggling Application | |
| |
| |
| Working with PowerPoint Slide Objects | |
| |
| |
| Specifying a Slide | |
| |
| |
| Creating a New Slide | |
| |
| |
| Inserting Slides from a File | |
| |
| |
| Slide Object Properties | |
| |
| |
| The Juggling Application: Creating the Slides | |
| |
| |
| Slide Object Methods | |
| |
| |
| Dealing with Shape Objects | |
| |
| |
| Specifying a Shape | |
| |
| |
| Adding Shapes to a Slide | |
| |
| |
| Some Shape Object Properties | |
| |
| |
| The Juggling Application: Creating the Title Page | |
| |
| |
| Some Shape Object Methods | |
| |
| |
| The Juggling Application: Creating the Instructions | |
| |
| |
| Operating a Slide Show | |
| |
| |
| Slide Show Transitions | |
| |
| |
| Slide Show Settings | |
| |
| |
| Running the Slide Show | |
| |
| |
| |
| Programming Access Databases | |
| |
| |
| Getting Ready: Two Steps Before You Begin | |
| |
| |
| |
| Create a Reference | |
| |
| |
| |
| Create a Data Source | |
| |
| |
| Working with Database Records: Opening a Recordset | |
| |
| |
| Opening a Recordset Using a Table | |
| |
| |
| Opening a Recordset Using a SELECT String | |
| |
| |
| Working with a Recordset | |
| |
| |
| Getting at the Recordset Data | |
| |
| |
| Navigating Records | |
| |
| |
| Finding a Record | |
| |
| |
| Editing a Record | |
| |
| |
| Adding a New Record | |
| |
| |
| Deleting a Record | |
| |
| |
| Retrieving Data into Excel | |
| |
| |
| Retrieving an Individual Field Value | |
| |
| |
| Retrieving One or More Entire Rows | |
| |
| |
| Retrieving an Entire Recordset | |
| |
| |
| |
| Programming Outlook Email | |
| |
| |
| Working with Outlook Folders | |
| |
| |
| Referencing Default Folders | |
| |
| |
| Using the Folders Property | |
| |
| |
| Prompting the User for a Folder | |
| |
| |
| Some MAPIFolder Methods | |
| |
| |
| Working with Email Messages | |
| |
| |
| MailItem Object Properties | |
| |
| |
| MailItem Object Methods | |
| |
| |
| Sending a Message | |
| |
| |
| Creating a New Message | |
| |
| |
| Creating a Reply or Forward | |
| |
| |
| Specifying the Message Recipients | |
| |
| |
| Sending the Message | |
| |
| |
| Working with Attachments | |
| |
| |
| Programming Outlook from Other Applications | |
| |
| |
| Setting Up a Reference to Outlook | |
| |
| |
| Getting the NameSpace Object | |
| |
| |
| Logging On to an Outlook Session | |
| |
| |
| Logging Off an Outlook Session | |
| |
| |
| |
| Getting the Most Out of VBA | |
| |
| |
| |
| Interacting with the User | |
| |
| |
| Programming Sounds | |
| |
| |
| Beeping the Speaker | |
| |
| |
| Programming PowerPoint Sound Effects | |
| |
| |
| Displaying Information to the User | |
| |
| |
| Displaying a Message in the Status Bar | |
| |
| |
| Displaying a Message Using MsgBox | |
| |
| |
| Getting Input from the User | |
| |
| |
| Prompting the User for Input | |
| |
| |
| Accessing an Application's Built-In Dialog Boxes | |
| |
| |
| |
| Creating Custom VBA Dialog Boxes | |
| |
| |
| Adding a Form to Your Project | |
| |
| |
| Changing the Form's Design-Time Properties | |
| |
| |
| The Appearance Category | |
| |
| |
| The Behavior Category | |
| |
| |
| The Font Category | |
| |
| |
| The Misc Category | |
| |
| |
| The Picture Category | |
| |
| |
| The Position Category | |
| |
| |
| The Scrolling Category | |
| |
| |
| Working with Controls | |
| |
| |
| Inserting Controls on a Form | |
| |
| |
| Selecting Controls | |
| |
| |
| Sizing Controls | |
| |
| |
| Moving Controls | |
| |
| |
| Copying Controls | |
| |
| |
| Deleting Controls | |
| |
| |
| Grouping Controls | |
| |
| |
| Setting Control Properties | |
| |
| |
| Common Control Properties | |
| |
| |
| Setting the Tab Order | |
| |
| |
| Handling Form Events | |
| |
| |
| Types of Form Controls | |
| |
| |
| Command Buttons | |
| |
| |
| Labels | |
| |
| |
| Text Boxes | |
| |
| |
| Frames | |
| |
| |
| Option Buttons | |
| |
| |
| Check Boxes | |
| |
| |
| Toggle Buttons | |
| |
| |
| List Boxes | |
| |
| |
| Scrollbars | |
| |
| |
| Spin Buttons | |
| |
| |
| Tab Strips and MultiPage Controls | |
| |
| |
| Using a Form in a Procedure | |
| |
| |
| Displaying the Form | |
| |
| |
| Unloading the Form | |
| |
| |
| Processing the Form Results | |
| |
| |
| |
| Creating Custom Menus and Toolbars | |
| |
| |
| Assigning Macros to Menu Commands | |
| |
| |
| First, a Game Plan | |
| |
| |
| Creating a New Menu | |
| |
| |
| Creating a New Submenu | |
| |
| |
| Adding Menu Commands and Assigning Macros | |
| |
| |
| Deleting Menus and Menu Commands | |
| |
| |
| Creating Custom Toolbars for Your Macros | |
| |
| |
| Creating a New Toolbar | |
| |
| |
| Adding a Toolbar Button and Assigning a Macro | |
| |
| |
| Menus, Toolbars, and VBA | |
| |
| |
| Understanding Command Bars | |
| |
| |
| Specifying a Command Bar | |
| |
| |
| Creating a New Command Bar | |
| |
| |
| Command Bar Properties | |
| |
| |
| Deleting a Custom Command Bar | |
| |
| |
| Resetting a Built-In Command Bar | |
| |
| |
| Working with Command Bar Controls | |
| |
| |
| Specifying a Control | |
| |
| |
| Adding a Control to a Command Bar | |
| |
| |
| The Command Bar Info Utility | |
| |
| |
| Control Properties | |
| |
| |
| Control Methods | |
| |
| |
| |
| Debugging VBA Procedures | |
| |
| |
| Trapping Program Errors | |
| |
| |
| A Basic Error-Trapping Strategy | |
| |
| |
| Working with the Err Object | |
| |
| |
| Err Object Properties | |
| |
| |
| A Basic Strategy for Debugging | |
| |
| |
| Syntax Errors | |
| |
| |
| Compile Errors | |
| |
| |
| Runtime Errors | |
| |
| |
| Logic Errors | |
| |
| |
| Pausing a Procedure | |
| |
| |
| Entering Break Mode | |
| |
| |
| Exiting Break Mode | |
| |
| |
| Stepping Through a Procedure | |
| |
| |
| Stepping into a Procedure | |
| |
| |
| Stepping Over a Procedure | |
| |
| |
| Stepping Out of a Procedure | |
| |
| |
| Stepping to the Cursor | |
| |
| |
| Monitoring Procedure Values | |
| |
| |
| Using the Locals Window | |
| |
| |
| Adding a Watch Expression | |
| |
| |
| Editing a Watch Expression | |
| |
| |
| Deleting a Watch Expression | |
| |
| |
| Displaying Data Values Quickly | |
| |
| |
| Using the Immediate Window | |
| |
| |
| Printing Data in the Immediate Window | |
| |
| |
| Executing Statements in the Immediate Window | |
| |
| |
| Debugging Tips | |
| |
| |
| Indent Your Code for Readability | |
| |
| |
| Turn on Syntax Checking | |
| |
| |
| Require Variable Declarations | |
| |
| |
| Break Down Complex Procedures | |
| |
| |
| Enter VBA Keywords in Lowercase | |
| |
| |
| Comment Out Problem Statements | |
| |
| |
| Break Up Long Statements | |
| |
| |
| Use Excel's Range Names Whenever Possible | |
| |
| |
| Take Advantage of User-Defined Constants | |
| |
| |
| |
| Appendixes | |
| |
| |
| |
| VBA Statements | |
| |
| |
| |
| VBA Functions | |