Mastering VBA for Microsoft Office 2016

Mastering VBA for Microsoft Office 2016

by Richard Mansfield
Released February 2016
Publisher(s): Sybex
ISBN: 9781119225386

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book 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.

Table of contents

  1. Title Page
  2. Copyright
  3. Dedication
  4. Acknowledgments
  5. About the Author
  6. Introduction
    1. Where to Get This Book's Example Code
    2. If You Have Questions
    3. What Can I Do with VBA?
    4. What's in This Book?
    5. How Should I Use This Book?
    6. Is This Book Suitable for Me?
    7. Conventions Used in This Book
    8. The Mastering Series
    9. For More Information
  7. Part 1: Recording Macros and Getting Started with VBA
    1. Chapter 1: Recording and Running Macros in the Office Applications
      1. What Is VBA and What Can You Do with It?
      2. Understanding Macro Basics
      3. Recording a Macro
      4. Running a Macro
      5. Recording a Sample Word Macro
      6. Recording a Sample Excel Macro
      7. Specifying How to Trigger an Existing Macro
      8. Deleting a Macro
      9. The Bottom Line
    2. Chapter 2: Getting Started with the Visual Basic Editor
      1. Opening the Visual Basic Editor
      2. Using the Visual Basic Editor's Main Windows
      3. Setting Properties for a Project
      4. Customizing the Visual Basic Editor
      5. The Bottom Line
    3. Chapter 3: Editing Recorded Macros
      1. Testing a Macro in the Visual Basic Editor
      2. Editing a Word Macro
      3. Editing the Excel Macro
      4. Editing a PowerPoint Macro
      5. The Bottom Line
    4. Chapter 4: Creating Code from Scratch in the Visual Basic Editor
      1. Setting Up the Visual Basic Editor to Create Macros
      2. Creating a Procedure for Word
      3. Creating a Macro for Excel
      4. Creating a Procedure for PowerPoint
      5. Creating a Procedure for Access
      6. The Bottom Line
  8. Part 2: Learning How to Work with VBA
    1. Chapter 5: Understanding the Essentials of VBA Syntax
      1. Getting Ready
      2. Procedures
      3. Statements
      4. Keywords
      5. Expressions
      6. Operators
      7. Variables
      8. Constants
      9. Arguments
      10. Objects
      11. Collections
      12. Properties
      13. Methods
      14. Events
      15. The Bottom Line
    2. Chapter 6: Working with Variables, Constants, and Enumerations
      1. Working with Variables
      2. Working with Constants
      3. Working with Enumerations
      4. The Bottom Line
    3. Chapter 7: Using Array Variables
      1. What Is an Array?
      2. Declaring an Array
      3. Storing Values in an Array
      4. Multidimensional Arrays
      5. Declaring a Dynamic Array
      6. Redimensioning an Array
      7. Returning Information from an Array
      8. Erasing an Array
      9. Determining Whether a Variable Is an Array
      10. Finding the Bounds of an Array
      11. Sorting an Array
      12. Searching an Array
      13. The Bottom Line
    4. Chapter 8: Finding the Objects, Methods, and Properties You Need
      1. What Is an Object?
      2. Working with Collections
      3. Finding the Objects You Need
      4. Using Object Variables to Represent Objects
      5. Team Programming and OOP
      6. The Bottom Line
  9. Part 3: Making Decisions and Using Loops and Functions
    1. Chapter 9: Using Built-In Functions
      1. What Is a Function?
      2. Using Functions
      3. Using Functions to Convert Data
      4. Using the Asc Function to Return a Character Code
      5. Using the Val Function to Extract a Number from the Start of a String
      6. Using the Format Function to Format an Expression
      7. Using the Chr Function and Constants to Enter Special Characters in a String
      8. Using Functions to Manipulate Strings
      9. Using the Left, Right, and Mid Functions to Return Part of a String
      10. Using InStr and InStrRev to Find a String Within Another String
      11. Using LTrim, RTrim, and Trim to Remove Spaces from a String
      12. Using Len to Check the Length of a String
      13. Using StrConv, LCase, and UCase to Change the Case of a String
      14. Using the StrComp Function to Compare Apples to Apples
      15. Using VBA's Mathematical Functions[[Page_232]]
      16. Using VBA's Date and[[Page_233]] Time Functions
      17. Using the DatePart Function to Parse Dates
      18. Calculating Time Intervals Using the DateDiff Function
      19. Using the DateAdd Function to Add or Subtract Time from a Date
      20. Using File-Management Functions
      21. Checking Whether a File Exists Using the Dir Function
      22. The Bottom Line
    2. Chapter 10: Creating Your Own Functions
      1. Components of a Function
      2. Creating a Function
      3. Examples of Functions for Any VBA-Enabled Office Application
      4. Creating a Function for Word
      5. Creating a Function for Excel
      6. Creating a Function for PowerPoint
      7. Creating a Function for Access
      8. The Bottom Line
    3. Chapter 11: Making Decisions in Your Code
      1. How Do You Compare Things in VBA?
      2. Testing Multiple Conditions by Using Logical Operators
      3. Select Case Blocks
      4. The Bottom Line
    4. Chapter 12: Using Loops to Repeat Actions
      1. When Should You Use a Loop?
      2. Understanding the Basics of Loops
      3. Using For…Loops for Fixed Repetitions
      4. Using Do…Loops for Variable Numbers of Repetitions
      5. While…Wend Loops
      6. Nesting Loops
      7. Avoiding Infinite Loops
      8. The Bottom Line
  10. Part 4: Using Message Boxes, Input Boxes, and Dialog Boxes
    1. Chapter 13: Getting User Input with Message Boxes and Input Boxes
      1. Opening a Macro
      2. Displaying Status-Bar Messages in Word and Excel
      3. Message Boxes
      4. Input Boxes
      5. Forms: When Message Boxes and Input Boxes Won't Suffice
      6. The Bottom Line
    2. Chapter 14: Creating Simple Custom Dialog Boxes
      1. When Should You Use a Custom Dialog Box?
      2. Creating a Custom Dialog Box
      3. Working with Groups of Controls
      4. Linking a Form to a Procedure
      5. Retrieving the User's Choices from a Dialog Box
      6. Examples of Connecting Forms to Procedures
      7. Using an Application's Built-In Dialog Boxes from VBA
      8. The Bottom Line
    3. Chapter 15: Creating Complex Forms
      1. Creating and Working with Complex Dialog Boxes
      2. Using Events to Control Forms
      3. The Bottom Line
  11. Part 5: Creating Effective Code
    1. Chapter 16: Building Modular Code and Using Classes
      1. Creating Modular Code
      2. Creating and Using Classes
      3. The Bottom Line
    2. Chapter 17: Debugging Your Code and Handling Errors
      1. Principles of Debugging
      2. The Different Types of Errors
      3. VBA's Debugging Tools
      4. Dealing with Infinite Loops
      5. Dealing with Runtime Errors
      6. Suppressing Alerts
      7. Handling User Interrupts in Word, Excel, and Project
      8. Documenting Your Code
      9. The Bottom Line
    3. Chapter 18: Building Well-Behaved Code
      1. What Is a Well-Behaved Procedure?
      2. Retaining or Restoring the User Environment
      3. Leaving the User in the Best Position to Continue Working
      4. Keeping the User Informed During the Procedure
      5. Making Sure a Procedure Is Running Under Suitable Conditions
      6. Cleaning Up After a Procedure
      7. The Bottom Line
    4. Chapter 19: Exploring VBA's Security Features
      1. Understanding How VBA Implements Security
      2. Signing Your Macro Projects with Digital Signatures
      3. Choosing a Suitable Level of Security
      4. Locking Your Code
      5. The Bottom Line
  12. Part 6: Programming the Office Applications
    1. Chapter 20: Understanding the Word Object Model and Key Objects
      1. Examining the Word Object Model
      2. Working with the Documents Collection and the Document Object
      3. Saving a Document
      4. Opening a Document
      5. Closing a Document
      6. Printing a Document
      7. Working with the ActiveDocument Object
      8. Working with the Selection Object
      9. Creating and Using Ranges
      10. Manipulating Options
      11. The Bottom Line
    2. Chapter 21: Working with Widely Used Objects in Word
      1. Using Find and Replace via VBA
      2. Working with Headers, Footers, and Page Numbers
      3. Working with Sections, Page Setup, Windows, and Views
      4. Working with Tables
      5. The Bottom Line
    3. Chapter 22: Understanding the Excel Object Model and Key Objects
      1. Getting an Overview of the Excel Object Model
      2. Understanding Excel's Creatable Objects
      3. Managing Workbooks
      4. Working with Worksheets
      5. Working with the Active Cell or Selection
      6. Working with Ranges
      7. Setting Options
      8. The Bottom Line
    4. Chapter 23: Working with Widely Used Objects in Excel
      1. Working with Charts
      2. Working with Windows Objects
      3. Working with Find and Replace
      4. Adding Shapes
      5. The Bottom Line
    5. Chapter 24: Understanding the PowerPoint Object Model and Key Objects
      1. Getting an Overview of the PowerPoint Object Model
      2. Understanding PowerPoint's Creatable Objects
      3. Working with Presentations
      4. Working with Windows and Views
      5. Working with Slides
      6. Working with Masters
      7. The Bottom Line
    6. Chapter 25: Working with Shapes and Running Slide Shows
      1. Working with Shapes
      2. Working with Headers and Footers
      3. Setting Up and Running a Slide Show
      4. The Bottom Line
    7. Chapter 26: Understanding the Outlook Object Model and Key Objects
      1. Getting an Overview of the Outlook Object Model
      2. Working with the Application Object
      3. Understanding General Methods for Working with Outlook Objects
      4. Working with Messages
      5. Working with Calendar Items
      6. Working with Tasks and Task Requests
      7. Searching for Items
      8. The Bottom Line
    8. Chapter 27: Working with Events in Outlook
      1. Working with Application-Level Events
      2. Working with Item-Level Events
      3. Understanding Quick Steps
      4. The Bottom Line
    9. Chapter 28: Understanding the Access Object Model and Key Objects
      1. Getting Started with VBA in Access
      2. Getting an Overview of the Access Object Model
      3. Understanding Creatable Objects in Access
      4. Opening and Closing Databases
      5. Working with the Screen Object
      6. Using the DoCmd Object to Run Access Commands
      7. The Bottom Line
    10. Chapter 29: Manipulating the Data in an Access Database via VBA
      1. Understanding How to Proceed
      2. Preparing to Manage the Data in a Database
      3. Opening a Recordset
      4. Accessing a Particular Record in a Recordset
      5. Searching for a Record
      6. Returning the Fields in a Record
      7. Editing a Record
      8. Inserting and Deleting Records
      9. Closing a Recordset
      10. Saving a Recordset to the Cloud
      11. The Bottom Line
    11. Chapter 30: Accessing One Application from Another Application
      1. Understanding the Tools Used to Communicate Between Applications
      2. Using Automation to Transfer Information
      3. Using the Shell Function to Run an Application
      4. Using Data Objects to Store and Retrieve Information
      5. Communicating via DDE
      6. Communicating via SendKeys
      7. Going Beyond VBA
      8. The Bottom Line
    12. Chapter 31: Programming the Office 2016 Ribbon
      1. What Is XML?
      2. Hiding the Clipboard Group on the Word Ribbon
      3. Working with Excel and PowerPoint
      4. Undoing Ribbon Modifications
      5. Selecting the Scope of Your Ribbon Customization
      6. Adding a New Group
      7. Adding Callbacks
      8. Adding Attributes
      9. Using Menus and Lists
      10. Toggling with a Toggle-Button Control
      11. Modifying the Ribbon in Access
      12. Adding a Callback in Access
      13. What to Look For If Things Go Wrong
      14. Where to Go from Here
      15. The Bottom Line
  13. Appendix: The Bottom Line
    1. Chapter 1: Recording and Running Macros in the Office Applications
    2. Chapter 2: Getting Started with the Visual Basic Editor[[Page_878]]
    3. Chapter 3: Editing Recorded Macros
    4. Chapter 4: Creating Code from Scratch in the Visual Basic Editor
    5. Chapter 5: Understanding the Essentials of VBA Syntax
    6. Chapter 6: Working with Variables, Constants, and Enumerations
    7. Chapter 7: Using Array Variables
    8. Chapter 8: Finding the Objects, Methods, and Properties You Need
    9. Chapter 9: Using Built-In Functions
    10. Chapter 10: Creating Your Own Functions
    11. Chapter 11: Making Decisions in Your Code
    12. Chapter 12: Using Loops to Repeat Actions
    13. Chapter 13: Getting User Input with Message Boxes and Input Boxes
    14. Chapter 14: Creating Simple Custom Dialog Boxes
    15. Chapter 15: Creating Complex Forms
    16. Chapter 16: Building Modular Code and Using Classes
    17. Chapter 17: Debugging Your Code and Handling Errors
    18. Chapter 18: Building Well-Behaved Code
    19. Chapter 19: Exploring VBA's Security Features
    20. Chapter 20: Understanding the Word Object Model and Key Objects
    21. Chapter 21: Working with Widely Used Objects in Word
    22. Chapter 22: Understanding the Excel Object Model and Key Objects
    23. Chapter 23: Working with Widely Used Objects in Excel
    24. Chapter 24: Understanding the PowerPoint Object Model and Key Objects
    25. Chapter 25: Working with Shapes and Running Slide Shows
    26. Chapter 26: Understanding the Outlook Object Model and Key Objects
    27. Chapter 27: Working with Events in Outlook
    28. Chapter 28: Understanding the Access Object Model and Key Objects
    29. Chapter 29: Manipulating the Data in an Access Database via VBA
    30. Chapter 30: Accessing One Application from Another Application
    31. Chapter 31: Programming the Office 2016 Ribbon
  14. End User License Agreement

Product information

  • Title: Mastering VBA for Microsoft Office 2016
  • Author(s): Richard Mansfield
  • Release date: February 2016
  • Publisher(s): Sybex
  • ISBN: 9781119225386