Mastering Vba for Microsoft Office 2016

Mastering VBA for Microsoft Office 2016 helps you extend the capabilities of the entire Office suite using Visual Basic for Applications (VBA). Even if you have no programming experience , you'll be automating routine computing processes quickly using the simple, yet powerful VBA programming language. Clear, systematic tutorials walk beginners through the basics, while intermediate and advanced content guides more experienced users toward efficient solutions. This comprehensive guide starts at the beginning to get you acquainted with VBA so you can start recording macros right away. You'll then build upon that foundation to utilize the full capabilities of the language as you use loops and functions, message boxes, input boxes, and dialog boxes to design your own Office automation program. Add-ins, embedded macros, content controls, and more give you advanced tools to enhance productivity, and all instruction is backed by real-world practice projects in Word, Excel, Outlook, and PowerPoint.

Productivity is the name of the game, and automating certain computing tasks is an easy solution with significant impact for any business. This book shows you how, with step-by-step guidance and expert insight.

• Expand Office 2016 functionality with macros
• Learn how to work with VBA and the entire Office suite
• Create effective code, even with no programing experience
• Understand ActiveX, XML-based files, the developer tab, and more

VBA is designed to be understandable and accessible to beginners, but powerful enough to create specialized business applications. If you're ready to begin exploring the possibilities, Mastering VBA for Microsoft Office 2016 gets you started right away.

Richard Mansfield is a best-selling author and a widely recognized expert on computer programming. He has authored or co-authored 46 books, including Programming: A Beginner's Guide, and the best-sellers Machine Language for Beginners, and The Visual Guide to Visual Basic. Overall, his books have sold more than half a million copies worldwide and have been translated into 12 languages.

Introduction xxxi

Part 1 Recording Macros and Getting Started with VBA 1

Chapter 1 Recording and Running Macros in the Office Applications 3

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

Understanding Macro Basics 5

Recording a Macro 6

Running a Macro 23

Recording a Sample Word Macro 24

Recording a Sample Excel Macro 27

Specifying How to Trigger an Existing Macro 29

Deleting a Macro 29

The Bottom Line 31

Chapter 2 Getting Started with the Visual Basic Editor .33

Opening the Visual Basic Editor 33

Using the Visual Basic Editor’s Main Windows 36

Setting Properties for a Project 48

Customizing the Visual Basic Editor 51

The Bottom Line 64

Chapter 3 Editing Recorded Macros .67

Testing a Macro in the Visual Basic Editor 68

Editing a Word Macro 73

Editing the Excel Macro 76

Editing a PowerPoint Macro 80

The Bottom Line 87

Chapter 4 Creating Code from Scratch in the Visual Basic Editor .89

Setting Up the Visual Basic Editor to Create Macros 89

Creating a Procedure for Word 91

Creating a Macro for Excel 96

Creating a Procedure for PowerPoint 101

Creating a Procedure for Access 106

The Bottom Line 107

Part 2 Learning How to Work with VBA 109

Chapter 5 Understanding the Essentials of VBA Syntax 111

Getting Ready 111

Procedures 112

Statements 114

Keywords 117

Expressions 118

Operators 118

Variables 118

Constants 120

Arguments 120

Objects 123

Collections 123

Properties 123

Methods 124

Events 124

The Bottom Line 126

Chapter 6 Working with Variables, Constants, and Enumerations 129

Working with Variables 130

Working with Constants 147

Working with Enumerations 149

The Bottom Line 150

Chapter 7 Using Array Variables 151

What Is an Array? 151

Declaring an Array 153

Storing Values in an Array 155

Multidimensional Arrays 156

Declaring a Dynamic Array 157

Redimensioning an Array 157

Returning Information from an Array 158

Erasing an Array 158

Determining Whether a Variable Is an Array 158

Finding the Bounds of an Array 158

Sorting an Array 159

Searching an Array 163

The Bottom Line 173

Chapter 8 Finding the Objects, Methods, and Properties You Need .175

What Is an Object? 175

Working with Collections 180

Finding the Objects You Need 182

Using Object Variables to Represent Objects 194

Team Programming and OOP 197

The Bottom Line 199

Part 3 Making Decisions and Using Loops and Functions 201

Chapter 9 Using Built-In Functions 203

What Is a Function? 203

Using Functions 205

Using Functions to Convert Data 208

Using the Asc Function to Return a Character Code 210

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

Using the Format Function to Format an Expression 213

Using the Chr Function and Constants to Enter Special Characters in a String 218

