Mastering VBA for Microsoft Office 2013 E-Book

Richard Mansfield

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 1621

Veröffentlichungsjahr: 2013

Beschreibung

A unique, comprehensive guide to creating custom apps with VBA Automating computing tasks to increase productivity is a goal for businesses of all sizes. Visual Basic for Applications (VBA) is a version of Visual Basic designed to be easily understandable for novice programmers, but still powerful enough for IT professionals who need to create specialized business applications. With this invaluable book, you'll learn how to extend the capabilities of Office 2013 applications with VBA programming and use it for writing macros, automating Office applications, and creating custom applications in Word, Excel, PowerPoint, Outlook, and Access. * Covers the basics of VBA in clear, systematic tutorials and includes intermediate and advanced content for experienced VB developers * Explores recording macros and getting started with VBA; learning how to work with VBA; 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 * Anchors the content with solid, real-world projects in Word, Excel, Outlook, PowerPoint, and Access Covering VBA for the entire suite of Office 2013 applications, Mastering VBA for Microsoft Office 2013 is mandatory reading.

Leseprobe

Table of Contents

Acknowledgments

About the Author

Introduction

Part 1: Recording Macros and Getting Started with VBA

Chapter 1: Recording and Running Macros in the Office Applications

What Is VBA and What Can You Do with It?

Understanding Macro Basics

Recording a Macro

Running a Macro

Recording a Sample Word Macro

Recording a Sample Excel Macro

Specifying How to Trigger an Existing Macro

Deleting a Macro

The Bottom Line

Chapter 2: Getting Started with the Visual Basic Editor

Opening the Visual Basic Editor

Using the Visual Basic Editor's Main Windows

Setting Properties for a Project

Customizing the Visual Basic Editor

The Bottom Line

Chapter 3: Editing Recorded Macros

Testing a Macro in the Visual Basic Editor

Editing the Word Macro

Editing the Excel Macro

Editing a PowerPoint Macro

The Bottom Line

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

Setting Up the Visual Basic Editor for Creating the Procedures

Creating a Procedure for Word

Creating a Procedure for Excel

Creating a Procedure for PowerPoint

Creating a Procedure for Access

The Bottom Line

Part 2: Learning How to Work with VBA

Chapter 5: Understanding the Essentials of VBA Syntax

Getting Ready

Procedures

Statements

Keywords

Expressions

Operators

Variables

Constants

Arguments

Objects

Collections

Properties

Methods

Events

The Bottom Line

Chapter 6: Working with Variables, Constants, and Enumerations

Working with Variables

Working with Constants

Working with Enumerations

The Bottom Line

Chapter 7: Using Array Variables

What Is an Array?

Declaring an Array

Storing Values in an Array

Multidimensional Arrays

Declaring a Dynamic Array

Redimensioning an Array

Returning Information from an Array

Erasing an Array

Finding Out Whether a Variable Is an Array

Finding the Bounds of an Array

Sorting an Array

Searching through an Array

The Bottom Line

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

What Is an Object?

Working with Collections

Finding the Objects You Need

Using Object Variables to Represent Objects

Team Programming and OOP

The Bottom Line

Part 3: Making Decisions and Using Loops and Functions

Chapter 9: Using Built-in Functions

What Is a Function?

Using Functions

Using Functions to Convert Data

Using Functions to Manipulate Strings

Using VBA's Mathematical Functions

Using VBA's Date and Time Functions

Using File-Management Functions

The Bottom Line

Chapter 10: Creating Your Own Functions

Components of a Function

Creating a Function

Examples of Functions for Any VBA-Enabled Office Application

Creating a Function for Word

Creating a Function for Excel

Creating a Function for PowerPoint

Creating a Function for Access

The Bottom Line

Chapter 11: Making Decisions in Your Code

How Do You Compare Things in VBA?

Testing Multiple Conditions by Using Logical Operators

Select Case Blocks

The Bottom Line

Chapter 12: Using Loops to Repeat Actions

When Should You Use a Loop?

Understanding the Basics of Loops

Using For…loops for Fixed Repetitions

