| |
| |
| Introduction | |
| |
| |
| |
| Understanding the BASI Cs | |
| |
| |
| |
| Introduc ing VBA | |
| |
| |
| What Is VBA? | |
| |
| |
| A Brief History of VBA | |
| |
| |
| What VBA Can Do for You | |
| |
| |
| Automating a Recurring Task | |
| |
| |
| Automating a Repetitive Task | |
| |
| |
| Running a Macro Automatically if Another Action Takes Place | |
| |
| |
| Creating Your Own Worksheet Functions | |
| |
| |
| Simplifying the Workbook's Look and Feel for Other Users | |
| |
| |
| Controlling Other Office Applications from Excel | |
| |
| |
| Liabilities of VBA | |
| |
| |
| Try It | |
| |
| |
| |
| Getting Started with Macros | |
| |
| |
| Composing Your First Macro | |
| |
| |
| Accessing the VBA Environment | |
| |
| |
| Using the Macro Recorder | |
| |
| |
| Running a Macro | |
| |
| |
| The Macro Dialog Box | |
| |
| |
| Shortcut Key | |
| |
| |
| Try It | |
| |
| |
| Lesson Requirements | |
| |
| |
| Step-by-Step | |
| |
| |
| |
| Introduc ing the Visual Basic Editor | |
| |
| |
| What Is the VBE? | |
| |
| |
| How To Get Into the VBE | |
| |
| |
| Understanding the VBE | |
| |
| |
| The Project Explorer Window | |
| |
| |
| The Code Window | |
| |
| |
| The Properties Window | |
| |
| |
| The Immediate Window | |
| |
| |
| Understanding Modules | |
| |
| |
| Using the Object Browser | |
| |
| |
| Exiting the VBE | |
| |
| |
| Try It | |
| |
| |
| |
| Working in the VBE | |
| |
| |
| Toolbars in the VBE | |
| |
| |
| Macros and Modules | |
| |
| |
| Locating Your Macros | |
| |
| |
| Understanding the Code | |
| |
| |
| Editing a Macro with Comments and Improvements to the Code | |
| |
| |
| Deleting a Macro | |
| |
| |
| Inserting a Module | |
| |
| |
| Renaming a Module | |
| |
| |
| Deleting a Module | |
| |
| |
| Locking and Protecting the VBE | |
| |
| |
| Try It | |
| |
| |
| Lesson Requirements | |
| |
| |
| Step-by-Step | |
| |
| |
| |
| : Diving Deeper into VBA | |
| |
| |
| |
| Ob ject-oriented Programm ing - A n Ov erview | |
| |
| |
| What "Object-Oriented Programming" Means | |
| |
| |
| The Object Model | |
| |
| |
| Properties | |
| |
| |
| Methods | |
| |
| |
| Collections | |
| |
| |
| Try It | |
| |
| |
| |
| Variabl es, Data Types, and Constants | |
| |
| |
| What Is a Variable? | |
| |
| |
| Assigning Values to Variables | |
| |
| |
| Why You Need Variables | |
| |
| |
| Data Types | |
| |
| |
| Understanding the Different Data Types | |
| |
| |
| Declaring a Variable for Dates and Times | |
| |
| |
| Declaring a Variable with the Proper Data Type | |
| |
| |
| Forcing Variable Declaration | |
| |
| |
| Understanding a Variable's Scope | |
| |
| |
| Local Macro Level Only | |
| |
| |
| Module Level | |
| |
| |
| Application Level | |
| |
| |
| Constants | |
| |
| |
| Choosing the Scope and Lifetime of Your Constants | |
| |
| |
| Try It | |
| |
| |
| Lesson Requirements | |
| |
| |
| Step-by-Step | |
| |
| |
| Understanding | |
| |
| |
| |
| Ob jects and Coll ections | |
| |
| |
| Workbooks | |
| |
| |
| Worksheets | |
| |
| |
| Cells and Ranges | |
| |
| |
| SpecialCells | |
| |
| |
| Try It | |
| |
| |
| Lesson Requirements | |
| |
| |
| Step-by-Step | |
| |
| |
| |
| Making Decisions with VBA | |
| |
| |
| Understanding Logical Operators | |
| |
| |
| AND | |
| |
| |
| OR | |
| |
| |
| NOT | |
| |
| |
| Choosing Between This or That | |
| |
| |
| If…Then | |
| |
| |
| If…Then…Else | |
| |
| |
| If…Then…ElseIf | |
| |
| |
| Select Case | |
| |
| |
| Getting Users to Make Decisions | |
| |
| |
| Message Boxes | |
| |
| |
| Input Boxes | |
| |
| |
| Try It | |
| |
| |
| Lesson Requirements | |
| |
| |
| Step-by-Step | |
| |
| |
| |
| : the macro recorder: writing your Own Code | |
| |
| |
| |
| Repeating Ac tions with Loops | |
| |
| |
| What Is a Loop? | |
| |
| |
| Types of Loops | |
| |
| |
| For…Next | |
| |
| |
| For…Each…Next | |
| |
| |
| Exiting a For… Loop | |
| |
| |
| Looping In Reverse with Step | |
| |
| |
| Do…While | |
| |
| |
| Do…Until | |
| |
| |
| Do…Loop…While | |
| |
| |
| Do…Loop…Until | |
| |
| |
| While…Wend | |
| |
| |
| Nesting Loops | |
| |
| |
| Try It | |
| |
| |
| Lesson Requirements | |
| |
| |
| Step-by-Step | |
| |
| |
| |
| Working with Arrays | |
| |
| |
| What Is an Array? | |
| |
| |
| What Arrays Can Do for You | |
| |
| |
| Declaring Arrays | |
| |
| |
| The Option Base Statement | |
| |
| |
| Boundaries in Arrays | |
| |
| |
| Declaring Arrays with Fixed Elements | |
| |
| |
| Declaring Dynamic Arrays with ReDim and Preserve | |
| |
| |
| Try It | |
| |
| |
| Lesson Requirements | |
| |
| |
| Step-by-Step | |
| |
| |
| |
| Au tomating Procedures with Worksheet Ev ents | |
| |
| |
| What Is an "Event"? | |
| |
| |
| Worksheet Events - an Overview | |
| |
| |
| Where Does the Worksheet Event Code Go? | |
| |
| |
| Enabling and Disabling Events | |
| |
| |
| Examples of Common Worksheet Events | |
| |
| |
| Worksheet_Change Event | |
| |
| |
| Worksheet_SelectionChange Event | |
| |
| |
| Worksheet_BeforeDoubleClick Event | |
| |
| |
| Worksheet_BeforeRightClick Event | |
| |
| |
| Worksheet_FollowHyperlink Event | |
| |
| |
| Worksheet_Activate Event | |
| |
| |
| Worksheet_Deactivate Event | |
| |
| |
| Worksheet_Calculate Event | |
| |
| |
| Worksheet_PivotTableUpdate Event | |
| |
| |
| Try It | |
| |
| |
| Lesson Requirements | |
| |
| |
| Step-by-Step | |
| |
| |
| Au tomating Procedures | |
| |
| |
| |
| with Workbook Ev ents | |
| |
| |
| Workbook Events - An Overview | |
| |
| |
| Where Does the Workbook Event Code Go? | |
| |
| |
| Entering Workbook Event Code | |
| |
| |
| Examples of Common Workbook Events | |
| |
| |
| Workbook_Open Event | |
| |
| |
| Workbook_BeforeClose Event | |
| |
| |
| Workbook_Activate Event | |
| |
| |
| Workbook_Deactivate Event | |
| |
| |
| Workbook_SheetChange Event | |
| |
| |
| Workbook_SheetSelectionChange Event | |
| |
| |
| Workbook_SheetBeforeDoubleClick Event | |
| |
| |
| Workbook_SheetBeforeRightClick Event | |
| |
| |
| Workbook_SheetPivotTableUpdate Event | |
| |
| |
| Workbook_NewSheet Event | |
| |
| |
| Workbook_BeforePrint Event | |
| |
| |
| Workbook_SheetActivate Event | |
| |
| |
| Workbook_SheetDeactivate Event | |
| |
| |
| Workbook_BeforeSave Event | |
| |
| |
| Try It | |
| |
| |
| Lesson Requirements | |
| |
| |
| Step-by-Step | |
| |
| |
| |
| Emb edded Controls | |
| |
| |
| Working with Forms Controls and ActiveX Controls | |
| |
| |
| The Forms Toolbar | |
| |
| |
| The Control Toolbox | |
| |
| |
| Try It | |
| |
| |
| Lesson Requirements | |
| |
| |
| Step-by-Step | |
| |
| |
| |
| Programm ing Charts | |
| |
| |
| Adding a Chart to a Chart Sheet | |
| |
| |
| Adding an Embedded Chart to a Worksheet | |
| |
| |
| Moving a Chart | |
| |
| |
| Looping Through All Embedded Charts | |
| |
| |
| Deleting Charts | |
| |
| |
| Renaming a Chart | |
| |
| |
| Try It | |
| |
| |
| Lesson Requirements | |
| |
| |
| Step-by-Step | |
| |
| |
| |
| Programm ing PivotTabl es and PivotCharts | |
| |
| |
| Creating a PivotTable Report | |
| |
| |
| Hiding the PivotTable Field List | |
| |
| |
| Using the Report Filter Area | |
| |
| |
| Formatting Numbers in the Values Area | |
| |
| |
| Why It's Called a PivotTable | |
| |
| |
| Creating a PivotChart | |
| |
| |
| Understanding PivotCaches | |
| |
| |
| Manipulating PivotFields in VBA | |
| |
| |
| Manipulating PivotItems with VBA | |
| |
| |
| Creating a PivotTables Collection | |
| |
| |
| Try It | |
| |
| |
| Lesson Requirements | |
| |
| |
| Step-by-Step | |
| |
| |
| |
| User Defined Fu nctions | |
| |
| |
| What Is a User Defined Function? | |
| |
| |
| Characteristics of User Defined Functions | |
| |
| |
| Anatomy of a UDF | |
| |
| |
| UDF Examples That Solve Common Tasks | |
| |
| |
| Volatile Functions | |
| |
| |
| The Name of the Active Worksheet and Workbook | |
| |
| |
| UDFs with Conditional Formatting | |
| |
| |
| Calling Your Function from a Macro | |
| |
| |
| Adding a Description to the Insert Function Dialog | |
| |
| |
| Try It | |
| |
| |
| Lesson Requirements | |
| |
| |
| Step-by-Step | |
| |
| |
| |
| Debu gging Your Code | |
| |
| |
| What Is Debugging? | |
| |
| |
| What Causes Errors? | |
| |
| |
| Weapons of Mass Debugging | |
| |
| |
| The Debugging Toolbar | |
| |
| |
| Trapping Errors | |
| |
| |
| Error Handler | |
| |
| |
| Bypassing Errors | |
| |
| |
| Try It | |
| |
| |
| Lesson Requirements | |
| |
| |
| Step-by-Step | |
| |
| |
| |
| Advanced Programm ing Techniques | |
| |
| |
| |
| Creating UserForms | |
| |
| |
| What Is a UserForm? | |
| |
| |
| Creating a UserForm | |
| |
| |
| Designing a UserForm | |
| |
| |
| Showing a UserForm | |
| |
| |
| Where Does the UserForm's Code Go? | |
| |
| |
| Closing a UserForm | |
| |
| |
| Unloading a UserForm | |
| |
| |
| Hiding a UserForm | |
| |
| |
| Try It | |
| |
| |
| Lesson Requirements | |
| |
| |
| Step-by-Step | |
| |
| |
| |
| nd Their Fu nctions | |
| |
| |
| Understanding the Frequently Used UserForm Controls | |
| |
| |
| CommandButtons | |
| |
| |
| Labels | |
| |
| |
| TextBoxes | |
| |
| |
| ListBoxes | |
| |
| |
| ComboBoxes | |
| |
| |
| CheckBoxes | |
| |
| |
| OptionButtons | |
| |
| |
| Frames | |
| |
| |
| MultiPages | |
| |
| |
| Try It | |
| |
| |
| Lesson Requirements | |
| |
| |
| Step-by-Step | |
| |
| |
| |
| Advanced UserForms | |
| |
| |
| The UserForm Toolbar | |
| |
| |
| Modal versus Modeless | |
| |
| |
| Disabling the UserForm's Close Button | |
| |
| |
| Maximizing Your UserForm's Size | |
| |
| |
| Selecting and Displaying Photographs on a UserForm | |
| |
| |
| Unloading a UserForm Automatically | |
| |
| |
| Pre-Sorting the ListBox and ComboBox Items | |
| |
| |
| Populating ListBoxes and ComboBoxes with Unique Items | |
| |
| |
| Display a Real-Time Chart in a UserForm | |
| |
| |
| Try It | |
| |
| |
| Lesson Requirements | |
| |
| |
| Step-by-Step | |
| |
| |
| |
| Class Modul es | |
| |
| |
| What Is a Class? | |
| |
| |
| What Is a Class Module? | |
| |
| |
| Creating Your Own Objects | |
| |
| |
| An Important Benefit of Class Modules | |
| |
| |
| Creating Collections | |
| |
| |
| Class Modules for Embedded Objects | |
| |
| |
| Try It | |
| |
| |
| Lesson Requirements | |
| |
| |
| Step-by-Step | |
| |
| |
| |
| Add-Ins | |
| |
| |
| What Is an Excel Add-In? | |
| |
| |
| Creating an Add-In | |
| |
| |
| Converting a File to an Add-In | |
| |
| |
| Installing an Add-In | |
| |
| |
| Creating a User Interface for Your Add-In | |
| |
| |
| Changing the Add-In's Code | |
| |
| |
| Closing Add-Ins | |
| |
| |
| Removing an Add-In from the Add-Ins List | |
| |
| |
| Try It | |
| |
| |
| Lesson Requirements | |
| |
| |
| Step-by-Step | |
| |
| |
| |
| Managing External Data | |
| |
| |
| Creating QueryTables from Web Queries | |
| |
| |
| Creating a QueryTable for Access | |
| |
| |
| Using Text Files to Store External Data | |
| |
| |
| Try It | |
| |
| |
| Lesson Requirements | |
| |
| |
| Step-by-Step | |
| |
| |
| |
| Data Acc ess with Ac tiveX Data Ob jects | |
| |
| |
| Introducing ADO | |
| |
| |
| The Connection Object | |
| |
| |
| The Recordset Object | |
| |
| |
| The Command Object | |
| |
| |
| An Introduction to Structured Query Language (SQL) | |
| |
| |
| The SELECT Statement | |
| |
| |
| The INSERT Statement | |
| |
| |
| The UPDATE Statement | |
| |
| |
| The DELETE Statement | |
| |
| |
| Try It | |
| |
| |
| |
| Not Gone, Not Forgotten | |
| |
| |
| Using Dialog Sheets | |
| |
| |
| What Does a Dialog Sheet Look Like? | |
| |
| |
| Option to Show Message Only Once | |
| |
| |
| Using XLM Get.Cell Functions | |
| |
| |
| Using the SendKeys Method | |
| |
| |
| Try It | |
| |
| |
| Lesson Requirements | |
| |
| |
| Step-by-Step | |
| |
| |
| Interacting with Other | |
| |
| |
| |
| Office Applications | |
| |
| |
| |
| Ov erview of Office Au tomation from Excel | |
| |
| |
| Why Automate Another Application? | |
| |
| |
| Understanding Office Automation | |
| |
| |
| Early Binding | |
| |
| |
| Late Binding | |
| |
| |
| Which One Is Better? | |
| |
| |
| Try It | |
| |
| |
| Lesson Requirements | |
| |
| |
| Step-by-Step | |
| |
| |
| |
| Working with Word from Excel | |
| |
| |
| Activating a Word Document | |
| |
| |
| Activating the Word Application | |
| |
| |
| Opening and Activating a Word Document | |
| |
| |
| Creating a New Word Document | |
| |
| |
| Copying an Excel Range to a Word Document | |
| |
| |
| Printing a Word Document from Excel | |
| |
| |
| Importing a Word Document to Excel | |
| |
| |
| Try It | |
| |
| |
| Lesson Requirements | |
| |
| |
| Step-by-Step | |
| |
| |
| |
| Working with Ou tlook from Excel | |
| |
| |
| Opening Outlook | |
| |
| |
| Composing an E-mail in Outlook from Excel | |
| |
| |
| Creating a MailItem Object | |
| |
| |
| Transferring an Excel Range to the Body of Your E-mail | |
| |
| |
| Putting It All Together | |
| |
| |
| E-mailing a Single Worksheet | |
| |
| |
| Try It | |
| |
| |
| Lesson Requirements | |
| |
| |
| Step-by-Step | |
| |
| |
| |
| ith Acc ess from Excel | |
| |
| |
| Adding a Record to an Access Table | |
| |
| |
| Exporting an Access Table to an Excel Spreadsheet | |
| |
| |
| Creating a New Table in Access | |
| |
| |
| Try It | |
| |
| |
| Lesson Requirements | |
| |
| |
| Step-by-Step | |
| |
| |
| |
| Working with PowerPoint from Excel | |
| |
| |
| Creating a New PowerPoint Presentation | |
| |
| |
| Copying a Worksheet Range to a PowerPoint Slide | |
| |
| |
| Copying Chart Sheets to PowerPoint Slides | |
| |
| |
| Running a PowerPoint Presentation from Excel | |
| |
| |
| Try It | |
| |
| |
| Lesson Requirements | |
| |
| |
| Step-by-Step | |
| |
| |
| Appendix: What's on the DVD? | |
| |
| |
| Index | |