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 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.

Auteur

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.

Résumé
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.

Contenu

Introduction xxvii

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

Understanding Macro Basics 5

Recording a Macro 6

Running a Macro 20

Recording a Sample Word Macro 21

Recording a Sample Excel Macro 24

Assigning a Way of Running the Macro 25

Deleting a Macro 26

The Bottom Line 28

Chapter 2 Getting Started with the Visual Basic Editor 29

Opening the Visual Basic Editor 29

Using the Visual Basic Editor's Main Windows 32

Setting Properties for a Project 43

Customizing the Visual Basic Editor 45

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

Editing the Word Macro 68

Editing the Excel Macro 72

Editing a PowerPoint Macro 77

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

Statements 109

Keywords 113

Expressions 113

Operators 113

Variables 114

Constants 114

Arguments 115

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

Working with Constants 140

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

The Bottom Line 165

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

What is an Object? 167

Working with Collections 172

Finding the Objects You Need 174

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

Using Functions to Convert Data from One Type to Another 198

Using Functions to Manipulate Strings 207

Using VBA's Mathematical Functions 218

Using VBA's Date and Time Functions 219

Using File-Management Functions 223

The Bottom Line 225

Chapter 10 Creating Your Own Functions 227

Components of a Function 228

Creating a Function 229

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 ...