Mastering VBA for Office 2010


A comprehensive guide to the language used to customize Microsoft Office Visual 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...

See more details below
$33.39 price
(Save 33%)$49.99 List Price

Pick Up In Store

Reserve and pick up in 60 minutes at your local store

Other sellers (Paperback)
  • All (14) from $27.34   
  • New (11) from $27.34   
  • Used (3) from $32.74   
Sending request ...


A comprehensive guide to the language used to customize Microsoft Office Visual 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 security Mastering VBA for Office 2010 prepares developers to customize all Microsoft Office 2010 applications for the unique needs of their employers.

Read More Show Less

Product Details

  • ISBN-13: 9780470634004
  • Publisher: Wiley
  • Publication date: 8/30/2010
  • Edition number: 2
  • Pages: 912
  • Sales rank: 248,645
  • Product dimensions: 7.40 (w) x 9.20 (h) x 2.10 (d)

Meet the Author

Richard Mansfield is the author or coauthor of more than 40 computer books, includingVisual Basic .NET Power Toolkit, Office 2003 Application Development All-in-One Desk Reference For Dummies, and Programming: A Beginner's Guide. He is the former editor of Compute! magazine. Overall, his books have sold more than half a million copies worldwide and have been translated into 12 languages.

Read More Show Less

Table of Contents


Part 1 Recording Macros and Getting Started with VBA 1

Chapter 1 Recording and Running Macros in the Office Applications 3

What is VBA and What Can You Do with It? 3

The Difference between Visual Basic and Visual Basic for Applications 4

Understanding Macro Basics 5

Recording a Macro 6

Planning the Macro 6

Starting the Macro Recorder 7

Naming the Macro 10

Assigning a Way to Run a Macro 15

Running a Macro 20

Recording a Sample Word Macro 21

Recording a Sample Excel Macro 24

Create a Personal Macro Workbook if You Don't Have One Yet 24

Record a Sample Excel Macro 24

Assigning a Way of Running the Macro 25

Assigning a Macro to a Quick Access Toolbar Button in Word 26

Assigning a Macro to a Shortcut key Combination 26

Deleting a Macro 26

The Bottom Line 28

Chapter 2 Getting Started with the Visual Basic Editor 29

Opening the Visual Basic Editor 29

Opening the Visual Basic Editor with a Macro Selected 30

Opening the Visual Basic Editor Directly 30

Navigating to a Macro 31

Using the Visual Basic Editor's Main Windows 32

The Project Explorer 32

The Object Browser 35

The Code Window 36

The Properties Window 40

The Immediate Window 42

Setting Properties for a Project 43

Customizing the Visual Basic Editor 45

Choosing Editor and View Preferences 46

Choosing and Laying Out the Editor Windows 53

Customizing the Toolbar and Menu Bar 54

Customizing the Toolbox 57

Closing the Visual Basic Editor and Returning to the Host Application 61

The Bottom Line 61

Chapter 3 Editing Recorded Macros 63

Testing a Macro in the Visual Basic Editor 63

Stepping through a Macro 65

Setting Breakpoints 66

Commenting Out Lines 67

Stepping Out of a Macro 68

Editing the Word Macro 68

Stepping Through the Transpose_Word_Right Macro 70

Running the Transpose_Word_Right Macro 70

Creating a Transpose_Word_Left Macro 70

Save Your Work 72

Editing the Excel Macro 72

Unhiding the Personal Macro Workbook 72

Opening the Macro for Editing 72

Editing the Macro 74

Saving Your Work 76

Editing a PowerPoint Macro 77

Save Your Work 81

The Bottom Line 83

Chapter 4 Creating Code from Scratch in the Visual Basic Editor 85

Setting Up the Visual Basic Editor for Creating the Procedures 85

Creating a Procedure for Word 86

Creating a Procedure for Excel 92

Creating a Procedure for PowerPoint 96

Creating a Procedure for Access 101

The Bottom Line 102

Part 2 Learning How to Work with VBA 105

Chapter 5 Understanding the Essentials of VBA Syntax 107

Getting Ready 107

Procedures 108

Functions 108

Subprocedures 109

Statements 109

Keywords 113

Expressions 113

Operators 113

Variables 114

Constants 114

Arguments 115

Specifying Argument Names vs. Omitting Argument Names 116

When to Include the Parentheses around the Argument List 117

Objects 117

Collections 117

Properties 118

Methods 118

Events 118

The Bottom Line 120

Chapter 6 Working with Variables, Constants, and Enumerations 123

Working with Variables 123

Choosing Names for Variables 124

Declaring a Variable 125

Choosing the Scope and Lifetime of a Variable 128

Specifying the Data Type for a Variable 134