Using Do… Loops for Variable Numbers of Repetitions

While… Wend Loops

Nesting Loops

Avoiding Infinite Loops

The Bottom Line

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

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

Opening a Procedure to Work On

Displaying Status-Bar Messages in Word and Excel

Message Boxes

Input Boxes

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

The Bottom Line

Chapter 14: Creating Simple Custom Dialog Boxes

When Should You Use a Custom Dialog Box?

Creating a Custom Dialog Box

Linking a Form to a Procedure

Retrieving the User's Choices from a Dialog Box

Examples of Connecting Forms to Procedures

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

The Bottom Line

Chapter 15: Creating Complex Forms

Creating and Working with Complex Dialog Boxes

Using Events to Control Forms

The Bottom Line

Part 5: Building Modular Code and Using Classes

Chapter 16: Building Modular Code and Using Classes

Creating Modular Code

Creating and Using Classes

The Bottom Line

Chapter 17: Debugging Your Code and Handling Errors

Principles of Debugging

The Different Types of Errors

VBA's Debugging Tools

Dealing with Infinite Loops

Dealing with Runtime Errors

Suppressing Alerts

Handling User Interrupts in Word, Excel, and Project

Documenting Your Code

The Bottom Line

Chapter 18: Building Well-Behaved Code

What Is a Well-Behaved Procedure?

Retaining or Restoring the User Environment

Leaving the User in the Best Position to Continue Working

Keeping the User Informed during the Procedure

Making Sure a Procedure Is Running under Suitable Conditions

Cleaning Up after a Procedure

The Bottom Line

Chapter 19: Securing Your Code with VBA's Security Features

Understanding How VBA Implements Security

Signing Your Macro Projects with Digital Signatures

Choosing a Suitable Level of Security

Locking Your Code

The Bottom Line

Part 6: Programming the Office Applications

Chapter 20: Understanding the Word Object Model and Key Objects

Examining the Word Object Model

Working with the Documents Collection and the Document Object

Working with the Selection Object

Creating and Using Ranges

Manipulating Options

The Bottom Line

Chapter 21: Working with Widely Used Objects in Word

Using Find and Replace via VBA

Working with Headers, Footers, and Page Numbers

Working with Sections, Page Setup, Windows, and Views

Working with Tables

The Bottom Line

Chapter 22: Understanding the Excel Object Model and Key Objects

Getting an Overview of the Excel Object Model

Understanding Excel's Creatable Objects

Managing Workbooks

Working with Worksheets

Working with the Active Cell or Selection

Working with Ranges

Setting Options

The Bottom Line

Chapter 23: Working with Widely Used Objects in Excel

Working with Charts

Working with Windows

Working with Find and Replace

Adding Shapes

The Bottom Line

Chapter 24: Understanding the PowerPoint Object Model and Key Objects

Getting an Overview of the PowerPoint Object Model

Understanding PowerPoint's Creatable Objects

Working with Presentations

Working with Windows and Views

Working with Slides

Working with Masters

The Bottom Line

Chapter 25: Working with Shapes and Running Slide Shows

Working with Shapes

Working with Headers and Footers

Setting Up and Running a Slide Show

The Bottom Line

Chapter 26: Understanding the Outlook Object Model and Key Objects

Getting an Overview of the Outlook Object Model

Working with the Application Object

Understanding General Methods for Working with Outlook Objects

Working with Messages

Working with Calendar Items

Working with Tasks and Task Requests

Searching for Items

The Bottom Line

Chapter 27: Working with Events in Outlook

Working with Application-Level Events

Working with Item-Level Events

Understanding Quick Steps

The Bottom Line

Chapter 28: Understanding the Access Object Model and Key Objects

Getting Started with VBA in Access

Getting an Overview of the Access Object Model

Understanding Creatable Objects in Access

Opening and Closing Databases

Working with the Screen Object

Using the DoCmd Object to Run Access Commands

The Bottom Line

Chapter 29: Manipulating the Data in an Access Database via VBA

Understanding How to Proceed

Preparing to Manage the Data in a Database