Using Functions to Manipulate Strings 219

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

Using InStr and InStrRev to Find a String within another String 224

Using LTrim, RTrim, and Trim to Remove Spaces from a String 227

Using Len to Check the Length of a String 228

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

Using the StrComp Function to Compare Apples to Apples 231

Using VBA’s Mathematical Functions 231

Using VBA’s Date and Time Functions 232

Using the DatePart Function to Parse Dates 234

Calculating Time Intervals Using the DateDiff Function 235

Using the DateAdd Function to Add or Subtract Time from a Date 236

Using File-Management Functions 236

Checking Whether a File Exists Using the Dir Function 236

The Bottom Line 238

Chapter 10 Creating Your Own Functions .241

Components of a Function 242

Creating a Function 244

Examples of Functions for Any VBA-Enabled Office Application 247

Creating a Function for Word 251

Creating a Function for Excel 253

Creating a Function for PowerPoint 255

Creating a Function for Access 257

The Bottom Line 258

Chapter 11 Making Decisions in Your Code 261

How Do You Compare Things in VBA? 262

Testing Multiple Conditions by Using Logical Operators 263

Select Case Blocks 278

The Bottom Line 282

Chapter 12 Using Loops to Repeat Actions 283

When Should You Use a Loop? 283

Understanding the Basics of Loops 284

Using For… Loops for Fixed Repetitions 285

Using Do… Loops for Variable Numbers of Repetitions 295

While…Wend Loops 306

Nesting Loops 307

Avoiding  Loops 310

The Bottom Line 310

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

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

Opening a Macro 316

Displaying Status-Bar Messages in Word and Excel 317

Message Boxes 319

Input Boxes 331

Forms: When Message Boxes and Input Boxes

The Bottom Line 334

Chapter 14 Creating Simple Custom Dialog Boxes 337

When Should You Use a Custom Dialog Box? 337

Creating a Custom Dialog Box 338

Working with Groups of Controls 372

Linking a Form to a Procedure 378

Retrieving the User’s Choices from a Dialog Box 380

Examples of Connecting Forms to Procedures 384

Using an Application’s Built-In Dialog Boxes from VBA 400

The Bottom Line 406

Chapter 15 Creating Complex Forms 409

Creating and Working with Complex Dialog Boxes 410

Using Events to Control Forms 430

The Bottom Line 457

Part 5 Creating Effective Code 459

Chapter 16 Building Modular Code and Using Classes 461

Creating Modular Code 461

Creating and Using Classes 478

The Bottom Line 489

Chapter 17 Debugging Your Code and Handling Errors 491

Principles of Debugging 491

The Different Types of Errors 493

VBA’s Debugging Tools 498

Dealing with  Loops 508

Dealing with Runtime Errors 509

Suppressing Alerts 516

Handling User Interrupts in Word, Excel, and Project 517

Documenting Your Code 518

The Bottom Line 520

Chapter 18 Building Well-Behaved Code 521

What Is a Well-Behaved Procedure? 521

Retaining or Restoring the User Environment 522

Leaving the User in the Best Position to Continue Working 523

Keeping the User Informed During the Procedure 524

Making Sure a Procedure Is Running Under Suitable Conditions 532

Cleaning Up After a Procedure 532

The Bottom Line 534

Chapter 19 Exploring VBA’s Security Features 537

Understanding How VBA Implements Security 537

Signing Your Macro Projects with Digital Signatures 541

Choosing a Suitable Level of Security 551

Locking Your Code 556

The Bottom Line 557

Part 6 Programming the Office Applications 559

Chapter 20 Understanding the Word Object Model and Key Objects 561

Examining the Word Object Model 561

Working with the Documents Collection and the Document Object 564

Printing a Document 576

Working with the ActiveDocument Object 578

Working with the Selection Object 579

Creating and Using Ranges 589

Manipulating Options 591

The Bottom Line 594

Chapter 21 Working with Widely Used Objects in Word 597

Using Find and Replace via VBA 597

Working with Headers, Footers, and Page Numbers 603

Working with Sections, Page Setup, Windows, and Views 610

Working with Tables 615

The Bottom Line 628

Chapter 22 Understanding the Excel Object Model and Key Objects 631

Getting an Overview of the Excel Object Model 631

Understanding Excel’s Creatable Objects 632

Managing Workbooks 633

Working with Worksheets 642

Working with the Active Cell or Selection 647

Working with Ranges 650

Setting Options 654

The Bottom Line 656

Chapter 23 Working with Widely Used Objects in Excel 659

