Mastering VBA for Microsoft Office 365, 2019 Edition E-Book

Richard Mansfield

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

Seitenzahl: 1677

Veröffentlichungsjahr: 2019

Beschreibung

Customize and ramp-up Office 365 applications NOTE: Please click Downloads (located in the menu on the left) to download "Full Code Download." The revised 2019 edition of Mastering VBA Microsoft Office 365 offers an accessible guide that shows how to extend the capabilities of Office 365 applications with VBA programming. Written in clear terms and understandable language, the book includes systematic tutorials and contains both intermediate and advanced content for experienced VB developers. Designed to be comprehensive, the book addresses not just one Office application, but the entire Office suite. To enhance understanding, the content is explored in real-world projects in Word, Excel, Outlook, and PowerPoint. Since the technical programming methods in the Office applications continue to evolve, the updated 2019 edition reviews the changes to the program. Code libraries, the API, and the object model for each Office program have been modified during the three years since the last version of this book. Various elements within VBA have been deprecated or replaced, and the surface UI changed as well. The updated 2019 edition covers topics such as: * 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 Written for all levels of Microsoft Office 365 users, Mastering VBA Microsoft Office 365: 2019 Edition explains how to customize and automate the Office suite of applications.

Leseprobe

Table of Contents

Cover

Acknowledgments

About the Author

Introduction

Where to Get This Book's Example Code

If You Have Questions

What Can I Do with VBA?

What's in This Book?

How Should I Use This Book?

Is This Book Suitable for Me?

Conventions Used in This Book

For More Information

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 a Word Macro

Editing an 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 to Create Macros

Creating a Procedure for Word

Creating a Macro 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?

Sorting an Array

Searching 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 the

Format

Function to Format an Expression

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 All VBA-Enabled Office Applications

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 Macro

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

Working with Groups of Controls

Linking a Form to a Procedure

Retrieving the User's Choices from a Dialog Box

Examples Showing How to Connect Forms to Procedures

Using an Application's Built-In Dialog Boxes

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: Creating Effective Code

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, and Excel

Documenting Your Code

The Bottom Line

Chapter 18: Building Well-Behaved Code

What Is a Well-Behaved Macro?

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 Macro Is Running Under Suitable Conditions

Cleaning Up After a Procedure

The Bottom Line

Chapter 19: Exploring 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

Saving a Document

Opening a Document

Closing a Document

Changing a Document's Template

Printing a Document

Working with the

ActiveDocument

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 Objects

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

How Event-Handler Procedures Differ from Ordinary Macros

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

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: Exploring 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: Accessing One Application from Another Application

Index

End User License Agreement

List of Tables

Chapter 6

TABLE 6.1: Type-declaration characters

TABLE 6.2: VBA variable data types

Chapter 8

TABLE 8.1: Core properties for collections in VBA

TABLE 8.2: Object Browser icons

Chapter 9

TABLE 9.1: VBA's functions for simple data conversion

TABLE 9.2: VBA's functions for complex data conversion

TABLE 9.3: Constants that specify how a year starts

TABLE 9.4: Predefined numeric formats

TABLE 9.5: Characters for creating your own number formats

TABLE 9.6: Characters for creating your own date and time formats

TABLE 9.7: Characters for creating your own string formats

TABLE 9.8: VBA character codes and character constants

TABLE 9.9: VBA's string-manipulation functions

TABLE 9.10: The most common conversion constants

TABLE 9.11: VBA's mathematical functions

TABLE 9.12: VBA's date and time functions

TABLE 9.13: The options for the FirstWeekOfYear constant

Chapter 11

TABLE 11.1: VBA's comparison operators

TABLE 11.2: VBA's logical operators

Chapter 12

TABLE 12.1: VBA's loop types

TABLE 12.2: Components of the syntax for a

For…Next

loop

Chapter 13

TABLE 13.1: Message-box types, controlled by the

buttons

argument

TABLE 13.2: Arguments for message-box icons

TABLE 13.3: Arguments for default message-box buttons

TABLE 13.4: Arguments for message-box modality

TABLE 13.5: Constants for selected buttons

Chapter 14

TABLE 14.1: Properties common to most or all controls

TABLE 14.2: TextAlign property values for the Label control

TABLE 14.3: Key properties of the TextBox control

TABLE 14.4: Key properties of the ComboBox and ListBox controls

TABLE 14.5: Properties of the Frame control

