In this chapter, you'll learn how to find the objects you need in the applications you're using. To learn the material in this chapter, you'll build on what you've learned in the earlier chapters. You'll start by examining the concepts involved: what objects and collections are, what properties are, and what methods are. You'll then learn how to find the objects, collections, properties, and methods you need to make your code work. To identify these items, you'll use a number of tools you've already read about, including the Object Browser (which you used briefly in Chapter 4, "Creating Code from Scratch in the Visual Basic Editor") and the Help files for VBA.
Along the way, this chapter explains how to use Object variables to represent objects in your code.
In this chapter you will learn to do the following:
Understand and use objects, properties, and methods
Use collections of objects
Find objects, properties, and methods
Use Object variables to represent objects
VBA-enabled applications (and many other modern applications) consist of a number of discrete objects, each with its own characteristics and capabilities.
Building an application out of objects is called object-oriented programming (OOP). In theory, object-oriented programming has a number of benefits—for example, the code is easy to build and maintain because you break it down into objects of a manageable size.
Object-oriented programs should also be easier to understand than monolithic programs because it's less difficult for most people to grasp the concept of individual objects with associated characteristics and actions than to remember a far longer list of capabilities for the application as a whole. Locating the commands you need can also be faster with OOP taxonomy. For example, a table in Word is represented by a Table object, and a column is represented by a Column object. The Column object has a Width property that sets or returns its width. It's simpler to manage this information when it's broken down into small pieces than to deal with some complex command such as WordTableSetColumnWidth or WordTableGetColumnWidth.
A third benefit of object-oriented programs is that they can be more extensible: the user can build custom objects to implement functionality that the application doesn't contain. For example, you can use VBA to build your own objects that do things that the applications themselves can't do.
Objects can—and frequently do—contain other objects. Typically, the objects in an object-oriented application are arranged into a hierarchy called the object model of the application. This hierarchy is intended to make it easier to figure out where—within a large library of objects—you'll find a particular object that you want to use in your VBA code. It's similar to the way a biography is likely to be found in the library's non-fiction area.
Most VBA host applications, including all the major Office applications, have an Application object that represents the application as a whole. The Application object has properties and methods for things that apply to the application as a whole. For example, many applications have a Quit method that exits the application and a Visible property that controls whether the application is visible or hidden.
In a typical object model, the Application object essentially contains all the other objects (and collections—groups—of objects) that make up the application. For example, Excel has an Application object that represents the Excel application, a Workbook object (grouped into the Workbooks collection) that represents a workbook, and a Worksheet object (grouped into the Sheets collection) that represents a worksheet. The Workbook object is contained within the Application object because you normally need to have the Excel application open to work with an Excel workbook.
In turn, the Worksheet object is contained within the Workbook object because you need to have an Excel workbook open to work with a worksheet. Walking further down the object model, the Worksheet object contains assorted other objects, including Row objects that represent the individual rows in the worksheet, Column objects that represent columns in the worksheet, and Range objects (which represent ranges of cells). And these objects in turn contain further objects.
To get to an object, you typically walk down through the hierarchy of the object model until you reach the object you're looking for.
To get to a Range object in Excel, for example, you would go through the Application object to the Workbook object, through the Workbook object to the appropriate Sheet object, and then finally to the Range object. The following statement shows how to select the range A1 in the first worksheet in the first open workbook (more on this in a minute):
Application.Workbooks(1).Sheets(1).Range("A1").SelectThe Application object, however, is optional and is usually left out of code lines. Why? Because you'd have to go through the Application object to get to pretty much anything in the application, most applications expose (make available to you) a number of creatable objects. Creatable merely means that you can access without having to type the word Application in your code. It's assumed. This is similar to the fact that you don't have to include the word Earth when addressing an envelope. There's only that one possibility.
These creatable objects are usually the most-used objects for the application, and by going through them, you can access most of the other objects without having to refer to the Application object. For example, Excel exposes the Workbooks collection as a creatable object, so you can use the following statement, which doesn't use the Application object, instead of the previous statement:
Workbooks(1).Sheets(1).Range("A1").SelectAny object can have properties and methods. The next sections discuss these items indetail.
In VBA, a property is an attribute or characteristic of an object—a way of describing it or part of it. Most objects have multiple properties that describe each relevant aspect of it. Each property has a specific data type for the information it stores. For example, the objects that represent files (such as documents, workbooks, or presentations) typically have a Boolean property named Saved that stores a value denoting whether all changes in the object have been saved (a value of True) or not (a value of False). These two values encompass the range of possibilities for the object: it can either contain unsaved changes or not contain unsaved changes. There is no third state.
Similarly, most objects that represent files have a Name property that contains the name of the file in question. The Name property is a String property because it needs to contain text. And that text can be set to just about anything, only limited by the 255-character path that Windows permits and by certain characters—such as colons and pipe (|) characters—that Windows forbids in filenames.
To work with a property, you get (fetch) it to find out its current value or set (change) it to a value of your choosing. Many properties are read/write, meaning that you can both get and set their values, but some properties are read-only, meaning that you can view their values but not change them.
The Saved property is read/write for most applications, so you can set it. This means that you can tell the application that a file contains unsaved changes when it really doesn't or that it contains no unsaved changes when it actually has some. (Changing the Saved property can be useful when you're manipulating a file without the user's knowledge.) But the Name property of a file object is read-only—you'll typically set the name by issuing a Save As command, after which you cannot change the name from within the application while the file is open. So you can return the Name property but not set it. You'll also encounter some write-only properties, properties that you can set but that you cannot get.
When an object contains another object, or contains a collection, it typically has a property that you call (invoke) to return the contained object or collection. For example, the Word Document object includes a PageSetup property that returns the PageSetup object for the document (the PageSetup object contains settings such as paper size, orientation, lines per page, and margins for the document) and a Tables property that you call to return the Tables collection. Here's how you can call the PageSetup object (which is contained in the Document object):
Sub GetLinesPage()
Dim sngLinesPerPage As Single
sngLinesPerPage = ActiveDocument.PageSetup.LinesPage
MsgBox sngLinesPerPage
End SubEach object of the same type has the same set of properties but stores its own particular values for them. For example, if you're running PowerPoint and have three Presentation objects open, each has its own Name property. The value in each Name property is specific to each Presentation object. In other words, the value in a property in one object has nothing to do with the value in that property in another object: Each object is independent of the other objects.
A method is an action that an object can perform, a capability an object has. For example, the Document object in various applications has a Save method that saves the document. You can use the Save method on different Document objects—Documents(1).Save saves the first Document object in the Documents collection, and Documents(2).Save saves the second Document object—but the Save method does the same thing in each case. An object can have one or more methods associated with it. Some objects have several dozen methods to implement all the functionality they need.
The Save method is very common. It appears in many applications, as do other methods, such as SaveAs (which saves the file with a different name, location, or both) and Close (which closes the file).
But other methods are unique to a particular application. For example, the Presentation object in PowerPoint has an AddBaseline method that applies a baseline (consisting either of the active presentation or of a specified presentation file) that enables you to track changes for a merge. The Document object in Word has no AddBaseline method, but it has an AcceptAllRevisions method that accepts all revisions in the document. PowerPoint doesn't have an AcceptAllRevisions method.
Just as methods like Save are common to multiple applications, so too are some methods found in more than one object. For example, the Delete method is associated with many different objects. As its name suggests, the Delete method usually deletes the specified object. But other implementations of the Delete method behave somewhat differently, depending on the object they're working with. So even if you're familiar with a method from using it with one object, you need to make sure that it will have the effect you expect when you use it with another object.
Some methods take no arguments. Other methods take one or more arguments (to supply necessary information). Just as with built-in functions like MsgBox, some methods' arguments are required, while others are optional.
When a method applies to multiple objects, it may have different syntax for different objects. Again, even if you're familiar with a method, you need to know exactly what it does with the object for which you're planning to use it.
To use a method, you access it through the object involved. For example, to close the ActivePresentation object, which represents the active presentation in PowerPoint, you use the Close method (but you must specify the ActivePresentation object, like this):
ActivePresentation.Close
When an object contains more than one object of the same type, the objects are said to be grouped into a collection. For example, Word uses Document objects, which are grouped into the Documents collection; PowerPoint has a Presentations collection for Presentation objects, and Excel has the Workbooks collection.
As in these examples, the names of most collections are simply the plural of the object in question. There are some exceptions, such as the Sheets collection in Excel that contains the Worksheet objects. But by and large the names of most collections are easy to derive from the name of the objects they contain—and vice versa.
A collection is an object too and can have its own properties and methods. For example, many collections have a Count property that tells you how many objects are in the collection. This next example tells you how many documents are in the Documents collection:
Sub GetDocCount()
Dim lngCount As Long
lngCount = Documents.Count
MsgBox lngCount
End SubCollections tend to have fewer properties and methods than individual objects. Most collections have an Add method for adding another object to the collection. Some collections, however, are read-only and do not have an Add method. Most collections have an Item property (the default property) for accessing an item within the collection.
Most collections in VBA have the core group of properties listed in Table 8.1
Table 8.1. Core properties for collections in VBA
To work with an object in a collection, you identify the object within the collection either by its name or by its position in the collection. For example, the following statement returns the first Document object in the Documents collection and displays its Name property in a message box:
MsgBox Documents(1).Name
You can optionally use the Item property to return an object from the collection, but because Item is the default property of a collection, you don't need to use it. The following two statements have the same effect, so there's no advantage to using the Item method:
strName = Documents(1).Name strName = Documents.Item(1).Name
The Visual Basic Editor provides a number of tools for finding the objects you need:
(Microsoft applications only) The Macro Recorder, which you used to record macros in some Microsoft Office applications in Chapter 1, "Recording and Running Macros in the Office Applications"
The Object Browser, which you used briefly in Chapter 4
The online Help system, which should provide detailed help on the objects in the application (though this depends on how much effort the software manufacturer put into the Help system)
The Auto List Members feature in the Visual Basic Editor
The following sections show you how to use these tools to find objects.
If you're using a Microsoft application, chances are that the easiest way to find the objects you need is to run the Macro Recorder to record a quick macro using the objects you're interested in. By recording the actions you perform, the Macro Recorder creates code that you can then open in the Visual Basic Editor, examine, and modify if necessary.
In spite of its advantages, the Macro Recorder does have two drawbacks:
First, you can't record every action that you might want. Let's say you're working in Excel and want to create a statement that performs an action on a specified workbook in the
Workbookscollection rather than on the active workbook. With the Macro Recorder, you can record only actions performed on the active workbook. (This is the case because the Macro Recorder can record only those actions you can perform interactively in Excel, and you can't work interactively with any workbook other than the active one.)Second, the Macro Recorder is apt to record statements that you don't strictly need, particularly when you're trying to record a setting in a dialog box.
You saw an example of the second problem in Chapter 4. Here's another example, this time recording a macro to create an AutoCorrect entry:
Start Word.
Click the Record Macro button on the status bar, or click the Developer tab on the Ribbon and then click the Record Macro button in the Code section. This displays the Record Macro dialog box. Type Add_Item_to_AutoCorrect in the Macro Name text box, and type a description in the Description text box. Make sure All Documents (Normal.dotm) is selected in the Store Macro In drop-down list, and then click the OK button to start recording.
Press Alt+F, I. Then click the Proofing button and the AutoCorrect Options button to display the AutoCorrect dialog box. Type reffs in the Replace box and references in the With box, and click the Add button. Then click OK twice to close both open dialog boxes.
Click the Stop Recording button on the Ribbon or the status bar to stop the Macro Recorder.
Now press Alt+F8 to display the Macros dialog box, select the Add_Item_to_AutoCorrect entry, and click the Edit button to open the macro in the Visual Basic Editor. The code should look like this:
Sub Add_Item_to_AutoCorrect()
'
' Add_Item_to_AutoCorrect Macro
' Change reffs to references
'
AutoCorrect.Entries.Add Name:="reffs", Value:="references"
With Options
.AutoFormatAsYouTypeApplyHeadings = False
.AutoFormatAsYouTypeApplyBorders = True
.AutoFormatAsYouTypeApplyBulletedLists = True
.AutoFormatAsYouTypeApplyNumberedLists = True
.AutoFormatAsYouTypeApplyTables = True
.AutoFormatAsYouTypeReplaceQuotes = True
.AutoFormatAsYouTypeReplaceSymbols = True
.AutoFormatAsYouTypeReplaceOrdinals = True
.AutoFormatAsYouTypeReplaceFractions = True
.AutoFormatAsYouTypeReplacePlainTextEmphasis = False
.AutoFormatAsYouTypeReplaceHyperlinks = True
.AutoFormatAsYouTypeFormatListItemBeginning = True
.AutoFormatAsYouTypeDefineStyles = False
.TabIndentKey = True
End With
With AutoCorrect
.CorrectInitialCaps = True
.CorrectSentenceCaps = True
.CorrectDays = True
.CorrectCapsLock = True
.ReplaceText = True
.ReplaceTextFromSpellingChecker = True
.CorrectKeyboardSetting = False
.DisplayAutoCorrectOptions = True
.CorrectTableCells = True
End With
With OMathAutoCorrect
.UseOutsideOMath = False
.ReplaceText = True
End With
With Options
.AutoFormatApplyHeadings = True
.AutoFormatApplyLists = True
.AutoFormatApplyBulletedLists = True
.AutoFormatApplyOtherParas = True
.AutoFormatReplaceQuotes = True
.AutoFormatReplaceSymbols = True
.AutoFormatReplaceOrdinals = True.AutoFormatReplaceFractions = True
.AutoFormatReplacePlainTextEmphasis = True
.AutoFormatReplaceHyperlinks = True
.AutoFormatPreserveStyles = True
.AutoFormatPlainTextWordMail = True
End With
Options.LabelSmartTags = False
End SubHere, you get dozens of lines of unnecessary code in addition to the one line you need:
AutoCorrect.Entries.Add Name:="reffs", Value:="references"
This line shows you that to add an AutoCorrect entry, you need to work with the Entries collection object in the AutoCorrect object. You use the Add method on the Entries collection to add an AutoCorrect entry to the list.
The other lines of code specifying the status of various options are unnecessary because you are not interested in changing any of them in this macro.
By removing these extraneous lines from this recorded macro, you can reduce it to just the single line it needs to contain (together with the comment lines, which you can also remove if you want):
Sub Add_Item_to_AutoCorrect()
'
' Add_Item_to_AutoCorrect Macro
' Change reffs to references
'
AutoCorrect.Entries.Add Name:="reffs",Value:="references"
End SubYou used the recorder to see the correct syntax for adding an entry to the AutoCorrect feature. There's no point to leaving in lines of code unrelated to your purposes. What's more, such extraneous code would make it harder at some future date to read and understand the macro's purpose.
In spite of its limitations, the Macro Recorder does provide quick access to the objects you need to work with, and you can always adjust the resulting code in the Visual Basic Editor. What's more, the code that the recorder generates is, if nothing else, guaranteed to execute without bugs.
For many programmers, the primary tool for finding the objects you need is the Object Browser, which you used briefly in Chapter 4. In this section, you'll get to know the Object Browser better and learn to use it to find the information you need about objects.
The Object Browser provides the following information about both built-in objects and custom objects you create:
Classes (formal definitions of objects)
Properties (the attributes of objects or aspects of their behavior)
Methods (actions you can perform on objects)
Events (for example, the opening or closing of a document)
Constants (named items that keep a constant value while a program is executing)
Figure 8.1 shows the components of the Object Browser.
Figure 8.1. The Object Browser provides information on built-in objects and custom objects. Here, the application is Excel.
Here's what the different elements of the Object Browser do:
The Project/Library drop-down list provides a list of object libraries available to the current project. (An object library is collection of objects made available to programs. There can be several libraries in use at a given time. For example, one library might contain objects that specialize in rendering graphics; a second library might contain objects that assist with security features; and so on.) Use the drop-down list to choose the object libraries you want to view. For example, you might choose to view only objects in Outlook by choosing Outlook in the Project/Library drop-down list. Alternatively, you could stay with the default choice of <All Libraries>.
In the Search Text box, enter the string you want to search for: Either type it in or choose a previous string in the current project session from the drop-down list. Then either press Enter or click the Search button to find members containing the search string.
Click the Go Back button to retrace one by one your previous selections in the Classes list and the Members Of list. Click the Go Forward button to move forward through your previous selections one by one. The Go Back button becomes available when you go to a class or member in the Object Browser; the Go Forward button becomes available only when you've used the Go Back button to go back to a previous selection.
Click the Copy To Clipboard button to copy the selected item from the Search Results list, the Classes list, the Members Of list, or the Details pane to the Clipboard so that you can paste it into your code.
Click the View Definition button to display a code window containing the code for the object selected in the Classes list or the Members Of list. The View Definition button is available (undimmed) only for objects that contain code, such as procedures and user forms that you've created.
Click the Help button to display any available Help for the currently selected item. Alternatively, press the F1 key.
Click the Search button to search for the term entered in the Search Text box. If the Search Results pane isn't open, VBA opens it at this point.
Click the Show/Hide Search Results button to toggle the display of the Search Results pane on and off.
The Search Results list in the Search Results pane contains the results of the latest search you've conducted for a term entered in the Search Text box. If you've performed a search, the Object Browser updates the Search Results list when you use the Project/Library drop-down list to switch to a different library. Choosing a different library in the Project/Library drop-down list is a handy way of narrowing, expanding, or changing the focus of your search.
The Classes list shows the available classes in the library or project specified in the Project/Library drop-down list.
The Members Of list displays the available elements of the class selected in the Classes list. A method, constant, event, property, or procedure that has code written for it appears in boldface. The Members Of list can display the members either grouped into their different categories (methods, properties, events, and so on) or ungrouped as an alphabetical list of all the members available. To toggle between grouped and ungrouped, right-click in the Members Of list and choose Group Members from the context menu; click either to place a check mark (to group the members) or to remove the check mark (to ungroup the members).
The Details pane displays the definition of the member selected in the Classes list or in the Members Of list. For example, if you select a procedure in the Members Of list, the Details pane displays its name, the name of the module and template or document in which it's stored, and any comment lines you inserted at the beginning of the procedure. The module name and project name contain hyperlinks (jumps) so that you can quickly move to them. You can copy information from the Details pane to the Code window by using either copy and paste or drag and drop.
Drag the three split bars to resize the panes of the Object Browser to suit yourself. (You can also resize the Object Browser window as needed or maximize it so that it docks itself in the Code window.)
The Object Browser uses different icons to indicate the various types of object that it lists. Figure 8.1 shows several icons; Table 8.2 shows the full range of icons and what they represent.
A blue dot in the upper-left corner of a Property icon or a Method icon indicates that that property or method is the default.
The default object libraries are sufficient for most typical macros, so you need not worry about adding any specialized libraries. If you get into some kinds of advanced macro programming, however, you will need to add other libraries (you'll modify the Access Ribbon in Chapter 31, and to do that you'll add a special library). You can add and remove object libraries by choosing Tools
By adding object libraries, you can make available additional objects to work with.
By removing object libraries that you don't need to view or use, you can reduce the number of object references that VBA needs to resolve when it is compiling the code in a project. This allows the code to run faster, though as I've mentioned before, today's computers are so fast that finding ways to increase speed of macro execution is rarely an issue for most people.
When you start the Visual Basic Editor, it automatically loads the object libraries required for using VBA and user forms with the host application. You don't have to change this set of object libraries until you need to access objects contained in other libraries. For example, if you create a procedure in Word that needs to employ a feature found in Excel, you'll usually add to Word a reference to an Excel object library to make its objects available.
You can adjust the priority (or order of precedence) of different references by adjusting the order in which the references appear in the References dialog box. The priority of references matters when you use in your code an object whose name appears in more than one reference: VBA checks the References list to determine the order of the references that contain that object name and uses the first one unless specifically told to do otherwise by use of an unambiguous name.
To add or remove object libraries, follow these steps:
In the Visual Basic Editor, choose Tools
In the Available References list box, select the check boxes for the object libraries you want to have access to, and clear the check boxes for the references you want to remove because you don't need them. You should find a reference for an object library for each application that supports automation and is installed on your computer. Automation, in this context, means that an application permits the automation of tasks (in other words, macros). Another way to put this is: An application that supports automation exposes its objects, meaning that the application makes its objects available to programmers.
The references that are in use appear together at the top of the Available References list box, not in alphabetical order (in order of precedence, as described earlier in this chapter).
Adjust the order of precedence of the references if necessary by selecting a reference and using the up- and down-arrow Priority buttons to move it up or down the list. Usually, you'll want to keep Visual Basic for Applications and the object library of the application you're working with at the top of your list.
Click OK to close the References dialog box and return to the Object Browser.
To browse the objects available to a project, follow these steps:
First, activate a code module by double-clicking it in the editor's Project Explorer.
Display the Object Browser by choosing View
In the Project/Library drop-down list, select the name of the project or the library that you want to view. The Object Browser displays the available classes in the Classes list.
In the Classes list, select the class you want to work with. For example, if you chose a project in step 3, select the module you want to work with in the Classes list.
If you want to work with a particular member of the class or project, select it in the Members Of list. For example, if you're working with a template project, you might want to choose a specific procedure or user form to work with.
Once you've selected the class, member, or project, you can perform the following actions on it:
View information about it in the Details pane at the bottom of the Object Browser window.
View the definition of an object by clicking the View Definition button. Alternatively, right-click the object in the Members Of list and choose View Definition from the context menu. The View Definition button and the View Definition command are enabled (available, undimmed) only for objects that contain code, such as procedures and user forms that you've created.
Copy the text for the selected class, project, or member to the Clipboard by clicking the Copy button or by issuing a standard Copy command (pressing Ctrl+C orCtrl+Insert).
VBA's Help system provides another easy way to access the details of the objects you want to work with. The Help files provide a hyperlinked reference to all the objects, methods, and properties in VBA, including graphics that show how the objects are related to each other.
The quickest way to access VBA Help is to press the F1 key while working in the Visual Basic Editor. The editor displays the Visual Basic Help dialog box (shown in Figure 8.3 with some topics expanded to reveal their contents).
If the table of contents isn't visible in the left pane, open it by clicking the small book icon—second from the right in the Help window toolbar.
In the Table Of Contents pane, click Word Object Model Reference.
Note that VBA Help is context sensitive, so if you have a particular command or object selected in code, you usually see that object's help reference displayed. Or you can simply click to place the insertion point on a word in your code before pressing F1.
Once you've opened the Help dialog box, you can search for help by typing keywords into the Search box and clicking the Search button or by browsing the topics.
Click any link in the Help dialog box for a topic you want to see and it opens in the Help dialog box. Figure 8.4 shows an example of what you see if you display the topic for the Document object in Word.
Apart from the regular Help information you'll find in the Help window, a few items deserve comment here:
A useful drop-down list appears if you click the down-arrow symbol on the Search button. This list allows you to quickly specify what kind of help you want: templates, online, training (videos, for example), the developer reference, and so on.
If a See Also hyperlink appears at the bottom of the window, you can click it to display a list of associated topics (see Figure 8.5). For example, as you'd discover if you clicked on the hyperlink, one of the See Also topics from the Document Object Help screen is Help on the Document Object Members.
Click the Hide (or Show) Table Of Contents button at the top of the Help window to toggle the left pane on and off.
Climb back to previous topics by clicking one of the links in the hierarchical path displayed at the top of the right pane, or just click the Back button to retrace the steps you took to get to the currently displayed topic.
Click the Home button to go to the highest (most abstract) level in the Help system.
You've already used the Auto List Members feature a couple of times in the previous chapters. To recap, in VBA code—as with most other programming languages—objects and their members (properties and methods) are separated by periods. This punctuation helps you see the relationships between parent objects, child objects, and members. Notice the two periods in this code:
sngLinesPerPage = ActiveDocument.PageSetup.LinesPage
Figure 8.5. Click on a link to display a topic. Here, you can see that the Document object contains a plethora of other objects, including Bookmarks and Characters.
When you're entering a statement in the Visual Basic Editor and you type the period at the end of the current object, the Auto List Members feature displays a list of properties and methods appropriate to the statement you've entered so far. (Turn this feature on in the Visual Basic Editor by choosing Tools
Technically, there's a distinction between Auto List Members and a somewhat similar List Properties/Methods feature. The former feature is triggered by typing a period (.) following the name of an object in a line of code. The latter is triggered by pressing Ctl+J, or by right-clicking the name of an object in a line of code and choosing List Properties/Methods from the menu that appears. Of the two, I find Auto List Members more useful.
The Auto List Members feature provides a quick way of completing statements, but you need to know which object you should work with before you can work with its members. Sometimes using this feature is a bit like finding your way through a maze and being given detailed directions that end with the phrase, "But you can't get there from here."
Once you know the object from which to start, though, you can easily find the property or method you need. For example, to put together the statement Application.Documents(1).Close to close the first document in the Documents collection in Word, you could work as follows:
Place the insertion point on a fresh line in an empty procedure (between the
SubandEnd Substatements). Create a new procedure if necessary.Type the word application, or type appl and press Ctrl+spacebar to have the Complete Word feature complete the word for you.
Type the period (.) after Application. The Auto List Members feature displays the list of properties and methods available to the
Applicationobject.Choose the
Documentsitem in the Auto List Members list. You can scroll to it using the mouse and then double-click it to enter it in the Code window, scroll to it by using the arrow keys and enter it by pressing Tab, or type the first few letters of its name (to automatically locate it) and then enter it by pressing Tab. The latter method is shown in Figure 8.6, which uses Word.Type (1). after
Documents. When you type this period, the Auto List Members feature displays the list of properties and methods available to aDocumentobject. Note that without the(1), you're working with the documents collection, but as soon as you add the(1), you're then working with a specific document, namely the first one in the collection.Choose the
Closemethod in the Auto List Members list by scrolling to it with the mouse or with the ↓ key. Because this is the end of the statement, press the Enter key to enter the method and start a new line (rather than pressing the Tab key, which enters the method but continues the same line of code).
As you learned in Chapter 6, one of the data types available for variables in VBA is the Object type. You use an Object variable to represent an object in your code: Instead of referring to the object directly, you can employ the Object variable to access or manipulate the object it represents.
Here's one major benefit: Using Object variables makes your code easier to read. It's simpler to see which object a section of code is working with, especially when you're working with multiple objects in the same section of code. And when you're working with collections, object variables are often a necessity. Read on.
For example, say you create a procedure that manipulates the three open workbooks in Excel, copying a range of cells from one to the other two. If you have only those three workbooks open, you'll be able to refer to them directly as Workbooks(1), Workbooks(2), and Workbooks(3), respectively, because they'll occupy the first (and only) three slots in the Workbooks collection.
But if your procedure changes the order of the workbooks, closes one or more workbooks, or creates one or more new workbooks, things rapidly get confusing. If, however, you've created Object variables (named, say, xlWorkbook1, xlWorkbook2, and xlWorkbook3) to refer to those specific workbooks, it will be much easier to keep them straight. This is because no matter which workbook moves to first position in the Workbooks collection, you'll be able to refer to the object represented by the Object variable xlWorkbook1 and know that you'll be accessing the workbook you're after. In other words, when you create Object variables, you get to name them, using words that are more easily understood than index numbers. More important, once it's named, an Object variable's name does not change. Index numbers can change.
To create an Object variable, you declare it in almost exactly the same way as you declare any other variable, using a Dim, Private, or Public statement. For example, the following statement declares the Object variable objMyObject:
Dim objMyObject As Object
As normal for the Dim statement, if you use this declaration within a procedure, it creates a variable with local scope. If you use it in the declarations section at the top of a code sheet, it creates a variable with module-level private scope. Similarly, the Private and Public keywords create module-level private and public Object variables, respectively.
Once you've declared the Object variable, you can assign an object to it. (Assigning objects works a bit differently from the way you use just an equal sign to assign a value to an ordinary variable.) To assign an object to an Object variable, you use a Set statement. The syntax for a Set statement is as follows:
Setobjectvariable= {[New]expression|Nothing}
Here's how that syntax breaks down:
objectvariableis the name of the Object variable to which you're assigning the object.Newis an optional keyword that you can use to implicitly create a new object of the specified class. However, usually it's better to create objects explicitly and then assign them to Object variables rather than useNewto create them implicitly.expressionis a required expression that specifies or returns the object you want to assign to the Object variable.Nothingis an optional keyword that you assign to an existing Object variable to obliterate its contents and release the memory they occupied.
For example, the following statements declare the Object variable objMyObject and assign to it the active workbook in Excel:
Dim objMyObject As Object
Set objMyObject = ActiveWorkbookThe following statement uses the Nothing keyword to release the memory occupied by the objMyObject Object variable:
Set objMyObject = Nothing
What's different about declaring an Object variable versus declaring other types of variables is that not only can you declare the Object variable as being of the type Object and then use the Set command, you can also specify which type of object it is. For example, if an Object variable will always represent a Workbook object, you can declare it as being of the Workbook data type. The following statement declares the Object variable xlWorkbook1 as being of the Workbook data type:
Dim xlWorkbook1 As Workbook
Strongly associating a type with an Object variable like this has a couple of advantages. First, once you've strongly typed (as it's called) the Object variable, the Visual Basic Editor can provide you with full assistance for the Object variable, just as if you were dealing with the object directly. For example, once you've created that Object variable xlWorkbook1 of the Workbook object type, the Visual Basic Editor displays the Auto List Members drop-down list when you type that Object variable's name followed by a period, as shown in Figure 8.7.
Figure 8.7. When you strongly type your Object variables, you get the full benefit of the Visual Basic Editor's code-completion features for those Object variables.
Second, when you strongly type an Object variable, you make it a bit harder to get things wrong in your code. If you try to assign the wrong type of object to a strongly typed Object variable, VBA gives an error. For example, if you create a Worksheet Object variable in Excel, as in the first of the following statements, but assign to it a Workbook object, as in the second statement, VBA displays a "Type Mismatch" error message when you execute this code—as well it should:
Dim wksSheet1 As Worksheet Set wksSheet1 = ActiveWorkbook
Finding out at this testing stage that you've created a problem is usually preferable to finding out later (for example, when you go to manipulate the wksSheet1 object and discover it doesn't behave as you expect it to).
The main argument for not strongly typing an Object variable is that you might not be sure ahead of time (while writing the code) what kind of object that variable will eventually reference during execution or if the kind of object it will store may vary from one execution of the code to another. (If either is the case, your code will need to be flexible enough to accommodate objects of different types for the same Object variable.) Usually, though, you'll want to strongly type all your Object variables.
If you're not sure which object type to use for an Object variable, start by declaring the Object variable as being of the Object data type. Then run through the code a couple of times with the Locals window (View
Dim wks As Object Set wks = ActiveWorkbook.Sheets(1)
- Understand and use objects, properties, and methods
Contemporary programming employs a hierarchical method of organization known as object-oriented programming (OOP). At the very top of the hierarchy for any given application is the
Applicationobject. You go through this object to get to other objects that are lower in the hierarchy.- Master It
By using creatable objects, you can often omit the
Applicationobject when referencing it in code. What are creatable objects?
- Use collections of objects
Collections are containers for a group of related objects, such as the
Documentscollection ofDocumentobjects.- Master It
Are collections objects? Do they have methods and properties?
- Find objects, properties, and methods
The Visual Basic Editor offers several ways to locate objects' members and add them to your programming code. There's an extensive Help system, the Object Browser, a List Properties/Methods feature, and the Auto List Members tool.
- Master It
How do you employ Auto List Members to find out which properties and methods are available for Word's
Documentobject?
- Use Object variables to represent objects
You can create variables that contain objects rather than typical values like strings or numbers.
- Master It
What keywords do you use to declare an Object variable?