Opening a Recordset

Accessing a Particular Record in a Recordset

Searching for a Record

Returning the Fields in a Record

Editing a Record

Inserting and Deleting Records

Closing a Recordset

Saving a Recordset to the Cloud

The Bottom Line

Chapter 30: Accessing One Application from Another Application

Understanding the Tools Used to Communicate between Applications

Using Automation to Transfer Information

Using the Shell Function to Run an Application

Using Data Objects to Store and Retrieve Information

Communicating via DDE

Communicating via SendKeys

Going beyond VBA

The Bottom Line

Chapter 31: Programming the Office 2013 Ribbon

What Is XML?

Hiding the Editing Group on the Word Ribbon

Working with Excel and PowerPoint

Undoing Ribbon Modifications

Selecting the Scope of Your Ribbon Customization

Adding a New Group

Adding Callbacks

Adding Attributes

Using Menus and Lists

Toggling with a Toggle-Button Control

Modifying the Ribbon in Access

Adding a Callback in Access

What to Look For If Things Go Wrong

Where to Go from Here

The Bottom Line

Appendix: The Bottom Line

Chapter 1: Recording and Running Macros in the Office Applications

Chapter 2: Getting Started with the Visual Basic Editor

Chapter 3: Editing Recorded Macros

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

Chapter 5: Understanding the Essentials of VBA Syntax

Chapter 6: Working with Variables, Constants, and Enumerations

Chapter 7: Using Array Variables

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

Chapter 9: Using Built-in Functions

Chapter 10: Creating Your Own Functions

Chapter 11: Making Decisions in Your Code

Chapter 12: Using Loops to Repeat Actions

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

Chapter 14: Creating Simple Custom Dialog Boxes

Chapter 15: Creating Complex Forms

Chapter 16: Building Modular Code and Using Classes

Chapter 17: Debugging Your Code and Handling Errors

Chapter 18: Building Well-Behaved Code

Chapter 19: Securing Your Code with VBA's Security Features

Chapter 20: Understanding the Word Object Model and Key Objects

Chapter 21: Working with Widely Used Objects in Word

Chapter 22: Understanding the Excel Object Model and Key Objects

Chapter 23: Working with Widely Used Objects in Excel

Chapter 24: Understanding the PowerPoint Object Model and Key Objects

Chapter 25: Working with Shapes and Running Slide Shows

Chapter 26: Understanding the Outlook Object Model and Key Objects

Chapter 27: Working with Events in Outlook

Chapter 28: Understanding the Access Object Model and Key Objects

Chapter 29: Manipulating the Data in an Access Database via VBA

Chapter 30: Accessing One Application from Another Application

Chapter 31: Programming the Office 2013 Ribbon

Acquisitions Editor: Mariann Barsolo

Development Editor: David Clark

Technical Editor: Russ Mullen

Production Editor: Eric Charbonneau

Copy Editor: Judy Flynn

Editorial Manager: Pete Gaughan

Production Manager: Tim Tate

Vice President and Executive Group Publisher: Richard Swadley

Vice President and Publisher: Neil Edde

Book Designers: Maureen Forys and Judy Fung

Proofreader: Candace Cunningham

Indexer: Ted Laux

Project Coordinator, Cover: Katherine Crocker

Cover Designer: Ryan Sneed

Cover Image: ©iStockphoto.com/pic4you

Copyright © 2013 by John Wiley & Sons, Inc., Indianapolis, IndianaPublished simultaneously in Canada

ISBN: 978-1-118-69512-8ISBN: 978-1-118-75022-3 (ebk.)ISBN: 978-1-118-78630-7 (ebk.)

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, [phone], fax [phone]. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, [phone], fax [phone], or online at www.wiley.com/go/permissions.

Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or warranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including without limitation warranties of fitness for a particular purpose. No warranty may be created or extended by sales or promotional materials. The advice and strategies contained herein may not be suitable for every situation. This work is sold with the understanding that the publisher is not engaged in rendering legal, accounting, or other professional services. If professional assistance is required, the services of a competent professional person should be sought. Neither the publisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Web site is referred to in this work as a citation and/or a potential source of further information does not mean that the author or the publisher endorses the information the organization or Web site may provide or recommendations it may make. Further, readers should be aware that Internet Web sites listed in this work may have changed or disappeared between when this work was written and when it is read.

