book

Mastering VBA for Microsoft Office 2007, 2nd Edition

by Richard Mansfield

April 2008

Beginner

888 pages

25h 46m

English

Sybex

Read now

Unlock full access

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

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
Testing a Macro in the Visual Basic EditorEditing the Word MacroEditing the Excel MacroEditing a PowerPoint MacroThe Bottom Line
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
Getting ReadyProceduresStatementsKeywordsExpressionsOperatorsVariablesConstantsArgumentsObjectsCollectionsPropertiesMethodsEventsThe Bottom Line
Working with VariablesWorking with ConstantsWorking with EnumerationsThe Bottom Line
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
What is an Object?Working with CollectionsFinding the Objects You NeedUsing Object Variables to Represent ObjectsThe Bottom Line
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
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
How Do You Compare Things in VBA?Testing Multiple Conditions by Using Logical OperatorsIf StatementsSelect Case StatementsThe Bottom Line
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
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
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
Creating and Working with Complex Dialog BoxesUsing Events to Control FormsThe Bottom Line
Creating Modular CodeCreating and Using ClassesThe Bottom Line
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
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
Understanding How VBA Implements SecuritySigning Your Macro Projects with Digital SignaturesChoosing a Suitable Level of SecurityLocking Your CodeThe Bottom Line
Examining the Word Object ModelWorking with the Documents Collection and the Document ObjectWorking with the Selection ObjectCreating and Using RangesManipulating OptionsThe Bottom Line
Using Find and Replace via VBAWorking with Headers, Footers, and Page NumbersWorking with Sections, Page Setup, Windows, and ViewsWorking with TablesThe Bottom Line
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
Working with ChartsWorking with WindowsWorking with Find and ReplaceAdding ShapesThe Bottom Line
Getting an Overview of the PowerPoint Object ModelUnderstanding PowerPoint’s Creatable ObjectsWorking with PresentationsWorking with Windows and ViewsWorking with SlidesWorking with MastersThe Bottom Line
Working with ShapesWorking with Headers and FootersSetting Up and Running a Slide ShowThe Bottom Line
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
Working with Application-Level EventsWorking with Item-Level EventsThe Bottom Line
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
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
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
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

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

Chapter 2 Getting Started with the Visual Basic Editor

In this chapter, you’ll start learning how to use the Visual Basic Editor, a powerful tool that Microsoft provides for working with VBA code and user forms. This programming editor is the culmination of over 15 years of modifications and improvements. It is highly effective.

All applications that host VBA use the Visual Basic Editor, so when you’re working with VBA, the environment looks much the same no matter which application you’re using.

This chapter shows you the basics of the Visual Basic Editor: its components, what they do, and how you use them. You’ll learn more advanced maneuvers as you work with VBA later in this book.

This chapter also shows you how to customize the Visual Basic ...