book

Mastering VBA for Microsoft Office 2007, 2nd Edition

Name: Mastering VBA for Microsoft Office 2007, 2nd Edition
Author: Richard Mansfield
ISBN: 9780470279595

by Richard Mansfield

April 2008

Beginner

888 pages

25h 46m

English

Sybex

Read now

Unlock full access

Cover
Contents
Title
Copyright
Dedication
Acknowledgments
About the Author
Introduction
Part 1: Recording Macros and Getting Started with VBA
Chapter 1: Recording and Running Macros in the Microsoft 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 MacroAssigning a Way of Running the 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 EditorClosing the Visual Basic Editor and Returning to the Host ApplicationThe 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 ObjectsThe Bottom Line
Part 3: Making Decisions and Using Loops and Functions
Chapter 9: Using Functions
What is a Function?Using FunctionsUsing Functions to Convert Data from One Type to AnotherUsing 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 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 OperatorsIf StatementsSelect Case StatementsThe 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 Dialog Box to a ProcedureRetrieving the User’s Choices from a Dialog BoxExamples of Connecting Dialog Boxes to ProceduresUsing an Application’s Built-in Dialog Boxes from VBAThe Bottom Line
Chapter 15: Creating Complex Dialog Boxes
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 the 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 ObjectsWorking with 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 EventsThe 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 Access the Data in the DatabaseOpening a RecordsetAccessing a Particular Record in a RecordsetSearching for a RecordReturning the Fields in a RecordEditing a RecordInserting and Deleting RecordsClosing a RecordsetThe Bottom Line
Chapter 30: Accessing One Application from Another Application
Understanding the Tools for Communicating with Other ApplicationsUsing Automation to Transfer InformationUsing the Shell Function to Run an ApplicationUsing Data Objects to Store and Retrieve InformationCommunicating via DDECommunicating via SendKeysThe Bottom Line
Chapter 31: Programming the Office 2007 Ribbon
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 Solutions
Glossary
Index

Content preview from Mastering VBA for Microsoft Office 2007, 2nd Edition

Chapter 9 Using Functions

VBA comes with a large number of built-in functions to perform commonly needed operations — everything from determining whether a file exists, to returning the current date, to converting data from one format to another. (For example, you can use a function to convert numeric data into a text string.) This chapter shows you what functions are and what they do, and shows you how to use functions. Along the way, you’ll meet some of the key functions that VBA provides, including functions for converting data from one data type to another, file-operation functions, date functions, and mathematical functions.

You can also create custom functions of your own to supplement VBA’s functions. The next chapter tells you how to ...

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

Publisher Resources

ISBN: 9780470279595

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Mastering VBA for Microsoft Office 2007, 2nd Edition

by Richard Mansfield

Chapter 9

Using Functions

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.