Mastering VBA for Microsoft Office 2010

✍ Scribed by Mansfield, Richard

• Publisher: Sybex; Wiley Publishing, Inc
• Year: 2010
• Tongue: English
• Leaves: 915
• Edition: 2nd ed
• Category: Library

✦ Synopsis

A comprehensive guide to the language used to customize Microsoft OfficeVisual Basic for Applications (VBA) is the language used for writing macros, automating Office applications, and creating custom applications in Word, Excel, PowerPoint, Outlook, and Access. This complete guide shows both IT professionals and novice developers how to master VBA in order to customize the entire Office suite for specific business needs.

Office 2010 is the leading productivity suite, and the VBA language enables customizations of all the Office programs; this complete guide gives both novice and experienced programmers the knowledge they need to make maximum use of VBA for Office Supported with real-world examples in Word, Excel, PowerPoint, Outlook, and Access, this book offers clear, systematic tutorials with both intermediate and advanced content Covers learning how to work with VBA; recording macros; using loops and functions; using message boxes, input boxes, and dialog boxes; creating effective code; XML-based files; ActiveX; the developer tab; content controls; add-ins; embedded macros; and securityMastering VBA for Office 2010prepares developers to customize all Microsoft Office 2010 applications for the unique needs of their employers.

✦ Table of Contents