TABLE 14.6: Unique properties of the CommandButton control

TABLE 14.7: Properties of the TabStrip and MultiPage controls

TABLE 14.8: Properties of the ScrollBar and SpinButton controls

TABLE 14.9: Set these properties of your label

TABLE 14.10: Set these properties of the ListBox

TABLE 14.11: Set these properties of the CommandButton

TABLE 14.12: Click return values

Chapter 15

TABLE 15.1: Constants and values for the

PictureAlignment

property

TABLE 15.2: Constants and values for the

PicturePosition

property

TABLE 15.3:

StartUpPosition

property settings

TABLE 15.4: Events that VBA supports and the objects and controls associated ...

TABLE 15.5: Values and constants for the

CloseMode

argument

TABLE 15.6:

ActionX

and

ActionY

constants and values for the

Scroll

event

TABLE 15.7:

Shift

constants and values

TABLE 15.8:

Button

values and constants

TABLE 15.9:

Shift

values

TABLE 15.10:

Button

values

TABLE 15.11:

DragState

constants and values

TABLE 15.12:

Effect

constants and values

TABLE 15.13:

Action

constants and values

Chapter 16

TABLE 16.1: Memory consumed by the different types of variables

TABLE 16.2: Properties of the

Book

class

Chapter 20

TABLE 20.1:

WdSaveFormat

constants

TABLE 20.2:

WdOpenFormat

constants for opening a document

TABLE 20.3: Selection types in Word

TABLE 20.4: Word story types

TABLE 20.5: Information available in the

Information

property

TABLE 20.6: Units of movement for the

EndOf

method

Chapter 21

TABLE 21.1: Properties of the

Find

object

TABLE 21.2: Options for Wrap offered by Word

TABLE 21.3:

wdPageNumberAlignment

constants and values

Chapter 22

TABLE 22.1: Constants for creating a chart workbook, macro sheet, or workshee...

TABLE 22.2:

XlFileFormat

constants for widely used formats

TABLE 22.3: Values for the

UpdateLinks

argument

TABLE 22.4: Constants for the

Type

argument for the

SpecialCells

method

TABLE 22.5: Constants for the

Value

argument for the

SpecialCells

method

Chapter 24

TABLE 24.1: Example

FileFormat

constants for saving PowerPoint files

Chapter 25

TABLE 25.1: Shapes and the methods for adding them to slides

TABLE 25.2:

Format

property constants for date and time headers and footers

Chapter 26

TABLE 26.1:

Type

arguments for the

SaveAs

method

Chapter 27

TABLE 27.1: Common item-level events

TABLE 27.2: Events that apply to explorers, inspectors, or views

TABLE 27.3: Events that apply to folders

TABLE 27.4: Events that apply to items and results

TABLE 27.5: Events that apply to reminders

TABLE 27.6: Events that apply to the

SyncObject

object

Chapter 28

TABLE 28.1: Methods of the DoCmd object

Chapter 29

TABLE 29.1: Constants and values for the

windowstyle

argument

TABLE 29.2:

SendKeys

keywords for movement and editing keys

TABLE 29.3:

SendKeys

symbols for meta keys

List of Illustrations

Chapter 1

Figure 1.1 Click here to add your Developer tab.

Figure 1.2 Find this Record Macro button on the status bar.

Figure 1.3 In the Record Macro dialog box, enter a name for the macro you're...

Figure 1.4 You can use the Developer tab on the Ribbon to work with macros....

Figure 1.5 The dialog boxes supplied by Word and Excel showing invalid macro...

Figure 1.6 Choose a way to run the macro in Word's Options dialog box.

Figure 1.7 Word gives the menu item or toolbar button the full name of the m...

Figure 1.8 Set a shortcut key combination for the macro in the Customize Keyb...

Figure 1.9 Clicking the blank workbook

Chapter 2

Figure 2.1 The Visual Basic Editor with the Transpose_Word_Right macro open ...

Figure 2.2 If the module contains two or more macros, scroll to the macro yo...

Figure 2.3 Use the Project Explorer to navigate to the module with which you...

Figure 2.4 Folder view (left) displays the objects separated into folders be...

Figure 2.5 The Object Browser provides a quick way to look up objects and th...

Figure 2.6 You edit macros in the Code window.

Figure 2.7 The Complete Word feature automatically completes a term when you...

Figure 2.8 The Edit toolbar contains features used when working in the Code ...

Figure 2.9 Use the Editor's Quick Info feature to see a VB language command'...

