book

Mastering VBA for Microsoft Office 2013

Name: Mastering VBA for Microsoft Office 2013
Author: Richard Mansfield
ISBN: 9781118695128

by Richard Mansfield

August 2013

Beginner

960 pages

28h 49m

English

Sybex

Read now

Unlock full access

Cover Page
Title Page
Copyright
Wiley
Dedication
Acknowledgments
About the Author
Contents at a Glance
Contents
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 the 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 for Creating the ProceduresCreating a Procedure for WordCreating a Procedure 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 ArrayFinding Out Whether a Variable Is an ArrayFinding the Bounds of an ArraySorting an ArraySearching through 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 Functions to Manipulate StringsUsing VBA's Mathematical FunctionsUsing VBA's Date and Time FunctionsUsing File-Management FunctionsThe 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 Procedure to Work OnDisplaying 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 BoxLinking 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: Securing Your Code with 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 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 WindowsWorking 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 2013 Ribbon
What Is XML?Hiding the Editing 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 EditorChapter 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: Securing Your Code with 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 2013 Ribbon
Index

Content preview from Mastering VBA for Microsoft Office 2013

images

Chapter 12 Using Loops to Repeat Actions

As in life, so in macros. Sometimes, you'll want to repeat an action a predetermined number of times: break six eggs to make an omelet, or create two new documents.

More often, though, you'll just repeat an action until a certain condition is met: break eggs until the pan is full, or buy two lottery tickets a week until you hit it big, or subtract five from every instance of a value in an Excel spreadsheet. In these situations, you don't know in advance when you'll triumph against the wretched odds of the lottery, or how many times the value will appear in the spreadsheet—your code must simply carry on ...

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 2007, 2nd Edition

Publisher Resources

ISBN: 9781118695128Purchase 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 2013

by Richard Mansfield

Chapter 12

Using Loops to Repeat Actions

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.