Cover page
Brief contents
Half Title page
Title page
Copyright page
about the author
Acknowledgments
contents
Introduction
Section I Understanding the BASICs
Chapter 1:Introducing VBA
What Is Vba?
A Brief His Tory 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
Chapter 2: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
Chapter 3:Introducing 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
Chapter 4: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
Section II Diving Deeper into VBA
Chapter 5:Object-oriented Programming — An Overview
What “object-oriented Programming” Means
The Object Model
Properties
Methods
Collections
Try It
Chapter 6:Variables, 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
Chapter 7:Understanding Objects and Collections
Workbooks
Worksheets
Cells and Ranges
Special cells
Try It
Lesson Requirements
Step-by-Step
Chapter 8: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
Section III Beyond the Macro Recorder: Writing Your Own Code
Chapter 9:Repeating Actions 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
Chapter 10: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
Chapter 11:Automating Procedures with Worksheet Events
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
Chapter 12:Automating Procedures with Workbook Events
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
Chapter 13:Using Embedded Controls
Working With Forms Controls And Ac Tivex Controls
The Forms Toolbar
The Control Toolbox
Try It
Lesson Requirements
Step-by-Step
Chapter 14:Programming 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
Chapter 15:Programming PivotTables 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
Chapter 16:User Defined Functions
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
Chapter 17:Debugging 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
Section IV Advanced Programming Techniques
Chapter 18:Creating UserForms
What Is a Userform?
Creating a Userform
Designing a Userform
Adding Controls to a Userform
Showing a Userform
Where Does the Userform’s Code Go?
Closing a Userform
Unloading a User Form
Hiding a User Form
Try It
Lesson Requirements
Step-by-Step
Chapter 19:UserForm Controls and Their Functions
Understanding The Frequently Used Userform Controls
CommandButtons
Labels
TextBoxes
ListBoxes
ComboBoxes
CheckBoxes
OptionButtons
Frames
MultiPages
Try It
Lesson Requirements
Step-by-Step
Chapter 20:Advanced UserForms
The Userform Toolbar
Modal Versus Modeless
Disabling the Userform’s Close Button
Maximizing Your Userform’s Size
Select Ing and Displaying Photographs on a Userform
Unloading a Userform Automatically
Pre-sorting The Listb Ox and Combobox Items
Populating Listb Oxes and Comboboxes With Unique It Ems
Display a Real-time Chart In a Userform
Try It
Lesson Requirements
Step-by-Step
Chapter 21:Class Modules
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
Chapter 22: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
Chapter 23: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
Chapter 24:Data Access with ActiveX Data Objects
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
Chapter 25: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
Section V Interacting with Other Office Applications
Chapter 26:Overview of Office Automation from Excel
Why Automate Another Application?
Understanding Office Automation
Early Binding
Late Binding
Which One is Better?
Try It
Lesson Requirements
Step-by-Step
Chapter 27: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
Chapter 28:Working with Outlook from Excel
Opening Outloo K
Composing An E‐mail In Outloo K 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-S tep
Chapter 29:Working with Access 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
Chapter 30: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?
System Requirements
Us Ing The Dvd
What’s On The Dvd?
Tr Oubleshooting
Customer Car E
Symbols
A
B
C
D
E
F
H
I
G
K
L
M
N
O
P
Q
R
S
U
T
V
W
Y
X
Z
Index
Back cover

lgrsnf/50160582.pdf

2025-02-09