Figure 2.10 Use the Auto List Members command to enter code items quickly an...

Figure 2.11 The List Constants feature saves you time and effort, especially ...

Figure 2.12 Use the Data Tips feature to check the value of a variable when ...

Figure 2.13 Use the Properties window to view the properties of a project, u...

Figure 2.14 Use the Immediate window for on-the-fly work and information.

Figure 2.15 Use the Project Properties dialog box to view and set the proper...

Figure 2.16 The Protection page of the Project Properties dialog box lets yo...

Figure 2.17 The Editor page of the Options dialog box

Figure 2.18 The Editor Format page of the Options dialog box

Figure 2.19 The General page of the Options dialog box

Figure 2.20 The Docking page of the Options dialog box

Figure 2.21 Use the Customize dialog box to tailor the Visual Basic Editor's...

Figure 2.22 The quickest way to add a control to the Toolbox is to drag it t...

Figure 2.23 In the Additional Controls dialog box, click the check boxes for ...

Chapter 3

Figure 3.1 Click the Run Sub/UserForm button on the Standard toolbar to star...

Figure 3.2 Stepping through a macro recorded in Word

Figure 3.3 Use a breakpoint (the red circle that appears in the margin indic...

Figure 3.4 The Debug toolbar contains commands for running code, stepping in...

Figure 3.5 If gray, this means the Personal Macro Workbook isn't hidden. Skip...

Chapter 4

Figure 4.1 The default configuration for the VBA Editor

Figure 4.2 The Advanced Track Changes Options dialog box in Word

Figure 4.3 The Visual Basic Editor's List Properties/Methods list displays t...

Figure 4.4 Use the Object Browser to find the objects, methods, and properti...

Figure 4.5 The result of the search for “maximize” in the Object Browser

Figure 4.6 Use the list of constants to enter a constant quick and easilly.

Figure 4.7 The result of the search for “recent” in the Object Browser

Figure 4.8 The ActivePresentation property screen

Figure 4.9 Select the Slides object from the list.

Figure 4.10 The Slides Collection Object Help screen

Figure 4.11 The Auto Quick Info feature displays the syntax for the Add meth...

Figure 4.12 Choose the ppLayoutTitle constant for the Layout argument.

Chapter 5

Figure 5.1 The Visual Basic Editor set up alongside a Word document. This is...

Figure 5.2 Optional arguments are enclosed within brackets.

Chapter 6

Figure 6.1 Interior words are capitalized in the camelCase style.

Figure 6.2 Private variable declarations appear in the declarations area.

Figure 6.3 Out pops a list of message box button type constants.

Chapter 7

Figure 7.1 The single-dimensional array created by the statement

Dim curMont

...

Figure 7.2 The single-dimensional array created by the statement

Dim curMont

...

Figure 7.3 A simple String array with three values assigned

Figure 7.4 You can think of a two-dimensional array as consisting of rows an...

Figure 7.5 The

Sort_an_Array

procedure displays a message box of the unsorted...

Figure 7.6 When the

Sort_an_Array

procedure has finished sorting, it display...

Figure 7.7 The

Linear_Search_of_Array

procedure displays an input box prompt...

Figure 7.8 Line 34 of Listing 7.2 displays a message box showing the user th...

Figure 7.9 The

Binary_Search_of_Array

procedure prompts the user to enter a n...

Figure 7.10 The

Binary_Search_of_Array

procedure tells the user whether the ...

Figure 7.11 Use the Locals window to track the values of the

intTop

,

intMidd

...

Chapter 8

Figure 8.1 The Object Browser provides information on built-in objects and c...

Figure 8.2 You add and remove object libraries by using the References dialog...

Figure 8.3 The generic VBA portal

Figure 8.4 Put your insertion cursor on a command, and then Press F1 to get ...

Figure 8.5 The main Help page for the ActiveWindow property

Figure 8.6 These links are up-to-date.

Figure 8.7 Using the Auto List Members feature to enter code

Figure 8.8 When you strongly type your Object variables, you get the full be...

Figure 8.9 You can use the Locals window to help identify the object type th...

Chapter 9

Figure 9.1 Using the

Left

function to take the left part of a string—in this...

Figure 9.2 An “Argument not optional” error occurs when you omit a required ...

Figure 9.3 You can use the

Dir

function to check whether a file exists so th...

Chapter 10

Figure 10.1 When you type a

Function

statement and press Enter, the Visual B...

Figure 10.2 You can also use the Add Procedure dialog box to specify elements...

Figure 10.3 The Visual Basic Editor displays a ToolTip of Auto Quick Info fo...

Figure 10.4 The

TestForSmog

procedure prompts for the car's year and then di...

Figure 10.5 Type in some data—any data will do—so you can experiment with Acc...

Chapter 12

Figure 12.1 A

Do While… Loop

loop tests for a condition before perform...

Figure 12.2 In a

Do… Loop While

loop, the actions in the loop run once...

Figure 12.3 A

Do Until… Loop

loop runs while the condition is

False

an...

Figure 12.4 In a

Do… Loop Until

loop, the actions in the loop are run ...

Figure 12.5 The dialog box to create folders and subfolders

Chapter 13

Figure 13.1 In some applications, you can display information on the status ...

Figure 13.2 When you specify only the

prompt

argument to display a simple me...

Figure 13.3 VBA's Auto Quick Info feature prompts you with the syntax for th...

Figure 13.4 You can display a multiline message box by using line-feed and c...

Figure 13.5 The

vbYesNo

constant produces a message box with Yes and No butt...

Figure 13.6 Adding an icon gives a message box greater visual impact.

Figure 13.7 Specify a default button to steer the user toward a particular b...

Figure 13.8 Usually, you'll want to specify the

title

argument for your mess...

Figure 13.9 Use the

vbMsgBoxHelpButton

constant to add a Help button to a me...

Figure 13.10 Use an input box to retrieve a single piece of information from...

Figure 13.11 The input box comes with a predefined set of buttons.

Chapter 14

Figure 14.1 The first step in creating a new dialog box is to start a new us...

Figure 14.2 The General page of the Options dialog box includes options for t...

Figure 14.3 You can choose either an alphabetized or a categorized list in t...

Figure 14.4 Use the Toolbox to add controls to the user form.

Figure 14.5 When you click in the user form after clicking a control on the ...

Figure 14.6 Once you've placed a control, you can resize it as necessary by ...

Figure 14.7 If a control isn't currently selected, you can move it by clicki...

Figure 14.8 If a control is selected, move the mouse pointer over its select...

Figure 14.9 To change the caption on a control, select the control, and then...

Figure 14.10 By setting the

TripleState

property of a check box to True, you...

Figure 14.11 Use the

SpecialEffect

property to display a flat check box (bot...

Figure 14.12 You can work with multiple controls simultaneously by grouping ...

Figure 14.13 You can resize all the controls in a group by dragging a sizing...

Figure 14.14 To work with one control in a group, start by selecting the gro...

Figure 14.15 Use the Tab Order dialog box to arrange the controls in your us...

Figure 14.16 The Move Current Paragraph dialog box that you will connect to ...

Figure 14.17 Start by placing two frames in the user form.

Figure 14.18 Place four option buttons in the Movement frame like this.

Figure 14.19 Place a check box in the Insertion Point frame.

Figure 14.20 Add two command buttons and set their properties.

Figure 14.21 The user form you'll build in this example contains a list box ...

Chapter 15

Figure 15.1 Word's Find And Replace dialog box hides some of its options (to...

Figure 15.2 The top part of this Inventories form offers the most frequently...

Figure 15.3 The Create New Employee Web Page form provides users with instru...

Figure 15.4 The second stage of the Create New Employee Web Page dialog box....

Figure 15.5 Use the Rename dialog box to set the caption, accelerator key, an...

Figure 15.6 Use the Move Up and Move Down buttons in the Page Order dialog bo...

Figure 15.7 By using multiple pages in a dialog box, you can achieve a clean...

Figure 15.8 Using a TabStrip control to create a multitab dialog box. The Ta...

Figure 15.9 VBA includes extensive graphics features—you can make your forms...

Figure 15.10 You can use the

Resize

event of a user form to resize or reposi...

Chapter 16

Figure 16.1 The Call Stack dialog box lets you see which procedure has calle...

Figure 16.2 VBA's helpful Auto List Members feature shows the properties and...

Chapter 17

Figure 17.1 The VBA Editor helps debug your code by identifying many compile...

Figure 17.2 Other errors appear only when you try to run the code.

Figure 17.3 The “Method or data member not found” error tells you that you'v...

Figure 17.4 An unhandled runtime error causes VBA to display a message box s...

Figure 17.5 The Debug toolbar provides 13 commands for debugging your proced...

Figure 17.6 You can enter Break mode by pressing Ctrl+Break and then clickin...

Figure 17.7 Entering Break mode from a runtime-error dialog box like this one...

Figure 17.8 Use the Locals window to see at a glance all the expressions in ...

Figure 17.9 Use the Watch window to track the values of variables and expres...

Figure 17.10 In the Add Watch dialog box, specify the watch expression you w...

Figure 17.11 You can edit your watch expressions in the Edit Watch dialog bo...

Figure 17.12 Use the Quick Watch dialog box to get quick information on a va...

Chapter 19

Figure 19.1 Suspect sources trigger this security warning when opened in Off...

Figure 19.2 You can self-sign a certificate, but Office only permits such ce...

Figure 19.3 Windows provides the Certificates dialog box to manage digital c...

Figure 19.4 Windows includes the Certificate Import Wizard to manage digital ...

Figure 19.5 On the Certificate Store page of the Certificate Import Wizard, c...

Figure 19.6 Use the Select Certificate Store dialog box to specify the certif...

Figure 19.7 The warning the Certificate Manager displays when you're about t...

Figure 19.8 Use the Digital Signature dialog box to specify the digital sign...

Figure 19.9 Use this Windows Security dialog box to confirm your choice of c...

Figure 19.10 Use the Certificate Details dialog box to examine the propertie...

Figure 19.11 The Details page of the Certificate Details dialog box contains ...

Figure 19.12 On the Macro Settings page of the Trust Center dialog box, choo...

Figure 19.13 On this page of the Trust Center dialog box, choose whether you...

Figure 19.14 File Block Settings specify what types of documents you want bl...

Figure 19.15 Use the Protection page of the Project Properties dialog box to ...

Figure 19.16 When you open a locked project, you need to enter the password ...

Chapter 20

Figure 20.1 A Word Help website

Figure 20.2 Drilling down in the Word Help site

Figure 20.3 The entries in the Word Object Model Reference will help you wri...

Chapter 22

Figure 22.1 The entries in the Excel object model reference will help you wr...

Figure 22.2 When deleting a worksheet by hand, Excel displays this warning d...

Figure 22.3 The currently active cell is fetched via the

ActiveCell

object....

Chapter 23

Figure 23.1 This chart was generated in a procedure, using the

Add

method of...

Chapter 24

Figure 24.1 The entries in the PowerPoint object-model reference will help y...

Chapter 25

Figure 25.1 msoTextEffect14 offers a metallic look.

Figure 25.2 Here's a selection of animation effects available for use in Pow...

Chapter 26

Figure 26.1 Sample code found in the Outlook object-model reference will hel...

Figure 26.2 Listing the folders contained in the

NameSpace

object

Figure 26.3 A new task, added programmatically

Figure 26.4 A message of high importance is created

Figure 26.5 To see the results, open your calendar.

Chapter 27

Figure 27.1 The drop-down list on the right shows all the events available i...

Figure 27.2 This note automatically appears when the user first starts Outlo...

Figure 27.3 You can experiment with the sample Quick Steps to get an idea ho...

Chapter 28

Figure 28.1 Use the Macro Designer window to create a new Access-style “macro...

Figure 28.2 The entries in the Access object-model reference will help you w...

Chapter 29

Figure 29.1 Once you've loaded the Excel object library, you can view its co...

Guide

Cover

Table of Contents

Begin Reading

Pages

iii

iv

v

vii

ix

xxix

xxx

xxxi

xxxii

xxxiii

xxxiv

xxxv

xxxvi

1

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

509

510

511

512

513

514

515

516

517

518

519

520

521

522

523

524

525

526

527

528

529

530

531

532

533

534

535

537

538

539

540

541

542

543

544

545

546

547

548

549

550

551

553

554

555

556

557

558

559

560

561

562

563

564

565

566

567

568

569

570

571

572

573

574

575

577

578

579

580

581

582

583

584

585

586

587

588

589

590

591

592

593

594

595

596

597

598

599

600

601

602

603

604

605

606

607

608

609

610

611

612

613

614

615

616

617

618

619

620

621

622

623

624

625

626

627

628

629

630

631

632

633

634

635

636

637

638

639

640

641

642

643

645

646

647

648

649

650

651

652

653

654

655

656

657

658

659

660

661

662

663

664

665

666

667

668

669

670

671

672

673

674

675

676

677

678

679

680

681

682

683

684

685

687

688

689

690

691

692

693

694

695

696

697

698

699

700

701

702

703

704

705

706

707

709

710

711

712

713

714

715

716

717

718

719

720

721

722

723

724

725

726

727

728

729

731

732

733

734

735

736

737

738

739

740

741

742

743

744

745

746

747

748

749

750

751

752

753

755

756

757

758

759

760

761

762

763

764

765

766

767

768

769

770

771

772

773

774

775

776

777

778

779

780

781

782

783

784

785

786

787

788

789

790

791

792

793

794

795

796

797

798

799

800

801

802

803

804

805

806

807

808

809

810

811

812

813

814

815

816

817

818

819

820

821

822

823

824

825

826

827

828

829

830

831

833

834

835

836

837

838

839

840

841

842

843

844

845

846

847

848

849

850

851

852

853

854

855

856

857

858

859

860

861

862

863

864

865

866

867

868

869

870

871

872

873

874

875

876

877

878

879

880

881

882

883

884

885

886

887

888

889

890

891

892

893

894

895

896

897

898

899

900

901

902

903

904

905

906

907

908

Mastering

VBA for Microsoft® Office 365®

2019 Edition

Richard Mansfield

Copyright © 2019 by John Wiley & Sons, Inc., Indianapolis, Indiana

Published simultaneously in Canada

ISBN: 978-1-119-57933-5

ISBN: 978-1-119-57941-0 (ebk.)

ISBN: 978-1-119-57938-0 (ebk.)

Manufactured in the United States of America

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 http://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: 2019942975

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 and Office 365 are registered trademarks 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.

I dedicate this book to my good friend Richard Biggs.

Acknowledgments

I'd like to thank all the good people at Sybex who contributed to this book. I am indebted to development editor Tom Cirtin, 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 Athiyappan Lalith Kumar, production editor, the book went smoothly through its final stages for author review, design, and assembly. My gratitude also goes to copyeditor Kim Cofer, who, via a very close read, polished this book in many ways. Evelyn Wellborn is also great at her job, and she flagged important issues during her proofreading.

About the Author

Mastering VBA for Microsoft Office 2019 is Richard Mansfield's 45th book. His 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.

About the Technical Editor

Russ Mullen is a veteran application developer and longtime tech editor (more than 125 titles). He has co-authored several software books. Written several large client server applications and proficient in several programming 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 job security, and it can do wonderful things for 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/masteringvbaoffice2019

If You Have Questions

I'm happy to hear from readers, so if you have any difficulty while using this book, please 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. But if you're embarrassed, just sign your email Connie and I'll think you're Connie.

Do though please limit questions to examples published in the book. I'm unable to provide answers to questions about your personal VBA code.

What Can I Do with VBA?

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

Here are some more examples. In Excel, you could 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 is faster, more accurate, more reliable, and far less expensive than any human worker. You can even 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. What's more, VBA can finish most jobs in less than a second.

But beyond automating actions you would otherwise perform manually, VBA also 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 create custom applications that run within the host application too. For example, you could build within PowerPoint a custom application that automatically creates presentations for you.

And VBA can communicate between applications, letting one application assist another. Word can't do much in the way of mathematical calculations on sets of data: that's Excel's specialty. So, you could 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.

You only have to learn VBA once. 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 Word. You'll need to learn the components particular to Word, 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.

Best of all, you can create some VBA programs without even writing a single line of code! You instead can 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 as usual, while the Recorder watches you work and translates all your actions into VBA programming code for you. The Recorder also acts as an assistant. 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. Makes sense, doesn't it?

For fun, search “save a document in C++” with Google, and you'll find lots of puzzling explanations attempting to accomplish this straightforward task in unfortunately unstraightforward ways, using often-puzzling diction. Pointless lingo really. The computer will print whether your language code uses the term Print “This” or as in C++:

#include <iostream>

std::cout << String << "\n";

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.

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 remember: Code that works in one Office 2019 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 2019 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,” contains 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 (execute in slow motion) 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 (another word for

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 macros. 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 the 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 users 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 macros 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 and shows you how to create it.

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 users 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 10 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 OneDrive.

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 the Find and Replace feature 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 27

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

Chapter 28

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

.

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. If you're not planning to work through the chapters sequentially, that's fine. But you might need to rely a bit more on the index to fill in any gaps in your understanding. This book of course can also be used purely as a reference work. In which case, the index becomes crucial.