the object model visual basic for applications 2
TRANSCRIPT
![Page 1: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/1.jpg)
The Object Model
Visual Basic for Applications
2
![Page 2: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/2.jpg)
Objectives
In this tutorial, you will learn how to:
Refer to the objects contained in an application’s
object model
Use the Object Browser in the Visual Basic Editor
Enter instructions in the Immediate window in the
Visual Basic Editor
Refer to the most commonly used objects in Excel,
Word, and Access
2
![Page 3: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/3.jpg)
Concept Lesson:Understanding the Object Model
Although all VBA-enabled applications
share the same language, each contains
its own unique set of objects, referred to
as the application’s object model
A collection object, typically referred to
simply as a collection, is a group of one or
more individual objects treated as one unit
2
![Page 4: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/4.jpg)
Simplified Object Modelfor a Library
2
![Page 5: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/5.jpg)
Referring to the Objects Containedin an Application’s Object Model
You can refer to an individual object within a collection using either its name or its position number in the collection
The Application rectangle represents the Application object
Immediately below the Application rectangle is the Presentations (Presentation) rectangle, which represents both the Presentations collection and the individual Presentation objects within the collection
2
![Page 6: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/6.jpg)
The Most Commonly Used PowerPoint Objects
2
![Page 7: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/7.jpg)
Referring to the Objects Containedin an Application’s Object Model
The Slides (Slide) rectangle is one level down from the Presentations (Presentation) rectanglein the object model
The plural word Slides denotes the name of the collection, and the singular word Slide indicates the type of object included inthe collection
2
![Page 8: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/8.jpg)
Referring to Objectsin VBA Code
You can use VBA to refer to any element in an application’s object model, but to do so you must specify the object’s exact location in the object model’s hierarchy
You begin at the top of the hierarchy, with the Application object, and then work your way down each level until you reach the desired element
You can refer to a specific object within a collection using either the object’s name, enclosed in quotation marks, or its position number within the collection, referred to as its index
In most collections, the index of the first object in the collection is 1
2
![Page 9: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/9.jpg)
Referring to an Object’s Properties and Methods in VBA Code
2
A property is a characteristic of an object, and a method is an action that an object can perform on its own
The Application object has a Name property that stores the Application object’s name, and it has a Quit method that is used to end the application
You use the expression.property syntax in a VBA instruction to access an object’s property, and you use the expression.method syntax to invoke one of its methods
![Page 10: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/10.jpg)
Referring to an Object’s Properties and Methods in VBA Code
Before you can customize an application,
you need to become familiar with the
objects in its object model
One way to do so is to use the Object
Browser, which is available in the Visual
Basic Editor
2
![Page 11: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/11.jpg)
The Object Browser
The Object Browser allows you to browse through all available objects in an application and see their properties, methods, and events
2
![Page 12: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/12.jpg)
The Object Browser2
A class is the formal definition of an object; it defines the properties that control the object’s appearance, and the methods and events that control the object’s behavior
All of the information pertaining to an application’s object model is stored in an object library, which is simply a file
![Page 13: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/13.jpg)
The PowerPoint Application Object’s Help Screen
2
![Page 14: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/14.jpg)
Getting Help in theObject Browser
You can view an object’s Help screen by clicking the object’s class in the Classes list box, and then clicking the Help button located at the top of the Object Browser window
According to the Help screen, the Application object represents the entire Microsoft PowerPoint application
To view a listing of these lower-level objects, you need simply to click the Multiple Objects rectangle; doing so will open the Topics Found dialog box
2
![Page 15: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/15.jpg)
Getting Help in theObject Browser
2
To view a Help screen for one of these objects, you select the object’s name in the list, and then click the Display button
You can view the Help screen for an individual Presentation object by clicking Presentation in the Presentations Collection Object Help screen
![Page 16: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/16.jpg)
Presentations Collection Object Help Screen
2
![Page 17: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/17.jpg)
Using the Immediate Window
In addition to the Code window, the Visual Basic Editor also provides an Immediate window into which you can enter VBA instructions
2
![Page 18: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/18.jpg)
Using the Immediate Window
The Application.Quit instruction does not begin with a question mark
Because Quit is a method, which represents an action that an object can perform, it does not store a value that can be displayed, as does a property
The Quit method simply instructs the application toclose itself
2
![Page 19: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/19.jpg)
Using the Immediate Window
In the ?Application.Presentations.Count instruction, Application.Presentations specifies the location of the Presentations collection in the object model, and Count specifies the desired property
The Application.Presentations.Count reference indicates that the Count property is a member of the Presentations collection, which is contained in the Application object
Applications.Presentations(2) is the expression, and it indicates that the object is the second Presentation object contained within the Presentations collection, which is contained within the Application object; Name is the property whose value you want to display
2
![Page 20: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/20.jpg)
Using the Immediate Window2
![Page 21: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/21.jpg)
Immediate Window Showing Additional VBA Instructions and Their Results
2
![Page 22: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/22.jpg)
Repeating and Deleting Instructionsin the Immediate Window
If you want to repeat an instruction, you
can either retype the instruction after the
last instruction in the Immediate window,
or you can select the instruction in the
Immediate window, copy it to the
clipboard, and then paste it after the last
instruction in the Immediate window
2
![Page 23: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/23.jpg)
Printing Code from the Immediate Window
You can use either a text editor or a word
processor—for example, WordPad or Word—
to print the instructions entered in the
Immediate window
Like the Object Browser and the Help screens,
the Immediate window is an invaluable tool for
learning an application’s object model
2
![Page 24: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/24.jpg)
Summary
To refer to an object in an application’sobject model:
Specify the object’s exact location in theobject model
If the object is a member of a collection, include either the object’s name or its index in a set of parentheses following the collection’s name
To access the property of an object: Use the expression.property syntax
To invoke one of an object’s methods: Use the expression.method syntax
2
![Page 25: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/25.jpg)
Summary
To view information about the various objects available to your application:
Open the Object Browser window by clicking the Object Browser button on the Visual Basic Editor’s Standard toolbar
Select the appropriate object library from the Project/Library list box
Click the appropriate keyword in the Classes list box, and then click the Help button
2
![Page 26: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/26.jpg)
Summary
To use the Immediate window in the Visual Basic Editor:
Open the Immediate window in the Visual Basic Editor by clicking View on the Visual Basic Editor menu bar, and then clicking Immediate window
Enter the instruction in the Immediate window You can copy and paste instructions from one part
of the Immediate window to another Remove one or more lines from the Immediate
window by selecting the lines you want to remove and then pressing either the Delete key or the Backspace key
2
![Page 27: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/27.jpg)
Excel Lesson:Understanding the Excel
Object Model Figure 2-17
shows the portion of the Microsoft Excel object model that includes the most commonly used Excel objects
2
![Page 28: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/28.jpg)
Understanding the Excel Object Model
A Range object in Excel is defined asa cell, a row,a column, ora selectionof cells containing one or more contiguous or noncontiguous blocks of cells
2
![Page 29: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/29.jpg)
Using the ObjectBrowser in Excel
The Object Browser is a useful tool for understanding the structure of an object model and for studying the various objects included in the model, as well as the properties and methods of the objects
To access the Object Browser in Excel, use the steps on pages 102 to 105 of the textbook
A Multiple Objects rectangle representsthe next level of objects in the objectmodel hierarchy
2
![Page 30: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/30.jpg)
Object Browser Window Showing the Excel Object Library
2
![Page 31: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/31.jpg)
Microsoft Visual Basic Help Window
2
![Page 32: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/32.jpg)
Using the ObjectBrowser in Excel
2
![Page 33: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/33.jpg)
Using the ObjectBrowser in Excel
To view the next level of objects in the object model hierarchy use the steps on pages 106 to 108 of the textbook
2
![Page 34: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/34.jpg)
Workbook Object Help Screen2
![Page 35: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/35.jpg)
Using the ImmediateWindow in Excel
Unlike the Code window, the Immediate window allows you to test a line of code without having to create and run anentire procedure
To open Martin’s workbook, save the workbook, and enter several VBA instructions into the Immediate window, use the steps on pages 108 to 112 ofthe textbook
2
![Page 36: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/36.jpg)
QtrSales.xls Workbook2
![Page 37: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/37.jpg)
Visual Basic Editor Showing Open Project Explorer and
Immediate Windows
2
![Page 38: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/38.jpg)
VBA Instructions Entered in the Immediate Window
2
![Page 39: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/39.jpg)
Referring to Range Objects
A Range object in Excel is defined as a cell, a row, a column, or a group of contiguous or noncontiguous cells
For referring to Range Objects, use the steps on pages 113 to 115 of the textbook
2
![Page 40: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/40.jpg)
Additional Instruction Entered in the Immediate Window
2
![Page 41: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/41.jpg)
Jackson Worksheet Showing Selection of a Range Object
Consisting of Noncontiguous Cells
2
![Page 42: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/42.jpg)
Word Lesson:Understanding the Word
Object Model Figure 2-31 shows the portion of the Microsoft Word
object model that includes the most commonly used Word objects
2
![Page 43: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/43.jpg)
Word Objects and Collections2
![Page 44: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/44.jpg)
Understanding the Word Object Model
A Range object in Word is defined as a contiguous area in a document; it can be assmall as the insertion point or as large as the entire document
Although a paragraph is a contiguous area in a document, it is considered a Paragraph object rather than a Range object
The Application object, which refers to Microsoft Word, contains one Document object in its Documents collection
The Document object’s name is Word Lesson.doc
2
![Page 45: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/45.jpg)
Understanding the Word Object Model
The Word Lesson document also contains five sentences in its Sentences collection; each sentence is a Range object
Like the Sentences collection, the Words collection also consists of Range objects
The Characters collection also consistsof Range objects, but each Range object in this collection is composed of exactly one character
2
![Page 46: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/46.jpg)
Using the Object Browser in Word
2
To access the Object Browser in Word use the steps on pages 122 to 125of the textbook
![Page 47: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/47.jpg)
Microsoft Visual Basic Help Window2
![Page 48: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/48.jpg)
Quit Method Help Screen2
![Page 49: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/49.jpg)
Example Showing the QuitMethod in a VBA Instruction
2
![Page 50: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/50.jpg)
Using the Object Browserin Word
To view the next level of objects in the object model hierarchy, use the steps on pages 125 to 127 of the textbook
If you are using a color monitor, you will notice that the object model diagram located at the top of the Help screen contains two blue rectangles and one yellow rectangle
You can use this Help screen to display a listing of the properties, methods, and events associated with a Document object
2
![Page 51: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/51.jpg)
Documents Collection Object Help Screen
2
![Page 52: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/52.jpg)
Document Object Help Screen2
![Page 53: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/53.jpg)
Using the Immediate Window
You will enter several VBA instructions into the Immediate window in the Visual Basic Editor
Recall that, unlike the Code window, the Immediate window allows you to test a lineof code without having to create and run an entire procedure
To open Pat’s document, then save the document and enter several VBA instructions into the Immediate window, use the steps on pages 128 to 132 of the textbook
2
![Page 54: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/54.jpg)
Offer Document2
![Page 55: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/55.jpg)
Visual Basic Editor Showing Open Project Explorer and Immediate Windows
2
![Page 56: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/56.jpg)
Using the Immediate Window2
The first VBA instruction you will enter into the Immediate window, ?Application.Name, will display the value stored in the Application object’s Name property
When you are told to enter an instruction, you always will be shown the complete instruction to enter
![Page 57: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/57.jpg)
Using the Immediate Window
To use the Selection object, use the steps on pages 132 and 133 of the textbook
2
![Page 58: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/58.jpg)
Using the Range Object’s Select Method
2
You can use the Range object’s Select
method to select a word, sentence,
paragraph, or character in a document
To use the Select method in the Offer
document, use the steps on page 134 of
the textbook
![Page 59: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/59.jpg)
Access Lesson:Understanding the Access
Object Model Figure 2-43 shows the portion of the Microsoft
Access object model that includes several of the most commonly used Access objects
2
![Page 60: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/60.jpg)
Understanding the Access Object Model
In Access, forms and reports are considered AccessObject objects
You will use the Object Browser in the Visual Basic Editor, to display a Help screen for the Application object in Access
2
![Page 61: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/61.jpg)
Using the Object Browser in Access
To use the Object Browser in Access, use the steps on pages 140 to 144 of the textbook
2
![Page 62: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/62.jpg)
Microsoft Visual Basic Help Window
2
![Page 63: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/63.jpg)
Quit Method (Application Object) Help Screen
2
The syntax of the method is Application.Quit
![Page 64: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/64.jpg)
Using the Object Browser in Access
2
When you enter the Quit method in either the Immediate or Code windows, the Auto List Members list shows that the names of the constants are acQuitPrompt, acQuitSaveAll, and acQuitSaveNone
![Page 65: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/65.jpg)
Using the Object Browser in Access
2
To view the next level of objects in the object model hierarchy, use the steps listed on pages 145 to 146 of the textbook
Notice that a Multiple Objects rectangle, which represents the objects located on the next level in the object model hierarchy, appears below the CurrentProject rectangle in the Help screen
![Page 66: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/66.jpg)
AllReports CollectionHelp Screen
2
![Page 67: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/67.jpg)
Using the ImmediateWindow in Access
You will enter several VBA instructions into the Immediate window in the Visual Basic Editor
To enter several VBA instructions into the Immediate window, use the steps on pages146 to 149 ofthe textbook
2
![Page 68: The Object Model Visual Basic for Applications 2](https://reader035.vdocuments.us/reader035/viewer/2022062517/56649ed95503460f94be8652/html5/thumbnails/68.jpg)
VBA Instructions Entered in the Immediate Window
2