Mastering Vba For Microsoft Office 2016

Richard Mansfield


e-books shop
e-books shop
Purchase Now !
Just with Paypal



Book Details
 Price
 8.00
 Pages
 1420 p
 File Size 
 12,552 KB
 File Type
 PDF format
 ISBN
 978-1-119-22538-6
 978-1-119-22539-3 (ebk.)
 978-1-119-22540-9 (ebk.)
 Copyright©   
 2016 by John Wiley & Sons, Inc 

About the Author
Mastering VBA for Microsoft Office 2016 is Richard Mansfield's 45th book. His
other titles include Visual Guide to Visual Basic (Ventana), CSS Web Design for
Dummies (Wiley), and Programming: A Beginner's Guide (McGraw-Hill).
Overall, his books have sold more than 500,000 copies worldwide and have been
translated into 12 languages. Richard also teaches a course titled Introduction to
XML at the online school ed2go.

Introduction
Visual Basic for Applications (VBA) is a powerful tool that enables you to
automate tasks in Microsoft Office applications.
Automating can save you and your colleagues considerable time and effort.
Getting more work done in less time is usually good for your self-esteem, and it
can do wonderful things for your job security and your career.

How Should I Use This Book?
This book tries to present material in a sensible and logical way. To avoid
repeating information unnecessarily, the chapters build on each other, so the later
chapters generally assume that you've read the earlier chapters.

The first five parts of the book offer a variety of code samples using Word, Excel,
PowerPoint, and, to a lesser extent, Access. If you have these applications (or
some of them), work through these examples as far as possible to get the most
benefit from them. While you may be able to apply some of the examples directly
to your work, mostly you'll find them illustrative of general VBA techniques and
principles, and you can customize them to suit your own needs.

The sixth and last part of this book shows you some more advanced techniques
that are useful when using VBA to program Word, Excel, PowerPoint, Outlook,
and Access. Work through the chapters that cover the application or applications
that you want to program with VBA.

Chapters 30 and 31 are specialized, but quite useful. Chapter 30 shows you how to
use one application to control another application; for example, you might use
Word to contact Excel and benefit from its special mathematic or graphing
capabilities. And Chapter 31 shows you many different ways to program the
Ribbon—the primary user interface in Office 2016 applications.

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


Bookscreen
e-books shop

Is This Book Suitable for Me?
Yes.
No programming experience required. This book is for anyone who wants to learn
to use VBA to automate their work in Office. Automating your work could involve
anything from creating a few simple procedures that would enable you to perform
some complex and tedious operations via a single keystroke, to building a custom
application with a complete interface that looks quite different from the host application.

This book attempts to present theoretical material in a practical context by
including lots of examples of the theory in action. For example, when you learn
about loops, you execute short procedures that illustrate the uses of each kind of
loop so that you can see how and why they work, and when to use them. And you'll
also find many step-throughs—numbered lists that take you through a task, one
step at a time. Above all, I've tried to make this book clear and understandable,
even to readers who've never written any programming in their life.
Previous Post Next Post