VBA for the 2007 Microsoft Office System

Paul McFedries0789736675, 9780789736673

VBA for the 2007 MicrosoftR Office System shows you how to take full advantage of the 2007 Microsoft Office suite by automating routine Office tasks. No matter which Office application you’re using, there are some tasks you perform dozens of times, such as typing a section of text, running a series of menu commands, or formatting a document in a particular way.This book shows you how to accomplish the same tasks by incorporating them into a macro that you can run with just a few mouse clicks or keystrokes. This book shows you the basics of VBA programming from the ground up. Even if you’ve never programmed before, VBA for the 2007 MicrosoftR Office System will have you up to speed with VBA in no time.You’ll learn how to write programs that control Word, Excel, PowerPoint, Access databases, and even Outlook email.You get dozens of example macros that not only illustrate the concepts in the book but also provide you with practical, business-oriented tools that you can use right away to improve your productivity.

---

Table of contents :
VBA for the 2007 Microsoft ® Office System……Page 1
Contents……Page 4
Introduction……Page 18
What Is a Macro?……Page 19
What You Should Know Before Reading This Book……Page 20
This Book’s Special Features……Page 21
I: GETTING STARTED WITH VBA……Page 24
1 Creating and Running Recorded Macros……Page 26
Recording a VBA Macro……Page 28
Running a Recorded Macro……Page 31
2 Writing Your Own Macros……Page 38
Displaying the Visual Basic Editor……Page 39
Touring the Visual Basic Editor……Page 40
Creating a Command Macro……Page 42
Creating a User-Defined Function……Page 46
Taking Advantage of IntelliSense……Page 50
Shutting Down the Visual Basic Editor……Page 53
Declaring Variables……Page 56
Avoiding Variable Errors……Page 58
Variable Data Types……Page 59
Using Array Variables……Page 63
Working with Constants……Page 67
Storing User Input in a Variable……Page 68
Understanding Expressions……Page 76
Working with VBA Operators……Page 77
Understanding Operator Precedence……Page 80
Working with Numeric Expressions……Page 83
Working with String Expressions……Page 86
Working with Logical Expressions……Page 89
Working with Date Expressions……Page 91
What Is an Object?……Page 94
Working with Object Properties……Page 96
Working with Object Methods……Page 98
Handling Object Events……Page 100
Working with Object Collections……Page 101
Assigning an Object to a Variable……Page 102
Working with Multiple Properties or Methods……Page 103
Example: The Application Object……Page 104
Example: The Window Object……Page 111
Code That Makes Decisions……Page 114
Using If…Then to Make True/False Decisions……Page 115
Using If…Then…Else to Handle a False Result……Page 116
Making Multiple Decisions……Page 117
Functions That Make Decisions……Page 124
Code That Loops……Page 127
Using Do…Loop Structures……Page 128
Using For…Next Loops……Page 129
Using For Each…Next Loops……Page 132
Using Exit For or Exit Do to Exit a Loop……Page 133
Indenting for Readability……Page 134
II: PUTTING VBA TO WORK……Page 136
Working with Documents……Page 138
Working with Text……Page 147
Working with the Range Object……Page 148
Using the Selection Object……Page 152
Using the Words Object……Page 155
Working with the Sentences Object……Page 156
Programming the Paragraph Object……Page 159
Excel’s Application Object……Page 162
Manipulating Workbook Objects……Page 169
Dealing with Worksheet Objects……Page 173
Working with Range Objects……Page 176
PowerPoint’s Presentation Object……Page 188
Working with PowerPoint Slide Objects……Page 193
Dealing with Shape Objects……Page 197
Operating a Slide Show……Page 209
10 Programming Access Databases……Page 212
Getting Ready: Two Steps Before You Begin……Page 213
Working with Database Records: Opening a Recordset……Page 215
Working with a Recordset……Page 221
Retrieving Data into Excel……Page 231
Getting Started……Page 236
Working with Outlook Folders……Page 237
Handling Incoming and Outgoing Messages……Page 240
Working with Email Messages……Page 243
Sending a Message……Page 248
Working with Attachments……Page 252
Programming Outlook from Other Applications……Page 254
III: GETTING THE MOST OUT OF VBA……Page 258
12 Creating Custom VBA Dialog Boxes……Page 260
Adding a Form to Your Project……Page 261
Changing the Form’s Design-Time Properties……Page 262
Working with Controls……Page 265
Types of Form Controls……Page 271
Using a Form in a Procedure……Page 281
Understanding Ribbon Extensibility……Page 286
Extending the Ribbon: An Example……Page 288
Hiding the Built-In Ribbon……Page 293
Creating Custom Tabs……Page 294
Creating Custom Groups……Page 296
Creating Custom Controls……Page 298
Working with Modules……Page 322
Configuring Macro Security Settings……Page 324
Digitally Signing a VBA Project……Page 327
Saving Application Settings in the Registry……Page 328
Accessing the File System Through VBA……Page 332
Tips for Faster Procedures……Page 342
15 Trapping Program Errors……Page 346
A Basic Error-Trapping Strategy……Page 347
Working with the Err Object……Page 353
Trappable VBA Errors……Page 356
16 Debugging VBA Procedures……Page 360
A Basic Strategy for Debugging……Page 361
Pausing a Procedure……Page 362
Stepping Through a Procedure……Page 365
Monitoring Procedure Values……Page 367
Using the Immediate Window……Page 371
Debugging Tips……Page 373
IV: APPENDIXES……Page 376
A: VBA Statements……Page 378
B: VBA Functions……Page 384
A……Page 394
C……Page 396
D……Page 399
E……Page 402
F……Page 404
G……Page 407
I……Page 408
K……Page 409
M……Page 410
O……Page 413
P……Page 417
R……Page 419
S……Page 422
T……Page 425
V……Page 427
W……Page 428
X-Y-Z……Page 429