The Benefits of OOP

Building an application out of objects is called object-oriented programming (OOP). In theory, object-oriented programming offers a number of benefits to programmers—for example, the code is said to be easier to build and maintain (update) because you break it down into objects of a manageable size.

Again, in theory object-oriented programs are considered by some experts to 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.

Figuring out which commands to use to accomplish your programming goals can also be faster thanks to 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 these small pieces than to deal with some more complex command such as WordTableSetColumnWidth or WordTableGetColumnWidth. Note that this use of OOP is not the same as creating your own objects in your code. Instead, you're employing objects (essentially commands) that are already embedded within the VBA language itself.

A third benefit of object-oriented programming is that the VBA language itself can be extended. The programmer can build custom objects to implement functionality that the language itself didn't originally contain. For example, you can use VBA to build your own objects that do things that the Office applications themselves can't do.

Another, rather different, use for OOP is somewhat clerical: OOP can be of considerable help when a group of programmers are working together on a single program. They can quite easily step on each other's toes in various ways—using an earlier version than the others on the team, changing each other's code without communication, and so on. At the end of this chapter, we'll look at the ways OOP is employed to prevent these problems in team programming. This is perhaps the primary reason to program with OOP.

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 macros. It's similar to the way a biography is likely to be found in the library's nonfiction 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 itself 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 use 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 that 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").Select

Understanding Creatable Objects

The Application object, however, is optional and is usually left out of code. 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 something 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 (so far).

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 require that you type in Application. See the alternative example a couple of paragraphs earlier in this chapter.

Workbooks(1).Sheets(1).Range("A1").Select

Any object can have properties and methods. The next sections discuss these items in detail.

Properties

In VBA, a property is an attribute or characteristic of an object. Think of it as a quality of an object, such as its color. Most objects have multiple properties that specify each aspect of that object.

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 entire range of possibilities for the saved status of the object: it can either contain unsaved changes or not contain unsaved changes. There is no third state. A Boolean data type can, therefore, be used because that type has only two possible values.

Similarly, most objects that represent files have a Name property that contains the name of the file in question. The Name property contains a String data type because it needs to contain text. That text can be just about anything, limited only by the 255-character path that Windows permits for files and by certain characters—such as colons and pipe (|) characters—that Windows forbids in filenames.

To work with a property, you get (fetch or return) 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.) However, 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. Therefore, you can get (read, return, or fetch) the Name property but not set it. You'll also encounter some write-only properties, properties that you can set but not 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 Sub

Each 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.

Methods

A method is an action that an object can perform … a capability. 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 offer.

The Save method is very common. It appears in many applications' objects, as do other methods, such as SaveAs (which saves the file with a different name, location, or both) and Close (which closes the file).

Other methods are unique to each 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 in turn doesn't have an AcceptAllRevisions method.

Just as methods such as Save are common to multiple applications, some methods are 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. However, other implementations of the Delete method behave somewhat differently, depending on the object with which they're working. Therefore, 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 VBA functions such as MsgBox, some 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. However, you must specify the ActivePresentation object, like this:

ActivePresentation.Close

Working with an Object in a Collection

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 specify it in your code. It's assumed.

The following two statements return the same value, so there's no advantage to using the Item method:

strName = Documents(1).Name
strName = Documents.Item(1).Name

Adding an Object to a Collection

To create a new object in a collection, you add an object to the collection. In many cases, you use the Add method to do so. For example, the following statement creates a new Document object in Word:

Documents.Add

Using the Macro Recorder to Add Code for the Objects You Need

If you're using a Microsoft application, chances are that the easiest way to figure out what objects you need to use is to run the Macro Recorder and record a quick macro using the tools or menu items that interest you. While you perform various actions in the application, 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 to automate via a macro.

  Let's say you're working in Excel and want to create some macro code that performs an action on a specified workbook in the Workbooks collection rather than on the currently 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. The same thing is true for Word documents.)

  Here's another example: Some Ribbon actions are not recorded. In Word, clicking the Review ➢ Show Markup Formatting feature to deselect it results in no recorded code. To put that behavior into a macro, you would need to write the following code in the Editor yourself:

  ActiveWindow.View.ShowFormatChanges = False

• Second, the Macro Recorder is apt to include more statements than you actually 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 we'll record a macro to create an AutoCorrect entry. Let's say that you often have to type the word references in your job—dozens of times every day. You can speed up your work by merely typing reffs (or some other abbreviation of your choice). Then Word will automatically replace reffs with references as you type. Here's how to create this macro:

1. Start Word.
2. 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.

3. Type Add_Item_to_AutoCorrect in the Macro Name text box, and type a description in the Description text box.
4. 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.
5. Press Alt+F and then press T. Then click the Proofing button and the AutoCorrect Options button to display the AutoCorrect dialog box.
6. Type reffs in the Replace box and references in the With box, and click the Add button.
7. Then click OK twice to close both open dialog boxes.
8. Click the Stop Recording button on the Ribbon or the status bar to stop the Macro Recorder.
9. Now click Developer ➢ Macros 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 Sub

Here, the Recorder has created dozens of lines of unnecessary code. The only statement you actually need to accomplish your task is this:

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.

