book

Mastering VBA for Microsoft Office 2016

Name: Mastering VBA for Microsoft Office 2016
Author: Richard Mansfield
ISBN: 9781119225386

by Richard Mansfield

February 2016

Beginner

984 pages

27h 54m

English

Sybex

Read now

Unlock full access

Title Page
Copyright
Dedication
Acknowledgments
About the Author
Introduction
Where to Get This Book's Example CodeIf You Have QuestionsWhat 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 BookThe Mastering SeriesFor 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 BasicsRecording a MacroRunning a MacroRecording a Sample Word MacroRecording a Sample Excel MacroSpecifying How to Trigger an Existing MacroDeleting a MacroThe Bottom Line
Chapter 2: Getting Started with the Visual Basic Editor
Opening the Visual Basic EditorUsing the Visual Basic Editor's Main WindowsSetting Properties for a ProjectCustomizing the Visual Basic EditorThe Bottom Line
Chapter 3: Editing Recorded Macros
Testing a Macro in the Visual Basic EditorEditing a Word MacroEditing the Excel MacroEditing a PowerPoint MacroThe Bottom Line

Chapter 4: Creating Code from Scratch in the Visual Basic Editor
Setting Up the Visual Basic Editor to Create MacrosCreating a Procedure for WordCreating a Macro for ExcelCreating a Procedure for PowerPointCreating a Procedure for AccessThe Bottom Line
Part 2: Learning How to Work with VBA
Chapter 5: Understanding the Essentials of VBA Syntax
Getting ReadyProceduresStatementsKeywordsExpressionsOperatorsVariablesConstantsArgumentsObjectsCollectionsPropertiesMethodsEventsThe Bottom Line
Chapter 6: Working with Variables, Constants, and Enumerations
Working with VariablesWorking with ConstantsWorking with EnumerationsThe Bottom Line
Chapter 7: Using Array Variables
What Is an Array?Declaring an ArrayStoring Values in an ArrayMultidimensional ArraysDeclaring a Dynamic ArrayRedimensioning an ArrayReturning Information from an ArrayErasing an ArrayDetermining Whether a Variable Is an ArrayFinding the Bounds of an ArraySorting an ArraySearching an ArrayThe Bottom Line
Chapter 8: Finding the Objects, Methods, and Properties You Need
What Is an Object?Working with CollectionsFinding the Objects You NeedUsing Object Variables to Represent ObjectsTeam Programming and OOPThe Bottom Line
Part 3: Making Decisions and Using Loops and Functions
Chapter 9: Using Built-In Functions
What Is a Function?Using FunctionsUsing Functions to Convert DataUsing the Asc Function to Return a Character CodeUsing the Val Function to Extract a Number from the Start of a StringUsing the Format Function to Format an ExpressionUsing the Chr Function and Constants to Enter Special Characters in a StringUsing Functions to Manipulate StringsUsing the Left, Right, and Mid Functions to Return Part of a StringUsing InStr and InStrRev to Find a String Within Another StringUsing LTrim, RTrim, and Trim to Remove Spaces from a StringUsing Len to Check the Length of a StringUsing StrConv, LCase, and UCase to Change the Case of a StringUsing the StrComp Function to Compare Apples to ApplesUsing VBA's Mathematical Functions[[Page_232]]Using VBA's Date and[[Page_233]] Time FunctionsUsing the DatePart Function to Parse DatesCalculating Time Intervals Using the DateDiff FunctionUsing the DateAdd Function to Add or Subtract Time from a DateUsing File-Management FunctionsChecking Whether a File Exists Using the Dir FunctionThe Bottom Line
Chapter 10: Creating Your Own Functions
Components of a FunctionCreating a FunctionExamples of Functions for Any VBA-Enabled Office ApplicationCreating a Function for WordCreating a Function for ExcelCreating a Function for PowerPointCreating a Function for AccessThe Bottom Line
Chapter 11: Making Decisions in Your Code
How Do You Compare Things in VBA?Testing Multiple Conditions by Using Logical OperatorsSelect Case BlocksThe Bottom Line
Chapter 12: Using Loops to Repeat Actions
When Should You Use a Loop?Understanding the Basics of LoopsUsing For…Loops for Fixed RepetitionsUsing Do…Loops for Variable Numbers of RepetitionsWhile…Wend LoopsNesting LoopsAvoiding Infinite LoopsThe 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 MacroDisplaying Status-Bar Messages in Word and ExcelMessage BoxesInput BoxesForms: When Message Boxes and Input Boxes Won't SufficeThe Bottom Line
Chapter 14: Creating Simple Custom Dialog Boxes
When Should You Use a Custom Dialog Box?Creating a Custom Dialog BoxWorking with Groups of ControlsLinking a Form to a ProcedureRetrieving the User's Choices from a Dialog BoxExamples of Connecting Forms to ProceduresUsing an Application's Built-In Dialog Boxes from VBAThe Bottom Line
Chapter 15: Creating Complex Forms
Creating and Working with Complex Dialog BoxesUsing Events to Control FormsThe Bottom Line
Part 5: Creating Effective Code
Chapter 16: Building Modular Code and Using Classes
Creating Modular CodeCreating and Using ClassesThe Bottom Line
Chapter 17: Debugging Your Code and Handling Errors
Principles of DebuggingThe Different Types of ErrorsVBA's Debugging ToolsDealing with Infinite LoopsDealing with Runtime ErrorsSuppressing AlertsHandling User Interrupts in Word, Excel, and ProjectDocumenting Your CodeThe Bottom Line
Chapter 18: Building Well-Behaved Code
What Is a Well-Behaved Procedure?Retaining or Restoring the User EnvironmentLeaving the User in the Best Position to Continue WorkingKeeping the User Informed During the ProcedureMaking Sure a Procedure Is Running Under Suitable ConditionsCleaning Up After a ProcedureThe Bottom Line
Chapter 19: Exploring VBA's Security Features
Understanding How VBA Implements SecuritySigning Your Macro Projects with Digital SignaturesChoosing a Suitable Level of SecurityLocking Your CodeThe Bottom Line
Part 6: Programming the Office Applications
Chapter 20: Understanding the Word Object Model and Key Objects
Examining the Word Object ModelWorking with the Documents Collection and the Document ObjectSaving a DocumentOpening a DocumentClosing a DocumentPrinting a DocumentWorking with the ActiveDocument ObjectWorking with the Selection ObjectCreating and Using RangesManipulating OptionsThe Bottom Line
Chapter 21: Working with Widely Used Objects in Word
Using Find and Replace via VBAWorking with Headers, Footers, and Page NumbersWorking with Sections, Page Setup, Windows, and ViewsWorking with TablesThe Bottom Line
Chapter 22: Understanding the Excel Object Model and Key Objects
Getting an Overview of the Excel Object ModelUnderstanding Excel's Creatable ObjectsManaging WorkbooksWorking with WorksheetsWorking with the Active Cell or SelectionWorking with RangesSetting OptionsThe Bottom Line
Chapter 23: Working with Widely Used Objects in Excel
Working with ChartsWorking with Windows ObjectsWorking with Find and ReplaceAdding ShapesThe Bottom Line
Chapter 24: Understanding the PowerPoint Object Model and Key Objects
Getting an Overview of the PowerPoint Object ModelUnderstanding PowerPoint's Creatable ObjectsWorking with PresentationsWorking with Windows and ViewsWorking with SlidesWorking with MastersThe Bottom Line
Chapter 25: Working with Shapes and Running Slide Shows
Working with ShapesWorking with Headers and FootersSetting Up and Running a Slide ShowThe Bottom Line
Chapter 26: Understanding the Outlook Object Model and Key Objects
Getting an Overview of the Outlook Object ModelWorking with the Application ObjectUnderstanding General Methods for Working with Outlook ObjectsWorking with MessagesWorking with Calendar ItemsWorking with Tasks and Task RequestsSearching for ItemsThe Bottom Line
Chapter 27: Working with Events in Outlook
Working with Application-Level EventsWorking with Item-Level EventsUnderstanding Quick StepsThe Bottom Line
Chapter 28: Understanding the Access Object Model and Key Objects
Getting Started with VBA in AccessGetting an Overview of the Access Object ModelUnderstanding Creatable Objects in AccessOpening and Closing DatabasesWorking with the Screen ObjectUsing the DoCmd Object to Run Access CommandsThe Bottom Line
Chapter 29: Manipulating the Data in an Access Database via VBA
Understanding How to ProceedPreparing to Manage the Data in a DatabaseOpening a RecordsetAccessing a Particular Record in a RecordsetSearching for a RecordReturning the Fields in a RecordEditing a RecordInserting and Deleting RecordsClosing a RecordsetSaving a Recordset to the CloudThe Bottom Line
Chapter 30: Accessing One Application from Another Application
Understanding the Tools Used to Communicate Between ApplicationsUsing Automation to Transfer InformationUsing the Shell Function to Run an ApplicationUsing Data Objects to Store and Retrieve InformationCommunicating via DDECommunicating via SendKeysGoing Beyond VBAThe Bottom Line
Chapter 31: Programming the Office 2016 Ribbon
What Is XML?Hiding the Clipboard Group on the Word RibbonWorking with Excel and PowerPointUndoing Ribbon ModificationsSelecting the Scope of Your Ribbon CustomizationAdding a New GroupAdding CallbacksAdding AttributesUsing Menus and ListsToggling with a Toggle-Button ControlModifying the Ribbon in AccessAdding a Callback in AccessWhat to Look For If Things Go WrongWhere to Go from HereThe Bottom Line
Appendix: The Bottom Line
Chapter 1: Recording and Running Macros in the Office ApplicationsChapter 2: Getting Started with the Visual Basic Editor[[Page_878]]Chapter 3: Editing Recorded MacrosChapter 4: Creating Code from Scratch in the Visual Basic EditorChapter 5: Understanding the Essentials of VBA SyntaxChapter 6: Working with Variables, Constants, and EnumerationsChapter 7: Using Array VariablesChapter 8: Finding the Objects, Methods, and Properties You NeedChapter 9: Using Built-In FunctionsChapter 10: Creating Your Own FunctionsChapter 11: Making Decisions in Your CodeChapter 12: Using Loops to Repeat ActionsChapter 13: Getting User Input with Message Boxes and Input BoxesChapter 14: Creating Simple Custom Dialog BoxesChapter 15: Creating Complex FormsChapter 16: Building Modular Code and Using ClassesChapter 17: Debugging Your Code and Handling ErrorsChapter 18: Building Well-Behaved CodeChapter 19: Exploring VBA's Security FeaturesChapter 20: Understanding the Word Object Model and Key ObjectsChapter 21: Working with Widely Used Objects in WordChapter 22: Understanding the Excel Object Model and Key ObjectsChapter 23: Working with Widely Used Objects in ExcelChapter 24: Understanding the PowerPoint Object Model and Key ObjectsChapter 25: Working with Shapes and Running Slide ShowsChapter 26: Understanding the Outlook Object Model and Key ObjectsChapter 27: Working with Events in OutlookChapter 28: Understanding the Access Object Model and Key ObjectsChapter 29: Manipulating the Data in an Access Database via VBAChapter 30: Accessing One Application from Another ApplicationChapter 31: Programming the Office 2016 Ribbon
End User License Agreement

Content preview from Mastering VBA for Microsoft Office 2016

Chapter 4Creating Code from Scratch in the Visual Basic Editor

In this chapter, you'll practice creating macros (procedures) from scratch in the Visual Basic Editor. The examples walk you through the process of creating a macro in Word, Excel, and PowerPoint.

For the examples in this book, the Visual Basic Editor should be set up a certain way visually, and set to require explicit declarations of variables (considered a good programming practice), so we'll start off this chapter by ensuring that these conditions are met.

The purpose of this chapter is to give you a feel for creating code in the Visual Basic Editor before you study the details of the language in future chapters. Here you'll work briefly with VBA elements (such as objects, properties, ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Mastering VBA for Microsoft Office 2013

Richard Mansfield

Mastering VBA for Microsoft® Office 2010

Richard Mansfield

VSTO for Mere Mortals™: A VBA Developer’s Guide to Microsoft Office Development Using Visual Studio 2005 Tools for Office

Kathleen McGrath, Paul Stubbs

Microsoft® Access® 2010 VBA Programming Inside Out

Andrew Couch

Publisher Resources

ISBN: 9781119225386Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Mastering VBA for Microsoft Office 2016

by Richard Mansfield

Chapter 4Creating Code from Scratch in the Visual Basic Editor

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.