For general information on our other products and services or to obtain technical support, please contact our Customer Care Department within the U.S. at [phone], outside the U.S. at [phone] or fax [phone].

Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley.com.

Library of Congress Control Number: 2013945361

TRADEMARKS: Wiley, the Wiley logo, and the Sybex logo are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other countries, and may not be used without written permission. Microsoft is a registered trademark of Microsoft Corporation. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book.

Dear Reader,

Thank you for choosing Mastering VBA for Microsoft Office 2013. This book is part of a family of premium-quality Sybex books, all of which are written by outstanding authors who combine practical experience with a gift for teaching.

Sybex was founded in 1976. More than 30 years later, we're still committed to producing consistently exceptional books. With each of our titles, we're working hard to set a new standard for the industry. From the paper we print on to the authors we work with, our goal is to bring you the best books available.

I hope you see all that reflected in these pages. I'd be very interested to hear your comments and get your feedback on how we're doing. Feel free to let me know what you think about this or any other Sybex book by sending me an email at [email protected]. If you think you've found a technical error in this book, please visit http://sybex.custhelp.com. Customer feedback is critical to our efforts at Sybex.

Acknowledgments

I'd like to thank all the good people at Sybex who contributed to this book. Mariann Barsolo's encouragement made this book possible in the first place, and Pete Gaughan provided thoughtful guidance while launching the project. I am also indebted to development editor David Clark, whose valuable suggestions contributed to this book's tone and organization. Technical editor Russ Mullen carefully checked the book for accuracy and ensured that all the code examples work without any errors. Finally, thanks to Eric Charbonneau, production editor, the book went smoothly through its final stages—author review, design, and assembly. My gratitude also goes to copyeditor Judy Flynn, who, via a very close read, polished this book in many ways; she is truly an exceptional copy editor. Candace Cunningham is also great at her job, and she flagged important issues during her proofreading.

About the Author

Mastering VBA for Microsoft Office 2013 is Richard Mansfield's 45th book. His recent titles include CSS Web Design for Dummies (Wiley), Office Application Development All-in-One Desk Reference for Dummies (Wiley), How to Do Everything with Second Life (McGraw-Hill), and Programming: A Beginner's Guide (McGraw-Hill). Overall, his books have sold more than 500,000 copies worldwide and have been translated into 12 languages.

Introduction

Visual Basic for Applications (VBA) is a powerful tool that enables you to automate tasks in Microsoft Office applications.

Automating can save you and your colleagues considerable time and effort. Getting more work done in less time is usually good for your self-esteem, and it can do wonderful things for your job security and your career.

Where to Get This Book's Example Code

Throughout this book you'll find many code (programming) examples. Rather than type in the code, you'll save yourself time (and typo-debugging headaches) if you just copy the code from this book's web page, then paste it into the Visual Basic Editor. You can find all the code from this book—accurate, fully tested, and bug-free—at this book's web page:

www.sybex.com/go/masteringvbaoffice2013

If You Have Questions

I'm happy to hear from readers, so if you have any difficulty while using this book, write me at [email protected].

I'll try to respond the same day. We've all been beginners at some point, so don't feel your question is silly. If you're embarrassed, sign your email Connie and I'll think you're Connie.

What Can I Do with VBA?

You can use VBA to automate almost any action that you can perform interactively (manually) with an Office 2013 application. For example, in Word, VBA can create a document, add text to it, format it, edit it, and save it.

In Excel, you can automatically integrate data from multiple workbooks into a single workbook. PowerPoint's VBA can create a custom presentation, including the latest data drawn from a variety of sources with no human intervention. And in Access you can create new tables, populate them with data, and send the table up to the cloud.