All 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 Sub

You 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.

Worst of all, these extra lines can set properties to conditions that you, or someone else using this macro, might not want. Let's say you run this macro in the future and you are working in a document that must not have any bullet symbols in it. You've clicked the File tab on the Ribbon, chosen File ➢ Options ➢ Proofing ➢ AutoCorrect Options ➢ AutoFormat As You Type, and turned off bullets. However, when you run this macro, bullets are turned back on by this unneeded line in the code:

 .AutoFormatAsYouTypeApplyBulletedLists = True

In spite of its limitations, the Macro Recorder does often provide quick access to the objects you need to work with, and you can always modify 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.

Using the Object Browser

For many programmers, the primary tool for writing code for objects is the Object Browser, which you used briefly in Chapter 4. In the following sections, you'll get to know the Object Browser better and learn to use it to find the information you need about objects. To see the Object Browser, press F2 in the Editor.

COMPONENTS OF THE OBJECT BROWSER

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 an object can perform, such as printing something)
• Events (things that can happen to an object. For example, the user opening or closing a document object)
• Constants (named items that keep a constant value while a program is executing)

Figure 8.1 shows the components of the Object Browser.

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 a 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 objects that it lists. Figure 8.1 shows several icons; Table 8.2 shows the full range of icons and what they represent.

ICON	MEANING
	Property
	User-defined type
	Method
	Global
	Constant
	Library
	Module
	Project
	Event
	Built-in keyword or type
	Class
	Enum (enumeration)

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.

Using Help to Find the Object You Need

VBA's online Help system provides another easy way to access the details of the objects you want to utilize. 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, and plenty of code samples to show you the correct syntax.

The quickest way to access VBA Help is to press the F1 key while working in the Visual Basic Editor.

PRESSING F1 TO GO TO A GENERAL VBA HELP PAGE

F1 works two different ways. Press F1 with the cursor (the blinking vertical line) on a blank line in the Code window, and you're taken to the VBA portal shown in Figure 8.3.

However, press F1 with the cursor on a language keyword in your code, such as Variant or InputBox, and you're taken to a Help page with specific information about that particular keyword.

First, try clicking a blank line in your Code window, and then press F1. Your browser opens a generic Office website like the one shown in Figure 8.3.

Take some time to familiarize yourself with the various useful links on this page. While we're on the subject of resources, also visit here:

https://stackoverflow.com/questions/tagged/vba

This website is an excellent place to search for answers, or post questions. And there are also quite a few code samples, too.

PRESSING F1 TO GO DIRECTLY TO A COMMAND'S HELP PAGE

The second way to use F1 takes you directly to the Help page for the keyword that interests you. If you want to see how to manipulate the active window, for example, just type activewindow into the Editor's Code window, and then, with the blinking insertion cursor somewhere in that word, press F1. See Figure 8.4.

After you press F1 on the activewindow command, as shown in Figure 8.4, the Help page for this command opens, as you can see in Figure 8.5.

Notice in Figure 8.5 that it's dated 2017. That simply means that this portal page's links are still valid and haven't needed revision since 2017.

Apart from the regular Help information you'll find in the Help pages online, here are a few additional ways to find help:

• At the top of most Microsoft Help windows, you'll see a search field (look for the magnifying glass symbol). Try typing Office 2019 into the search field. A page is displayed with plenty of links.
• If you want to read about a particular object, mention it first in your search term. Try selection object office. You'll see useful links that, at first, seem out of date. They are described as Office 2003. But below each link you're informed that the information was updated November, 2018 (Figure 8.6).

  

• When looking for help, you can also try clicking the Help menu in the Editor and then choosing one of the two help options listed: Microsoft Visual Basic for Applications Help or MSDN On The Web.

These two options open different entrees into the Help system, from which you can drill down until you locate the explanations or code samples you're after. (Note that although the first option listed displays F1, you are not taken to the same web page you get when actually pressing F1.)

• Finally, when looking for help with objects, remember that you can press F2 to display the built-in Object Browser.

Using the Auto List Members Feature

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, at the end of the line, the members. Notice the two periods in this code:

sngLinesPerPage = ActiveDocument.PageSetup.LinesPage

When you're entering a statement in the Visual Basic Editor and you type the period, the Auto List Members feature displays a list of properties and methods appropriate to the object. (If necessary, turn this feature on in the Visual Basic Editor by choosing Tools ➢ Options, and then selecting the Auto List Members check box.)

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:

1. Place the insertion point on a fresh line in an empty procedure (between the Sub and End Sub statements). Create a new procedure if necessary.
2. Type the word application, or type appl and press Ctrl+spacebar to have the Complete Word feature complete the word for you.
3. Type the period (.) after Application. The Auto List Members feature displays the list of properties and methods available to the Application object.
4. Choose the Documents item 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.
   • 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.7, which uses Word.

   

5. Type (1). after Documents.

   When you type this period, the Auto List Members feature displays the list of properties and methods available to a Document object. 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.

6. Choose the Close method in the Auto List Members list by scrolling to it with the mouse or with the down-arrow key.
7. 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).