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 Power Point.
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 Active X, 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.
Table of Content
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 Power Point 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 Power Point 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 In Str and In Str Rev 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 Str Conv, LCase, and UCase to Change the Case of a String 229
Using the Str Comp Function to Compare Apples to Apples 231
Using VBA’s Mathematical Functions 231
Using VBA’s Date and Time Functions 232
Using the Date Part Function to Parse Dates 234
Calculating Time Intervals Using the Date Diff Function 235
Using the Date Add 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 Power Point 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 Active Document 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 Power Point Object Model and Key Objects 673
Getting an Overview of the Power Point Object Model 673
Understanding Power Point’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 Send Keys 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 Power Point 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 Power Point 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
About the author
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.
More ebooks from the same author(s) / Editor
2,799 Ebooks in this category