Working with Charts 659

Working with Windows Objects 666

Working with Find and Replace 669

Adding Shapes 672

The Bottom Line 672

Chapter 24 Understanding the PowerPoint Object Model and Key Objects 673

Getting an Overview of the PowerPoint Object Model 673

Understanding PowerPoint’s Creatable Objects 674

Working with Presentations 675

Working with Windows and Views 683

Working with Slides 686

Working with Masters 693

The Bottom Line 695

Chapter 25 Working with Shapes and Running Slide Shows 697

Working with Shapes 697

Working with Headers and Footers 711

Setting Up and Running a Slide Show 713

The Bottom Line 717

Chapter 26 Understanding the Outlook Object

Model and Key Objects 719

Getting an Overview of the Outlook Object Model 719

Working with the Application Object 721

Understanding General Methods for Working with Outlook Objects 727

Working with Messages 731

Working with Calendar Items 734

Working with Tasks and Task Requests 735

Searching for Items 737

The Bottom Line 738

Chapter 27 Working with Events in Outlook .741

Working with Application-Level Events 742

Working with Item-Level Events 748

Understanding Quick Steps 758

The Bottom Line 759

Chapter 28 Understanding the Access Object Model and Key Objects 761

Getting Started with VBA in Access 761

Getting an Overview of the Access Object Model 768

Understanding Creatable Objects in Access 769

Opening and Closing Databases 770

Working with the Screen Object 777

The Bottom Line 784

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

Understanding How to Proceed 787

Preparing to Manage the Data in a Database 788

Opening a Recordset 789

Accessing a Particular Record in a Recordset 800

Searching for a Record 802

Returning the Fields in a Record 805

Editing a Record 805

Inserting and Deleting Records 805

Closing a Recordset 806

Saving a Recordset to the Cloud 806

The Bottom Line 808

Chapter 30 Accessing One Application from another Application 809

Understanding the Tools Used to Communicate Between Applications 809

Using Automation to Transfer Information 810

Using the Shell Function to Run an Application 822

Using Data Objects to Store and Retrieve Information 824

Communicating via DDE 827

Communicating via SendKeys 831

Going Beyond VBA 835

The Bottom Line 836

Chapter 31 Programming the Office 2016 Ribbon 837

What Is XML? 838

Hiding the Clipboard Group on the Word Ribbon 839

Working with Excel and PowerPoint 846

Undoing Ribbon Modifications 847

Selecting the Scope of Your Ribbon Customization 847

Adding a New Group 848

Adding Callbacks 851

Adding Attributes 853

Using Menus and Lists 855

Toggling with a Toggle-Button Control 860

Modifying the Ribbon in Access 861

Adding a Callback in Access 865

What to Look For If Things Go Wrong 866

Where to Go from Here 869

The Bottom Line 870

Appendix The Bottom Line 871

Chapter 1: Recording and Running Macros in the Office Applications 871

Chapter 2: Getting Started with the Visual Basic Editor 872

Chapter 3: Editing Recorded Macros 873

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

Chapter 5: Understanding the Essentials of VBA Syntax 876

Chapter 6: Working with Variables, Constants, and Enumerations 878

Chapter 7: Using Array Variables 880

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

Chapter 9: Using Built-in Functions 882

Chapter 10: Creating Your Own Functions 883

Chapter 11: Making Decisions in Your Code 885

Chapter 12: Using Loops to Repeat Actions 886

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

Chapter 14: Creating Simple Custom Dialog Boxes 889

Chapter 15: Creating Complex Forms 892

Chapter 16: Building Modular Code and Using Classes 894

Chapter 17: Debugging Your Code and Handling Errors 895

Chapter 18: Building Well-Behaved Code 897

Chapter 19: Exploring VBA’s Security Features 898

Chapter 20: Understanding the Word Object Model and Key Objects 900

Chapter 21: Working with Widely Used Objects in Word 901

Chapter 22: Understanding the Excel Object Model and Key Objects 902

Chapter 23: Working with Widely Used Objects in Excel 903

Chapter 24: Understanding the PowerPoint Object Model and Key Objects 904

Chapter 25: Working with Shapes and Running Slide Shows 905

Chapter 26: Understanding the Outlook Object Model and Key Objects 906

Chapter 27: Working with Events in Outlook 906

Chapter 28: Understanding the Access Object Model and Key Objects 907

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

Chapter 30: Accessing One Application from another Application 910

Chapter 31: Programming the Office 2016 Ribbon 911

Index 913

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.