Working with Constants 140

Declaring Your Own Constants 140

Choosing the Scope and Lifetime for Your Constants 141

Working with Enumerations 142

The Bottom Line 142

Chapter 7 Using Array Variables 145

What Is an Array? 145

Declaring an Array 146

Storing Values in an Array 148

Multidimensional Arrays 149

Declaring a Dynamic Array 150

Redimensioning an Array 150

Returning Information from an Array 151

Erasing an Array 151

Finding Out Whether a Variable Is an Array 151

Finding the Bounds of an Array 152

Sorting an Array 152

Searching through an Array 156

Performing a Linear Search through an Array 156

Performing a Binary Search Through an Array 160

The Bottom Line 165

Chapter 8 Finding the Objects, Methods, and Properties You Need 167

What Is an Object? 167

Properties 169

Methods 170

Working with Collections 172

Working with an Object in a Collection 173

Adding an Object to a Collection 173

Finding the Objects You Need 174

Using the Macro Recorder to Record the Objects You Need 174

Using the Object Browser 176

Using Help to Find the Object You Need 182

Using the Auto List Members Feature 184

Using Object Variables to Represent Objects 186

The Bottom Line 189

Part 3 Making Decisions and Using Loops and Functions 191

Chapter 9 Using Built-in Functions 193

What Is a Function? 193

Using Functions 194

Passing Arguments to a Function 196

Using Functions to Convert Data from One Type to Another 198

Using the Asc Function to Return a Character Code 199

Using the Val Function to Extract a Number from the Start of a String 199

Using the Str Function to Convert a Value to a String 200

Using the Format Function to Format an Expression 201

Using the Chr Function and Constants to Enter Special Characters in a string 205

Using Functions to Manipulate Strings 207

Using the Left, Right, and Mid Functions to Return Part of a String 208

Using InStr and InstrRev to Find a String within Another String 212

Using LTrim, RTrim, and Trim to Trim Spaces from a String 214

Using Len to Check the Length of a String 215

Using StrConv, LCase, and UCase to Change the Case of a String 216

Using the StrComp Function to Compare Apples to Apples 217

Using VBA's Mathematical Functions 218

Using VBA's Date and Time Functions 219

Using the DatePart Function to Parse Dates 221

Using the DateDiff Function to Return an Interval 222

Using the DateAdd Function to Add to a Date 223

Using File-Management Functions 223

Using the Dir Function to Check Whether a File Exists 223

Returning the Current Path 225

The Bottom Line 225

Chapter 10 Creating Your Own Functions 227

Components of a Function 228

Creating a Function 229

Starting a Function Manually 229

Starting a Function by Using the Add Procedure Dialog Box 230

Passing Arguments to the Function 231

Declaring the Data Types of Arguments 232

Specifying an Optional Argument 232

Controlling the Scope of a Function 232

Examples of Functions for Any VBA-Enabled Application 233

Creating a Function for Word 237

Creating a Function for Excel 239

Creating a Function for PowerPoint 240

Creating a Function for Access 242

The Bottom Line 244

Chapter 11 Making Decisions in Your Code 245

How Do You Compare Things in VBA? 245

Testing Multiple Conditions by Using Logical Operators 247

If Statements 249

If... Then 249

If... Then... Else Statements 252

If... Then... Elself... Else Statements 253

Creating Loops with If and GoTo 258

Nesting If Statements 259

Select Case Statements 261

Syntax 262

Example 262

The Bottom Line 264

Chapter 12 Using Loops to Repeat Actions 265

When Should You Use a Loop? 265

Understanding the Basics of Loops 266

Using for... Loops for Fixed Repetitions 267

For... Next Loops 267

For Each... Next Loops 275

Using an Exit For Statement 275

Using Do... Loops for Variable Numbers of Repetitions 276

Do While... Loop Loops 277

Do... Loop While Loops 280

Do Until... Loop Loops 282

Do... Loop Until Loops 284

Using an Exit Do Statement 286

While... Wend Loops 287

Nesting Loops 288

Avoiding Infinite Loops 290

The Bottom Line 291

Part 4 Using Message Boxes, Input Boxes, and Dialog Boxes 293

Chapter 13 Getting User Input with Message Boxes and Input Boxes 295

Opening a Procedure to Work On 296

Displaying Status Bar Messages in Word and Excel 297

Message Boxes 299

The Pros and Cons of Message Boxes 299

Message Box Syntax 299

Displaying a Simple Message Box 300

Displaying a Multiline Message Box 301

Choosing Buttons for a Message Box 303

Choosing an Icon for a Message Box 303

Setting a Default Button for a Message Box 304

Controlling the Modality of a Message Box 306

Specifying a Title for a Message Box 307