VBA performs actions faster, more accurately, more reliably, and far more cheaply than any human. You can specify conditions for making a decision, then let VBA make those decisions for you in the future. By adding decision-making structures and loops (repetitions) to your code, you can go far beyond the range of actions that any human user can perform and finish the job in less than a second.

Beyond automating actions you would otherwise perform manually, VBA gives you the tools to create user interfaces for your code—message boxes, input boxes, and user forms—windows containing graphical objects that you can use to create forms and custom dialog boxes to display to the user.

Using VBA, you can also create custom applications that run within the host application. For example, you could build within PowerPoint a custom application that automatically creates presentations for you.

VBA can also communicate between applications. For example, Word can't do much in the way of mathematical calculations on sets of data: that's Excel's specialty. So, you can make Word start Excel running, perform some calculations, and then put the results into a Word document. Similarly, you could send graphs from Excel to PowerPoint or Outlook. You get the picture.

Because VBA provides a standard set of tools that differ only in the specializations of the host applications, once you've learned to use VBA in one application, you'll be able to apply that knowledge quickly to using VBA in another application. For example, you might start by learning VBA in order to manipulate Excel and then move on to using your VBA skills with Outlook. You'll need to learn the components particular to Outlook, because they're different from Excel's features, but you'll be up to speed rapidly. It's like shopping. Once you understand the basics, going to a hardware store differs from going to a bookstore only in the particulars.

As with any programming language, getting started with VBA involves a learning curve—but you'll be surprised how many tools VBA provides to help you quickly learn the fundamentals.

The VBA Editor is among the best programming environments available. It includes help features that list programming options while you're typing, that instantly point out problems (and suggest solutions), that prevent you from making some kinds of mistakes, that offer context- sensitive help (with example programming), that even automatically complete your lines (sentences) of programming code.

What's more, you can create some kinds of VBA programs without even writing a single line of code! You use the Macro Recorder tool built into Word and Excel—a great way to learn VBA more quickly. You turn on the Recorder and do what you want with Word or Excel manually via keyboard and mouse while the Recorder translates all your actions into programming code for you. Can't remember the programming code for saving a document? Just turn on the Recorder (click the icon on the lower left of Word's or Excel's status bar), save a document, then you've got the code it recorded:

ActiveDocument.Save

Another truly cool thing about VBA: Its words—most of the programming commands that make the language do what you want—are English words. Unlike less efficient programming languages, Basic strives to be human-friendly, understandable, readable. The programming code that saves Word's current document is ActiveDocument Save. For Excel, you use ActiveWorkbook Save.

For fun, search “save a document in c++” in Google, and you'll find lots of puzzling explanations attempting to accomplish this straightforward task in unfortunately unstraightforward ways, using often-puzzling diction. If you've tried programming in other languages, you'll find the simplicity and plain English of VBA a great relief. It's easy to learn, easy to use, yet no less powerful than any other programming language.

This book uses the Macro Recorder as the jumping-off point for you to start creating code. You first explore how to record macros (small programs) and then learn to edit this recorded code to make it do other things. After that easy introduction, you go on to explore the essentials of VBA diction and syntax. The book concludes with ambitious topics.

Word, because it's the most popular Office application and because it has the most sophisticated and efficient programming tools, is used for many of the examples in this book. But there are plenty of examples showing how to program Excel, PowerPoint, Outlook, and even Access. And code that works in one Office 2013 application will generally work with other applications in the suite—with little or sometimes no modification.

What's in This Book?

This book teaches you how to use VBA to automate your work in Office 2013 applications. For its general examples, the book focuses on Word, Excel, Outlook, and PowerPoint, because those are the Microsoft Office applications that you're most likely to have, and because they have less eccentric programming tools and strategies than Access. The last part of the book continues the discussion of how to program these four applications, but also increases coverage of Access.

Part 1 of the book, “Recording Macros and Getting Started with VBA,” comprises the following chapters:

Chapter 1 shows you how to record a macro using the Macro Recorder in Word and Excel. You also learn several ways to run macros and how to delete them.

Chapter 2 introduces you to the powerful VBA Editor, the application in which you create VBA code (either by editing recorded code or by writing code from scratch) and user forms. The second half of this chapter discusses how you can customize the Visual Basic Editor so that you can work in it more efficiently.

