Skip to main content

Mastering VBA for Microsoft Office 2016

Mastering VBA for Microsoft Office 2016

Richard Mansfield

ISBN: 978-1-119-22540-9

Feb 2016

984 pages

$32.99

Description

Enhance productivity in any Office application with zero programming experience

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.

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

The Difference Between Visual Basic and Visual Basic for Applications 4

Understanding Macro Basics 5

Recording a Macro 6

Displaying the Developer Tab on the Ribbon 7

Planning the Macro 7

Starting the Macro Recorder 8

Naming the Macro 11

Choosing How to Run a New Macro 16

Running a Macro 23

Recording a Sample Word Macro 24

Recording a Sample Excel Macro 27

Creating a Personal Macro Workbook 27

Recording the Macro 27

Specifying How to Trigger an Existing Macro 29

Assigning a Macro to a Quick Access Toolbar Button in Word 29

Assigning a Macro to a Shortcut Key Combination 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

Opening the Visual Basic Editor with a Macro Selected 34

Opening the Visual Basic Editor Directly 34

Navigating to a Macro 35

Using the Visual Basic Editor’s Main Windows 36

The Project Explorer 36

The Object Browser40

The Code Window 40

The Properties Window 45

The Immediate Window 48

Setting Properties for a Project 48

Customizing the Visual Basic Editor 51

Choosing Editor and View Preferences 51

Choosing and Laying Out the Editor Windows 59

Customizing the Toolbar and Menu Bar 59

Customizing the Toolbox 60

The Bottom Line 64

Chapter 3 • Editing Recorded Macros 67

Testing a Macro in the Visual Basic Editor 68

Stepping Through a Macro 69

Setting Breakpoints 71

Commenting Out Lines 71

Stepping Out of a Macro 72

Editing a Word Macro 73

Stepping Through the Transpose_Word_Right Macro 74

Running the Transpose_Word_Right Macro 75

Creating a Transpose_Word_Left Macro 75

Saving Your Work 76

Editing the Excel Macro 76

Unhiding the Personal Macro Workbook 77

Opening a Macro for Editing 77

Editing a Macro 78

Editing a PowerPoint Macro 80

Saving Your Work 85

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

Functions  113

Subprocedures 113

Statements 114

Keywords 117

Expressions 118

Operators 118

Variables 118

Constants 120

Arguments 120

Specifying Argument Names vsOmitting Argument Names 121

Including Parentheses Around the Argument List 122

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

Choosing Names for Variables 130

Declaring a Variable 131

Choosing the Scope and Lifetime of a Variable 134

Specifying the Data Type for a Variable 141

Working with Constants 147

Declaring Your Own Constants 148

Choosing the Scope or Lifetime for Your Constants 148

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

Performing a Linear Search Through an Array 163

Binary Searching an Array 168

The Bottom Line 173

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

What Is an Object? 175

The Benefi ts of OOP 175

Understanding Creatable Objects 177

Properties 177

Methods 178

Working with Collections 180

Working with an Object in a Collection 181

Adding an Object to a Collection 182

Finding the Objects You Need 182

Using the Macro Recorder to Add Code for the Objects You Need 182

Using the Object Browser 185

Using Help to Find the Object You Need 191

Using the Auto List Members Feature 193

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

Passing Arguments to a Function 207

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 Str Function to Convert a Number into a String 212

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 Date Diff 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

Returning the Current Path 238

The Bottom Line 238

Chapter 10 • Creating Your Own Functions 241

Components of a Function 242

Creating a Function 244

Starting a Function Manually 244

Starting a Function by Using the Add Procedure Dialog Box 244

Passing Arguments to a Function 245

Declaring the Data Types of Arguments 246

Specifying an Optional Argument 246

Controlling the Scope of a Function 247

Examples of Functions for Any VBA-Enabled Office Application 247

How Functions Return Information 248

Returning Text Data from a Function 249

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

If Blocks 266

If…f Then 266

If…Then…Else Statements 268

If…f Then…ElseIf…f Else Statements 270

Creating Loops with If and GoTo 275

Nesting If Blocks 276

Select Case Blocks 278

Syntax 279

Example 279

When Order Matters 281

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

For...Next Loops 285

For Each…Next Loops 293

Using an Exit For Statement 294

