Mastering VBA for Microsoft Office 2013

Richard Mansfield

• Publisher: Sybex
• Year: 2013
• Print ISBN: 9781118695128
• eBook ISBN: 9781118786307
• Edition: 2
• Topic: Computer Science
• Subtopic: Programming Languages
• Index: Computer Science

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.

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:

• You can record a macro that automatically carries out a series of actions that you frequently perform. Let's say that you often edit Word documents written by a co-worker, but she sets the zoom level to 100. You prefer a zoom level of 150. All you need to automatically fix this is this VBA code:

 ActiveWindow.ActivePane.View.Zoom.Percentage = 150 

And don't worry, you need not even know these programming terms like ActiveWindow or View.Zoom. When you turn on the Macro Recorder, then perform these actions (clicking View, then clicking Zoom, then setting the percentage), all your actions are translated into the necessary VBA code. You write no code at all.

• You can write code that performs actions a certain number of times and that makes decisions depending on the situation in which it is running. For example, you could write code that takes a series of actions on every presentation that's open in PowerPoint.
• You can have your macros interact with the user by displaying forms, or custom dialog boxes, that enable the user to make choices and specify settings while the macro is running. For example, you might display a set of formatting options—showing captioned controls such as check boxes and option buttons—that the user can select. Then when the user closes the dialog box, your macro takes appropriate actions based on the user's input.
• You can take actions via VBA that you can't take (or take easily) by directly manipulating the user interface. For example, when you're working interactively in most applications, you're limited to working with the active file—the active document in Word, the active workbook in Excel, and so on. By using VBA, you can manipulate files that aren't active.
• You can make one application manipulate another application. For example, you can make Word place a table from a Word document into an Excel worksheet.

The Difference between Visual Basic and Visual Basic for Applications

VBA is based on Visual Basic, a programming language derived from BASIC. BASIC stands for Beginner's All-Purpose Symbolic Instruction Code. BASIC is designed to be user-friendly because it employs recognizable English words (or variations on them) rather than the abstruse and incomprehensible programming terms found in languages like COBOL. In addition to its English-like diction, BASIC's designers endeavored to keep its punctuation and syntax as simple and familiar as possible.

Visual Basic is visual in that it offers efficient shortcuts such as drag-and-drop programming techniques and many graphical elements.

Visual Basic for Applications is a version of Visual Basic tailored to Microsoft Office applications. The set of objects (features and behaviors) available in each application differs because no two applications share the same features and commands.

For example, some VBA objects available in Word are not available in Excel (and vice versa) because some of Word's features, like the Table of Contents generator, are not appropriate in Excel.

However, the large set of primary commands, fundamental structure, and core programming techniques of VBA in Word and VBA in Excel are the same. So you'll find that it's often quite easy to translate your knowledge of VBA in Word to VBA in Excel (or indeed in any VBA-enabled application).

For example, you'd use the Save method (a method is essentially an action that can be carried out) to save a file in Excel VBA, Word VBA, or PowerPoint VBA. What differs is the object involved. In Excel VBA, the command would be ActiveWorkbook.Save, whereas in Word VBA it would be ActiveDocument.Save and in PowerPoint it would be ActivePresentation.Save.

VBA always works with a host application (such as Access or Word). With the exception of some stand-alone programs that are usually best created with Visual Studio Tools for Office, a host application always needs to be open for VBA to run. This means that you can't build stand-alone applications with VBA the way you can with Visual Basic .NET or Visual Studio Tools for Office (VSTO). If you wish, you can hide the host application from the user so that all they see is the interface (typically user forms) that you give to your VBA procedures. By doing this, you can create the illusion of a stand-alone application. Whether you need to employ this technique will depend on the type of programming you do.

---

What Are Visual Basic .NET and Visual Basic Express?

Visual Basic .NET (VB .NET) is just one version of Microsoft's long history of BASIC language implementations. BASIC contains a vast set of libraries of prewritten code that allow you to do pretty much anything that Windows is capable of. Although VB .NET is generally employed to write stand-alone applications, you can tap into its libraries from within a VBA macro. Just remember, each Office application has its own object library, but the .NET libraries themselves contain many additional capabilities (often to manipulate the Windows operating system). So, if you need a capability that you can't find within VBA or an Office application's object library, the resources of the entire .NET library are also available to you. Visual Basic Express is a free version of VB .NET. After you've worked with VBA in this book, you might want to explore VB .NET at

www.microsoft.com/visualstudio/eng/products/visual-studio-express-products

You'll find versions for both traditional desktop Windows as well as Windows 8.

---

Understanding Macro Basics

A macro is a sequence of commands you or a user can repeat at will. That's exactly the definition of a computer program. Macros, however, are generally short programs—dedicated to a single task. Think of it like this: A normal computer program, such as Photoshop or Internet Explorer (IE), has many capabilities. IE can prevent pop-up ads, block websites, display full-screen when you press F11, and so on. A macro is smaller, dedicated to accomplishing just one of these tasks, such as displaying full-screen.

In some applications, you can set a macr...

Table of contents

• Cover
• Acknowledgments
• About the Author
• Introduction
• Part 1: Recording Macros and Getting Started with VBA
• Part 2: Learning How to Work with VBA
• Part 3: Making Decisions and Using Loops and Functions
• Part 4: Using Message Boxes, Input Boxes, and Dialog Boxes
• Part 5: Building Modular Code and Using Classes
• Part 6: Programming the Office Applications
• Appendix: The Bottom Line