Chapter 3 shows you how to edit recorded macros, using the macros you recorded in Chapter 1. You learn how to step through and test a macro in the Visual Basic Editor.

Chapter 4 teaches you how to start writing code from scratch in the Visual Basic Editor. You create a procedure (a small program called a macro) for Word, one for Excel, and a third for PowerPoint.

Part 2, “Learning How to Work with VBA,” contains the following chapters:

Chapter 5 explains the essentials of VBA syntax, giving you a brief overview of the concepts you need to know. You also practice creating statements in the Visual Basic Editor.

Chapter 6 shows you how to work with variables and constants, which are used to store information for your procedures to work on.

Chapter 7 discusses how to use arrays. Arrays are like super-variables that can store multiple pieces of information at the same time.

Chapter 8 teaches you how to find the objects you need to create your procedures. You learn how to correctly write code involving objects by employing the Macro Recorder, the Object Browser, and the Help system. And you see how to use object variables to represent objects. Finally, you explore the uses of object models.

Part 3, “Making Decisions and Using Loops and Functions,” consists of the following chapters:

Chapter 9 describes how to use VBA's built-in functions—everything from string-conversion functions through mathematical and date functions to file-management functions.

Chapter 10 shows you how to create functions of your own to supplement the built-in libraries of functions. You create functions that work in any VBA-enabled application, together with application-specific functions for Word, Excel, and PowerPoint.

Chapter 11 shows you how to use conditional statements (such as

If

statements) to make decisions in your code. Conditional statements are key to making your code flexible and intelligent.

Chapter 12 covers how you can use loops to repeat actions in your procedures: fixed-iteration loops for fixed numbers of repetitions, and indefinite loops that repeat until they satisfy a condition you specify. You also learn how to avoid creating infinite loops, which can cause your code to run either forever or until your computer crashes.

Part 4, “Using Message Boxes, Input Boxes, and Dialog Boxes,” has the following chapters:

Chapter 13 shows you how to use message boxes to communicate with the users of your procedures and let them make simple decisions about how the procedures run. You also explore input boxes, which are dialog boxes that give the users a way to supply information the procedures need.

Chapter 14 discusses how to employ VBA's user forms to create custom dialog boxes that enable the users to supply information, make choices, and otherwise interact with your macros.

Chapter 15 discusses how to build more-complex dialog boxes. These include dynamic dialog boxes that update themselves when the user clicks a button, dialog boxes with hidden zones that the user can reveal to access infrequently used options, dialog boxes with multiple pages of information, and dialog boxes with controls that respond to actions the user takes.

Part 5, “Creating Effective Code,” contains the following chapters:

Chapter 16 illustrates the benefits of reusable modular code rather than single-purpose procedures and then shows you how to write this reusable code.

Chapter 17 explains the principles of debugging VBA code, examines the different kinds of errors that occur, and discusses how to deal with them.

Chapter 18 explores how to build well-behaved code that's stable enough to withstand being run under the wrong circumstances and civilized enough to leave the user in the best possible state to continue their work after it finishes running.

Chapter 19 discusses the security mechanisms that Windows and VBA provide for safeguarding VBA code and ensuring that you or your users do not run malevolent code (viruses, trojans, worms, and so on). The chapter discusses digital certificates and digital signatures, how to choose an appropriate security setting for the application you're using, and how to manage passwords.

Part 6, “Programming the Office Applications,” consists of these 12 chapters:

Chapter 20 explains the Word object model and shows you how to work with key objects in Word, including the

Document

object, the

Selection

object, and

Range

objects. You also learn how to set options in Word and manage cloud storage via such systems as Dropbox or Microsoft's SkyDrive.

Chapter 21 discusses how to work with widely used objects in Word, including the objects for Find and Replace; headers, footers, and page numbers; sections, page setup, windows, and views; and tables.

Chapter 22 introduces you to the Excel object model and shows you how to work with key objects in Excel, including the

Workbook

object, the

Worksheet