Using Do… Loops for Variable Numbers of Repetitions 295

Do While…Loop Loops 295

Do…Loop While Loops 299

Do Until…Loop Loops301

Do…Loop Until Loops 303

Using an Exit Do Statement 305

Is the Exit Do Statement Bad Practice? 305

While…Wend Loops 306

Nesting Loops 307

Avoiding Infi nite 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

The Pros and Cons of Message Boxes 319

Message-Box Syntax 319

Displaying a Simple Message Box 320

Displaying a Multiline Message Box 321

Choosing Buttons for a Message Box 322

Choosing an Icon for a Message Box 323

Setting a Default Button for a Message Box 324

Controlling the Modality of a Message Box 326

Specifying a Title for a Message Box 327

Title Bars Can Provide Useful Information 327

Adding a Help Button to a Message Box 328

Specifying a Help File for a Message Box 328

Using Some Arguments Without Others 329

Retrieving a Value from a Message Box 330

Input Boxes 331

Input-Box Syntax 332

Retrieving Input from an Input Box 333

Forms: When Message Boxes and Input Boxes Won’t Suffice 334

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

Designing a Dialog Box 340

Inserting a User Form 340

Renaming a User Form 343

Adding Controls to the User Form 345

Grouping Controls 349

Renaming Controls 350

Moving a Control 351

Changing the Caption on a Control 352

Key Properties of the Toolbox Controls 354

Working with Groups of Controls 372

Aligning Controls 375

Placing Controls 376

Adjusting the Tab Order of a Form 376

Linking a Form to a Procedure 378

Loading and Unloading a Form 379

Displaying and Hiding a Form 379

Setting a Default Command Button 380

Retrieving the User’s Choices from a Dialog Box 380

Returning a String from a Text Box 380

Returning a Value from an Option Button 381

Returning a Value from a Check Box 382

Returning a Value from a List Box 382

Returning a Value from a Combo Box 383

Examples of Connecting Forms to Procedures 384

Word Example: The Move-Paragraph Procedure 384

General Example: Opening a File from a List Box 395

Creating the Code for the User Form 397

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

Displaying a Built-In Dialog Box 401

Setting and Restoring Options in a Built-In Dialog Box 405

Which Button Did the User Choose in a Dialog Box? 405

Specifying a Time-Out for a Dialog Box 406

The Bottom Line 406

Chapter 15 • Creating Complex Forms 409

Creating and Working with Complex Dialog Boxes 410

Updating a Dialog Box to Reflect the User’s Choices 410

Revealing a Hidden Part of a Form 410

Tracking a Procedure in a Form 415

Using Multipage Dialog Boxes and TabStrip Controls 418

Creating a Modeless Dialog Box 429

Specifying a Form’s Location Onscreen 430

Using Events to Control Forms 430

Events Unique to the UserForm Object 434

Events That Apply to Both UserForms and Container Controls 438

Events That Apply to Many or Most Controls 443

Events That Apply Only to a Few Controls 456

The Bottom Line 457

Part 5 • Creating Effective Code 459

Chapter 16 • Building Modular Code and Using Classes 461

Creating Modular Code 461

What Is Modular Code? 461

Advantages of Using Modular Code 462

How to Approach Creating Modular Code 462

Arranging Your Code in Modules 463

Calling a Procedure 463

Making Logical Improvements to Your Code 465

Making Visual Improvements to Your Code 472

Creating and Using Classes 478

What Can You Do with Class Modules? 478

A Brief Overview of Classes 479

Planning Your Class 479

Creating a Class Module 480

Naming the Class 480

Setting the Instancing Property 480

Declaring Variables and Constants for the Class 481

Adding Properties to the Class 481

Adding Methods to a Class 486

Using Your Class 487

The Bottom Line 489

Chapter 17 • Debugging Your Code and Handling Errors 491

Principles of Debugging 491

The Different Types of Errors 493

Language Errors 493

Compile Errors 493

Runtime Errors 496

Program Logic Errors 497

VBA’s Debugging Tools 498

Break Mode 499

The Step Over and Step Out Commands 500

The Locals Window 501

The Watch Window 502

The Immediate Window 506

The Call Stack Dialog Box 508

Dealing with Infi nite Loops 508

Dealing with Runtime Errors 509

When Should You Write an Error Handler? 509

Trapping an Error 510