MASTERING VBA for Microsoft® Office 2010......Page 1
Acknowledgments......Page 7
About the Author......Page 8
Contents at a Glance......Page 9
Contents......Page 11
What Can I Do with VBA?......Page 29
What’s in This Book?......Page 30
Is This Book Suitable for Me?......Page 33
Conventions Used in This Book......Page 34
Part 1: Recording Macros and Getting Started with VBA......Page 35
What Is VBA and What Can You Do with It?......Page 37
Understanding Macro Basics......Page 39
Recording a Macro......Page 40
Running a Macro......Page 54
Recording a Sample Word Macro......Page 55
Recording a Sample Excel Macro......Page 58
Assigning a Way of Running the Macro......Page 59
Deleting a Macro......Page 60
The Bottom Line......Page 62
Opening the Visual Basic Editor......Page 63
Using the Visual Basic Editor’s Main Windows......Page 66
Setting Properties for a Project......Page 77
Customizing the Visual Basic Editor......Page 79
The Bottom Line......Page 95
Testing a Macro in the Visual Basic Editor......Page 97
Editing the Word Macro......Page 102
Editing the Excel Macro......Page 106
Editing a PowerPoint Macro......Page 111
The Bottom Line......Page 117
Setting Up the Visual Basic Editor for Creating the Procedures......Page 119
Creating a Procedure for Word......Page 120
Creating a Procedure for Excel......Page 126
Creating a Procedure for PowerPoint......Page 130
Creating a Procedure for Access......Page 135
The Bottom Line......Page 136
Part 2: Learning How to Work with VBA......Page 139
Getting Ready......Page 141
Procedures......Page 142
Statements......Page 143
Operators......Page 147
Constants......Page 148
Arguments......Page 149
Collections......Page 151
Events......Page 152
The Bottom Line......Page 154
Working with Variables......Page 157
Working with Constants......Page 174
The Bottom Line......Page 176
What Is an Array?......Page 179
Declaring an Array......Page 180
Storing Values in an Array......Page 182
Multidimensional Arrays......Page 183
Redimensioning an Array......Page 184
Finding Out Whether a Variable Is an Array......Page 185
Sorting an Array......Page 186
Searching through an Array......Page 190
The Bottom Line......Page 199
What Is an Object?......Page 201
Working with Collections......Page 206
Finding the Objects You Need......Page 208
Using Object Variables to Represent Objects......Page 220
The Bottom Line......Page 223
Part 3: Making Decisions and Using Loops and Functions......Page 225
What Is a Function?......Page 227
Using Functions......Page 228
Using Functions to Convert Data from One Type to Another......Page 232
Using Functions to Manipulate Strings......Page 241
Using VBA’s Mathematical Functions......Page 252
Using VBA’s Date and Time Functions......Page 253
Using File-Management Functions......Page 257
The Bottom Line......Page 259
Chapter 10: Creating Your Own Functions......Page 261
Components of a Function......Page 262
Creating a Function......Page 263
Examples of Functions for Any VBA-Enabled Application......Page 267
Creating a Function for Word......Page 271
Creating a Function for Excel......Page 273
Creating a Function for PowerPoint......Page 274
Creating a Function for Access......Page 276
The Bottom Line......Page 278
How Do You Compare Things in VBA?......Page 279
Testing Multiple Conditions by Using Logical Operators......Page 281
Select Case Statements......Page 295
The Bottom Line......Page 298
When Should You Use a Loop?......Page 299
Understanding the Basics of Loops......Page 300
Using For… Loops for Fixed Repetitions......Page 301
Using Do… Loops for Variable Numbers of Repetitions......Page 310
While… Wend Loops......Page 321
Nesting Loops......Page 322
Avoiding Infinite Loops......Page 324
The Bottom Line......Page 325
Part 4: Using Message Boxes, Input Boxes, and Dialog Boxes......Page 327
Chapter 13: Getting User Input with Message Boxes and Input Boxes......Page 329
Opening a Procedure to Work On......Page 330
Displaying Status Bar Messages in Word and Excel......Page 331
Message Boxes......Page 333
Input Boxes......Page 345
Forms: When Message Boxes and Input Boxes Won’t Suffice......Page 347
The Bottom Line......Page 348
When Should You Use a Custom Dialog Box?......Page 349
Creating a Custom Dialog Box......Page 350
Linking a Dialog Box to a Procedure......Page 386
Retrieving the User’s Choices from a Dialog Box......Page 388
Examples of Connecting Dialog Boxes to Procedures......Page 392
Using an Application’s Built-in Dialog Boxes from VBA......Page 408
The Bottom Line......Page 414
Chapter 15: Creating Complex Dialog Boxes......Page 415
Creating and Working with Complex Dialog Boxes......Page 416
Using Events to Control Forms......Page 434
The Bottom Line......Page 460
Part 5: Creating Effective Code......Page 463
Creating Modular Code......Page 465
Creating and Using Classes......Page 480
The Bottom Line......Page 490
Principles of Debugging......Page 491
The Different Types of Errors......Page 492
VBA’s Debugging Tools......Page 497
Dealing with Infinite Loops......Page 507
Dealing with Runtime Errors......Page 508
Handling User Interrupts in Word, Excel, and Project......Page 515
Documenting Your Code......Page 517
The Bottom Line......Page 518
What Is a Well-Behaved Procedure?......Page 521
Retaining or Restoring the User Environment......Page 522
Keeping the User Informed during the Procedure......Page 523
Making Sure a Procedure Is Running under Suitable Conditions......Page 530
Cleaning Up after a Procedure......Page 531
The Bottom Line......Page 533
Understanding How VBA Implements Security......Page 535
Signing Your Macro Projects with Digital Signatures......Page 538
Choosing a Suitable Level of Security......Page 548
Locking Your Code......Page 555
The Bottom Line......Page 557
Part 6: Programming the Office Applications......Page 559
Examining the Word Object Model......Page 561
Working with the Documents Collection and the Document Object......Page 564
Working with the Selection Object......Page 577
Creating and Using Ranges......Page 587
Manipulating Options......Page 589
The Bottom Line......Page 591
Using Find and Replace via VBA......Page 593
Working with Headers, Footers, and Page Numbers......Page 600
Working with Sections, Page Setup, Windows, and Views......Page 605
Working with Tables......Page 610
The Bottom Line......Page 622
Getting an Overview of the Excel Object Model......Page 625
Managing Workbooks......Page 627
Working with Worksheets......Page 637
Working with the Active Cell or Selection......Page 642
Working with Ranges......Page 644
Setting Options......Page 647
The Bottom Line......Page 649
Working with Charts......Page 651
Working with Windows......Page 657
Working with Find and Replace......Page 660
The Bottom Line......Page 663
Getting an Overview of the PowerPoint Object Model......Page 665
Understanding PowerPoint’s Creatable Objects......Page 666
Working with Presentations......Page 667
Working with Windows and Views......Page 674
Working with Slides......Page 677
Working with Masters......Page 683
The Bottom Line......Page 685
Working with Shapes......Page 687
Working with Headers and Footers......Page 699
Setting Up and Running a Slide Show......Page 701
The Bottom Line......Page 705
Getting an Overview of the Outlook Object Model......Page 707
Working with the Application Object......Page 709
Understanding General Methods for Working with Outlook Objects......Page 715
Working with Messages......Page 718
Working with Calendar Items......Page 720
Working with Tasks and Task Requests......Page 722
Searching for Items......Page 723
The Bottom Line......Page 726
Chapter 27: Working with Events in Outlook......Page 727
Working with Application-Level Events......Page 728
Working with Item-Level Events......Page 734
Understanding Quick Steps......Page 744
The Bottom Line......Page 745
Getting Started with VBA in Access......Page 747
Getting an Overview of the Access Object Model......Page 753
Understanding Creatable Objects in Access......Page 754
Opening and Closing Databases......Page 755
Working with the Screen Object......Page 760
Using the DoCmd Object to Run Access Commands......Page 761
The Bottom Line......Page 767
Understanding How to Proceed......Page 769
Preparing to Manage the Data in a Database......Page 770
Opening a Recordset......Page 771
Accessing a Particular Record in a Recordset......Page 781
Searching for a Record......Page 783
Returning the Fields in a Record......Page 785
Inserting and Deleting Records......Page 786
The Bottom Line......Page 787
Understanding the Tools Used to Communicate Between Applications......Page 789
Using Automation to Transfer Information......Page 790
Using the Shell Function to Run an Application......Page 802
Using Data Objects to Store and Retrieve Information......Page 804
Communicating via DDE......Page 807
Communicating via SendKeys......Page 810
The Bottom Line......Page 815
Chapter 31: Programming the Office 2010 Ribbon......Page 817
Hiding the Editing Group on the Word Ribbon......Page 818
Working with Excel and PowerPoint......Page 822
Adding a New Group......Page 823
Adding Callbacks......Page 826
Adding Attributes......Page 828
Using Menus and Lists......Page 829
Toggling with a Toggle Button Control......Page 834
Modifying the Ribbon in Access......Page 835
Adding a Callback in Access......Page 839
What to Look For If Things Go Wrong......Page 840
Where to Go from Here......Page 843
The Bottom Line......Page 844
Chapter 1: Recording and Running Macros in the Offi ce Applications......Page 845
Chapter 2: Getting Started with the Visual Basic Editor......Page 846
Chapter 3: Editing Recorded Macros......Page 847
Chapter 4: Creating Code from Scratch in the Visual Basic Editor......Page 848
Chapter 5: Understanding the Essentials of VBA Syntax......Page 851
Chapter 6: Working with Variables, Constants, and Enumerations......Page 852
Chapter 7: Using Array Variables......Page 854
Chapter 8: Finding the Objects, Methods, and Properties You Need......Page 855
Chapter 9: Using Built-in Functions......Page 857
Chapter 10: Creating Your Own Functions......Page 858
Chapter 11: Making Decisions in Your Code......Page 860
Chapter 12: Using Loops to Repeat Actions......Page 861
Chapter 13: Getting User Input with Message Boxes and Input Boxes......Page 862
Chapter 14: Creating Simple Custom Dialog Boxes......Page 864
Chapter 15: Creating Complex Dialog Boxes......Page 867
Chapter 16: Building Modular Code and Using Classes......Page 869
Chapter 17: Debugging Your Code and Handling Errors......Page 870
Chapter 18: Building Well-Behaved Code......Page 871
Chapter 19: Securing Your Code with VBA’s Security Features......Page 873
Chapter 20: Understanding the Word Object Model and Key Objects......Page 875
Chapter 21: Working with Widely Used Objects in Word......Page 876
Chapter 23: Working with Widely Used Objects in Excel......Page 878
Chapter 24: Understanding the PowerPoint Object Model and Key Objects......Page 879
Chapter 25: Working with Shapes and Running Slide Shows......Page 880
Chapter 26: Understanding the Outlook Object Model and Key Objects......Page 881
Chapter 27: Working with Events in Outlook......Page 882
Chapter 28: Understanding the Access Object Model and Key Objects......Page 883
Chapter 29: Manipulating the Data in an Access Database via VBA......Page 884
Chapter 30: Accessing One Application from Another Application......Page 885
Chapter 31: Programming the Office 2010 Ribbon......Page 887
Index......Page 889