object, the

ActiveCell

object, and

Range

objects. You also learn how to set options in Excel.

Chapter 23 shows you how to work with charts, windows, and Find and Replace in Excel via VBA.

Chapter 24 gets you started working with the PowerPoint object model and the key objects that it contains. You work with

Presentation

objects,

Window

objects,

Slide

objects, and

Master

objects.

Chapter 25 teaches you how to go further with VBA in PowerPoint by working with shapes, headers and footers, and the VBA objects that enable you to set up and run a slide show automatically.

Chapter 26 introduces you to Outlook's object model and the key objects that it contains. You meet Outlook's creatable objects and main interface items; learn general methods for working with Outlook objects; and work with messages, calendar items, tasks and task requests, and searches.

Chapter 27 shows you how to work with events in Outlook. There are two types of events, application-level events and item-level events, which you can program to respond to both Outlook actions (such as new mail arriving) and user actions (such as creating a new contact).

Chapter 28 familiarizes you with the Access object model and demonstrates how to perform key tasks with some of its main objects.

Chapter 29 shows you how to manipulate the data in an Access database via VBA.

Chapter 30 shows you how to communicate between applications via VBA. You learn which tools are available, how to use Automation, how to work with the

Shell

function, and how to use data objects, DDE, and

SendKeys

.

Chapter 31 explores the various ways you can customize the Ribbon programmatically. It's not possible to customize it by VBA code alone. Instead, you must write XML code to modify what the user sees on the Ribbon and write

callbacks

(event-handler procedures in VBA) to respond when the user clicks one of the buttons or other controls you've added to the Ribbon. You see how to modify tabs, groups, and individual controls—in Word, PowerPoint, Excel, and, using different techniques, in Access.

How Should I Use This Book?

This book tries to present material in a sensible and logical way. To avoid repeating information unnecessarily, the chapters build on each other, so the later chapters generally assume that you've read the earlier chapters.

The first five parts of the book offer a variety of code samples using Word, Excel, PowerPoint, and, to a lesser extent, Access. If you have these applications (or some of them), work through these examples as far as possible to get the most benefit from them. While you may be able to apply some of the examples directly to your work, mostly you'll find them illustrative of general VBA techniques and principles, and you'll need to customize them to suit your own needs.

The sixth and last part of this book shows you some more-advanced techniques that are useful when using VBA to program Word, Excel, PowerPoint, Outlook, and Access. Work through the chapters that cover the application or applications that you want to program with VBA.

Chapters 30 and 31 are specialized, but quite useful. Chapter 30 shows you how to use one application to control another application; for example, you might use Word to contact Excel and exploit its special mathematic or graphing capabilities. And Chapter 31 shows you many different ways to program the Ribbon—the primary user interface in Office 2013 applications.

Is This Book Suitable for Me?

Yes.

This book is for anyone who wants to learn to use VBA to automate their work in a VBA-enabled application. Automating your work could involve anything from creating a few simple procedures that would enable you to perform some complex and tedious operations via a single keystroke to building a custom application with a complete interface that looks nothing like the host application's regular interface.

This book attempts to present theoretical material in as practical a context as possible by including lots of examples of the theory in action. For example, when you learn about loops, you execute short procedures that illustrate the use of each kind of loop so that you can see how and why they work and when to use them. And you'll also find many step-throughs—numbered lists that take you through a task, one step at a time. Above all, I've tried to make this book clear and understandable, even to readers who've never written any programming in their life.

Conventions Used in This Book

This book uses several conventions to convey information succinctly:

designates choosing a command from a menu. For example, “choose File ⇒ Open” means that you should pull down the File menu and choose the Open command from it.

+ signs indicate key combinations. For example, “press Ctrl+Shift+F9” means that you should simultaneously hold down the Ctrl, Shift, and F9 keys. Also, you'll sometimes see this: Press Ctrl+F, I. That means simultaneously press Ctrl and F, then release them and press I.

Some of these key combinations can be confusing at first (for example, “Ctrl++” means that you hold down Ctrl and press the + key—in other words, hold down Ctrl and Shift together and press the = key, because the + key is the shifted =.).