Disabling an Error Trap 512

Resuming After an Error 512

Getting a Description of an Error 516

Raising Your Own Errors 516

Suppressing Alerts 516

Handling User Interrupts in Word, Excel, and Project 517

Disabling User Input While a Procedure Is Running 517

Disabling User Input While Part of a Macro Is Running 518

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

Manipulating the Cursor 526

Displaying Information at the Beginning of a Procedure 527

Communicating with the User via a Message Box or Dialog Box at the End of a Procedure 528

Creating a Log File 528

Making Sure a Procedure Is Running Under Suitable Conditions 532

Cleaning Up After a Procedure 532

Undoing Changes the Procedure Has Made 532

Removing Scratch Files and Folders 533

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

What Is a Digital Certifi cate? 541

Getting a Digital Certifi cate 542

Choosing a Suitable Level of Security 551

Understanding the Security Threats Posed by VBA 551

Protecting Against Macro Viruses 552

Specifying a Suitable Security Setting 552

Additional Office Security Features 553

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

Creating a Document 565

Creating a Template 565

Saving a Document 566

Opening a Document 572

Closing a Document 576

Changing a Document’s Template 576

Printing a Document 576

Working with the ActiveDocument Object 578

Working with the Selection Object 579

Checking the Type of Selection 580

Checking the Story Type of the Selection 581

Getting Other Information About the Current Selection 582

Inserting Text at a Selection 586

Inserting a Paragraph in a Selection 587

Applying a Style 587

Extending a Selection 588

Collapsing a Selection 589

Creating and Using Ranges 589

Defining a Named Range 590

Redefining a Range 591

Using the Duplicate Property to Store or Copy Formatting 591

Manipulating Options 591

Making Sure Hyperlinks Require Ctrl+Clicking 591

Turning Off Overtype 592

Setting a Default File Path 592

Turning Off Track Changes 593

Accessing OneNote 593

The Bottom Line 594

Chapter 21 • Working with Widely Used Objects in Word 597

Using Find and Replace via VBA 597

Understanding the Syntax of the Execute Method 599

Putting Find and Replace to Work 602

Working with Headers, Footers, and Page Numbers 603

Understanding How VBA Implements Headers and Footers 604

Getting to a Header or Footer 604

Checking to See If a Header or Footer Exists 604

Linking to the Header or Footer in the Previous Section 605

Creating a Different First-Page Header 605

Creating Different Odd- and Even-Page Headers 605

Adding Page Numbers to Your Headers and Footers 606

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

Adding a Section to a Document 610

Changing the Page Setup 611

Opening a New Window Containing an Open Document 611

Closing All Windows Except the First for a Document 612

Splitting a Window 612

Displaying the Document Map for a Window 612

Scrolling a Window 613

Arranging Windows 613

Positioning and Sizing a Window 613

Making Sure an Item Is Displayed in the Window 614

Changing a Document’s View 614

Switching to Read Mode 615

Zooming the View to Display Multiple Pages 615

Working with Tables 615

Creating a Table 616

Selecting a Table 617

Converting Text to a Table 617

Ensuring That a Selection Is Within a Table 619

Finding Out Where a Selection Is Within a Table 619

Sorting a Table 620

Adding a Column to a Table 621

Deleting a Column from a Table 622

Setting the Width of a Column 622

Selecting a Column 623

Adding a Row to a Table 623

Deleting a Row from a Table 623

Setting the Height of One or More Rows 624

Selecting a Row 624

Inserting a Cell 624

Returning the Text in a Cell 625

Entering Text in a Cell 625

Deleting Cells 625

Selecting a Range of Cells 626

Converting a Table or Rows to Text 627

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

Creating a Workbook 633

Saving a Workbook 635

Accessing Cloud Storage 637

Opening a Workbook 638

Closing a Workbook 640

Sharing a Workbook 641

Protecting a Workbook 641

Working with the ActiveWorkbook Object 642

Working with Worksheets 642

Inserting a Worksheet 643

Deleting a Worksheet 643

Copying or Moving a Worksheet 644

Printing a Worksheet 645

Protecting a Worksheet 646

Working with the ActiveSheet Object 647

Working with the Active Cell or Selection647

Working with the Active Cell 648

Working with the User’s Selection 650

Working with Ranges 650

Working with a Range of Cells 650

Creating a Named Range 650

Deleting a Named Range 651

Working with a Named Range 652

Working with the Used Range 652

Working with SpecialCells 652

Entering a Formula in a Cell 653

Setting Options 654

Setting Options in the Application Object t 654

Setting Options in a Workbook 655

Accessing OneNote 655

The Bottom Line 656

Chapter 23 • Working with Widely Used Objects in Excel 659

Working with Charts 659

Creating a Chart 659

Specifying the Source Data for the Chart 661

Specifying a Chart Type 661

Working with Series in the Chart 661

Adding a Legend to the Chart 664

Adding a Chart Title 664

Working with a Chart Axis 665

Formatting Headers and Footers 665

Working with Windows Objects 666

Opening a New Window on a Workbook 666

Closing a Window 666

Activating a Window 666

Arranging and Resizing Windows 667

Zooming a Window and Setting Display Options 668

Working with Find and Replace 669

Searching with the Find Method 669

Continuing a Search with the FindNext and FindPrevious Methods 670

Replacing with the Replace Method 671

Searching for and Replacing Formatting 671

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

Creating a New Presentation Based on the Default Template 675

Creating a New Presentation Based on a Template 676

Opening an Existing Presentation 677

Opening a Presentation from the Cloud 677

Saving a Presentation 678

Closing a Presentation 680

Exporting a Presentation or Some Slides to Graphics 681

Printing a Presentation 681

Applying a Template to a Presentation, to a Slide, or to a Range of Slides 682

Working with the Active Presentation 683

Working with Windows and Views 683

Working with the Active Window 684

Opening a New Window on a Presentation 684

Closing a Window 684

Activating a Window 685

Arranging and Resizing Windows 685

Changing the View 686

Working with Panes 686

Working with Slides 686

Adding a Slide to a Presentation 687

Inserting Slides from an Existing Presentation 688

Finding a Slide by Its ID Number 688

Changing the Layout of an Existing Slide 689

Deleting an Existing Slide 689

Copying and Pasting a Slide 689

Duplicating a Slide 689

Moving a Slide 690

Accessing a Slide by Name 690

Working with a Range of Slides 690

Formatting a Slide 691

Setting a Transition for a Slide, a Range of Slides, or a Master 692

Working with Masters 693

Working with the Slide Master 693

Working with the Title Master 694

Working with the Handout Master 694

Working with the Notes Master 694

Deleting a Master 695

The Bottom Line695

Chapter 25 • Working with Shapes and Running Slide Shows 697

Working with Shapes 697

Adding Shapes to Slides 697

Deleting a Shape 703

Selecting All Shapes 703

Repositioning and Resizing a Shape 703

Copying Formatting from One Shape to Another 704

Working with Text in a Shape704

Animating a Shape or a Range of Shapes 709

Working with Headers and Footers 711

Returning the Header or Footer Object You Want 711

Displaying or Hiding a Header or Footer Object 711

Setting the Text in a Header or Footer 712

Setting the Format for Date and Time Headers and Footers 712

Setting Up and Running a Slide Show 713

Controlling the Show Type 713

Creating a Custom Show 714

Deleting a Custom Show 715

Starting a Slide Show 715

Changing the Size and Position of a Slide Show 715

Moving from Slide to Slide 716

Pausing the Show and Using White and Black Screens 716

Starting and Stopping Custom Shows 717

Exiting a Slide Show 717

The Bottom Line 717

Chapter 26 • Understanding the Outlook Object Model and Key Objects 719

Getting an Overview of the Outlook Object Model 719

Understanding Where Outlook Stores VBA Macros 720

Understanding Outlook’s Most Common Creatable Objects 720

Working with the Application Object 721

Introducing the NameSpace Object 722

Working with Inspectors and Explorers 722

Understanding Inspectors and Explorers 724

Creating Items 726

Quitting Outlook 727

Understanding General Methods for Working with Outlook Objects 727

Using the Display Method 727

Using the Close Method 728

Using the PrintOut Method 729

Using the Save Method 729

Using the SaveAs Method 730

Working with Messages 731

Creating a New Message 731

Working with the Contents of a Message 731

Adding an Attachment to a Message 732

Sending a Message 733

Working with Calendar Items 734

Creating a New Calendar Item 734

Working with the Contents of a Calendar Item 734

Working with Tasks and Task Requests 735

Creating a Task 735

Working with the Contents of a Task Item 735

Assigning a Task to a Colleague 736

Searching for Items 737

The Bottom Line 738

Chapter 27 • Working with Events in Outlook 741

Working with Application-Level Events 742

Using the Startup Event 744

Using the Quit Event 744

Using the ItemSend Event 745

Using the NewMail Event 746

Using the AdvancedSearchComplete and the AdvancedSearchStopped Events 746

Using the MAPILogonComplete Event 747

Using the Reminder Event 748

Using the OptionsPagesAdd Event 748

Working with Item-Level Events 748

Declaring an Object Variable and Initializing an Event 749

Understanding the Events That Apply to All Message Items 750

Understanding the Events That Apply to Explorers, Inspectors, and Views 752

Understanding the Events That Apply to Folders 756

Understanding the Events That Apply to Items and Results Objects 757

Understanding the Events That Apply to Reminders 757

Understanding the Events That Apply to Synchronization 758

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

Creating a Module in the VBA Editor 763

Creating a Function 763

Using the Macro Designer 763

Creating an Access-Style Macro to Run a Function 763

Translating an Access-Style Macro into a VBA Macro 765

Using an AutoExec Macro to Initialize an Access Session 767

Running a Subprocedure 767

Understanding the Option Compare Database Statement 768

Getting an Overview of the Access Object Model 768

Understanding Creatable Objects in Access 769

Opening and Closing Databases 770

Using the CurrentDb Method to Return the Current Database 770

Closing the Current Database and Opening a Different Database 770

Communicating Between Office Applications773

Opening Multiple Databases at Once 774

Closing a Database 776

Creating and Removing Workspaces 776

Working with the Screen Object 777

Using the DoCmd Object to Run Access Commands 778

Using the OpenForm Method to Open a Form 782

Using the PrintOut Method to Print an Object 783

Using the RunMacro Method to Run an Access-Style Macro 784

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

Adding a Reference to the Appropriate Object Library 788

Establishing a Connection to the Database 789

Opening a Recordset 789

Opening a Recordset Using ADO 789

Choosing How to Access the Data in an ADO Recordset 792

Accessing a Particular Record in a Recordset 800

Using the MoveFirst, MoveNext, MovePrevious, and MoveLast Methods 800

Using the Move Method to Move Past Multiple Records 801

Searching for a Record 802

Searching for a Record in an ADO Recordset 802

Searching for a Record in a DAO Recordset 804

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

Understanding Early and Late Binding 811

Creating an Object with the CreateObject Function 812

Returning an Object with the GetObject Function 813

Examples of Using Automation with the Offi ce Applications 813

Using the Shell Function to Run an Application 822

Using Data Objects to Store and Retrieve Information 824

Creating a Data Object 824

Storing Information in a Data Object 825

Returning Information from a Data Object 825

Assigning Information to the Clipboard 826

Finding Out Whether a Data Object Contains a Given Format 826

Communicating via DDE 827

Using DDEInitiate to Start a DDE Connection 827

Using DDERequest to Return Text from Another Application 828

Using DDEPoke to Send Text to Another Application 829

Using DDEExecute to Have One Application Execute a Command in Another 830

Using DDETerminate to Close a DDE Channel 830

Using DDETerminateAll to Close All Open DDE Channels 831

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

A Word of Warning 845

XML Terminology 845

Using Built-In Icons 846

Working with Excel and PowerPoint 846

Undoing Ribbon Modifications 847

Selecting the Scope of Your Ribbon Customization 847

Adding a New Group 848

Cautions About Customizing 848

Two Ways to Find the Correct idMso 850

Adding Callbacks 851

Adding Attributes 853

Using Built-In Icons and ScreenTips 853

Creating Your Own Icons 854

Using Menus and Lists 855

Adding Menus 855

Adding a DropDown List Control 857

Using a DialogBoxLauncher 859

Toggling with a Toggle-Button Control 860

Modifying the Ribbon in Access 861

Testing Your New Ribbon 863

Adding a Callback in Access 865

What to Look For If Things Go Wrong 866

Employ Error-Message Tools 866

Cure Common User-Interface Programming Problems 867

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

Sample Codes
Sample code for all the chapters
Download