Title Bars Can Provide Useful Information 307

Adding a Help Button to a Message Box 308

Specifying a Help File for a Message Box 309

Using Some Arguments without Others 309

Retrieving a Value from a Message Box 310

Input Boxes 311

Input Box Syntax 311

Retreving Input from an Input Box 313

Forms: When Message Boxes and Input Boxes Won't Suffice 313

The Bottom Line 314

Chapter 14 Creating Simple Custom Dialog Boxes 315

When Should You Use a Custom Dialog Box? 315

Creating a Custom Dialog Box 316

Designing the Dialog Box 317

Inserting a User Form 317

Renaming a User Form 321

Adding Controls to the User Form 322

Renaming Controls 326

Moving a Control 327

Changing the Caption on a Control 328

Key Properties for the Toolbox Controls 330

Working with Groups of Controls 348

Aligning Controls 349

Placing Controls 350

Adjusting the Tab Order of the Dialog Box 351

Linking a Dialog Box to a Procedure 352

Loading and Unloading a Dialog Box 353

Displaying and Hiding a Dialog Box 354

Setting a Default Command Button 354

Retrieving the User's Choices from a Dialog Box 354

Returning a String from a Text Box 355

Returning a Value from an Option Button 355

Returning a Value from a Check Box 356

Returning a Value from a List Box 356

Returning a Value from a Combo Box 358

Examples of Connecting Dialog Boxes to Procedures 358

Word Example: The Move-Paragraph Procedure 358

General Example: Opening a File from a List Box 368

Creating the Code for the User Form 371

Using an Application's Built-in Dialog Boxes from VBA 374

Displaying a Built-in Dialog Box 375

Setting and Restoring Options in a Built-in Dialog Box 378

Which Button Did the User Choose in a Dialog Box? 378

Specifying a Time-Out for a Dialog Box 379

The Bottom Line 380

Chapter 15 Creating Complex Dialog Boxes 381

Creating and Working with Complex Dialog Boxes 382

Updating a Dialog Box to Reflect the User's Choices 382

Revealing a Hidden Part of a Dialog Box 382

Tracking a Procedure in a Dialog Box 386

Using Multipage Dialog Boxes and Tab Strip Controls 388

Creating a Modeless Dialog Box 398

Choosing a Position for the Dialog Box 399

Using Events to Control Forms 400

Events That Apply Only to the UserForm Object 403

Events That Apply to the UserForm Object and to Container Controls 408

Events That Apply to Many or Most Controls 412

Events That Apply Only to a Few Controls 425

The Bottom Line 426

Part 5 Creating Effective Code 429

Chapter 16 Building Modular Code and Using Classes 431

Creating Modular Code 431

What Is Modular Code? 431

Advantages of Using Modular Code 432

How to Approach Creating Modular Code 432

Arranging Your Code in Modules 433

Calling a Procedure 433

Making Logical Improvements to Your Code 435

Making Visual Improvements to Your Code 441

Creating and Using Classes 446

What Can You Do with Class Modules? 446

A Brief Overview 447

Planning Your Class 447

Creating the Class Module 448

Naming the Class 448

Setting the Instancing Property 448

Declaring Variables and Constants for the Class 448

Adding Properties to the Class 449

Adding Methods to the Class 453

Using Your Class 454

The Bottom Line 456

Chapter 17 Debugging Your Code and Handling Errors 457

Principles of Debugging 457

The Different Types of Errors 458

Language Errors 459

Compile Errors 459

Runtime Errors 461

Program Logic Errors 462

VBA's Debugging Tools 463

Break Mode 464

The Step Over and Step Out Commands 465

The Locals Window 466

The Watch Window 467

The Immediate Window 470

The Call Stack Dialog Box 473

Dealing with Infinite Loops 473

Dealing with Runtime Errors 474

When Should You Write an Error Handler? 474

Trapping an Error 474

Disabling an Error Trap 476

Resuming after an Error 477

Getting a Description of an Error 480

Raising Your Own Errors 480

Suppressing Alerts 481

Handling User Interrupts in Word, Excel, and Project 481

Disabling User Input While a Procedure Is Running 482

Disabling User Input While Part of a Procedure Is Running 482

Documenting Your Code 483

The Bottom Line 484

Chapter 18 Building Well-Behaved Code 487

What is a Well-Behaved Procedure? 487

Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star


4 Star


3 Star


2 Star


1 Star


Your Rating:

Your Name: Create a Pen Name or

Barnes & Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation


  • - By submitting a review, you grant to Barnes & and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Terms of Use.
  • - Barnes & reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously

    If you find inappropriate content, please report it to Barnes & Noble
    Why is this product inappropriate?
    Comments (optional)