Likewise, “Shift+click” means that you should hold down the Shift key as you click with the mouse, and “Ctrl+click” means that you should hold down the Ctrl key as you click.

↑→↓← represent the arrow keys on your keyboard. These arrows are also represented in the text as “up-arrow,” “down-arrow,” etc. The important thing to note is that ← does not mean the Backspace key (which on many keyboards bears a similar arrow). The Backspace key is indicated simply by the words “Backspace” or “the Backspace key.”

Boldface

indicates that you are to type something.

Program font

indicates program items, or text derived from program lines. Complete program lines appear offset in separate paragraphs like the example below, while shorter expressions appear as part of the main text.

Sub Sample_Listing() ‘lines of program code look like this. End Sub

Italics

usually indicate either new terms being introduced or variable information (such as a drive letter that will vary from computer to computer and that you'll need to substitute for your own).

_ (a continuation underline character) indicates that a single line of code has been broken onto a second or subsequent line in the book (because of the limitations of page size). In the VBA Editor, you should enter these “broken” lines of code as a single line. For example, in this code sample, a single line of VBA Editor code has been broken into three lines when printed in this book:

MsgBox System.PrivateProfileString(““, _ ”HKEY_CURRENT_USER\Software\Microsoft\ _ Office\11.0\Common\AutoCorrect”, “Path”)

You'll also see sidebars throughout the book. These include asides, notes, tips, and warnings. They're a bit like footnotes, though less tedious. Each sidebar, no matter how small, has a headline—so you can quickly see if you want to read it.

Finally, each chapter includes one, longer,

Real World Scenario

sidebar: a case study, an important practical technique, or some other useful advice.

The Mastering Series

The Mastering series from Sybex provides outstanding instruction for readers with intermediate and advanced skills in the form of top-notch training and development for those already working in their field and clear, serious education for those aspiring to become pros. Every Mastering book includes the following:

Real World Scenarios, ranging from case studies to interviews, that show how the tool, technique, or knowledge presented is applied in actual practice

Skill-based instruction with chapters organized around real tasks rather than abstract concepts or subjects

Self-review test questions so you can be certain you're equipped to do the job right

For More Information

Sybex strives to keep you supplied with the latest tools and information you need for your work. Please check the website at www.sybex.com/go/masteringvbaoffice2013, where we'll post additional content and updates that supplement this book if the need arises.

Part 1

Recording Macros and Getting Started with VBA

Chapter 1: Recording and Running Macros in the Office Applications

Chapter 2: Getting Started with the Visual Basic Editor

Chapter 3: Editing Recorded Macros

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

Chapter 1

Recording and Running Macros in the Office Applications

In this chapter, you'll learn the easiest way to get started with Visual Basic for Applications (VBA): recording simple macros using a Macro Recorder that is built into the Office applications. Then you'll see how to run your macros to perform useful tasks.

I'll define the term macro in a moment. For now, just note that by recording macros, you can automate straightforward but tediously repetitive tasks and speed up your regular work. You can also use the Macro Recorder to create VBA code that performs the actions you need and then edit the code to customize it—adding flexibility and power. In fact, VBA is a real powerhouse if you know how to use it. This book shows you how to tap into that power.

In this chapter you will learn to do the following:

Record a macro

Assign a macro to a button or keyboard shortcut

Run a macro

Delete a macro

What Is VBA and What Can You Do with It?

Visual Basic for Applications is a programming language created by Microsoft that can be built into applications. You use VBA to automate operations in applications that support it. All the main Office applications—Word, Excel, Outlook, Access, and PowerPoint—include VBA, so you can automate operations through most Office applications.

And please don't be put off by the notion that you'll be programming: As you'll see shortly, working with VBA is nearly always quite easy. In fact, quite often you need not actually write any VBA yourself; you can merely record it—letting the Office application write all the VBA “code.” The phrase automate operations in applications is perhaps a bit abstract. VBA allows you to streamline many tasks, avoid burdensome repetition, and improve your efficiency. Here are some examples: