Excel VBA 24-Hour Trainer (2015)
Part II
Diving Deeper into VBA
Lesson 5: Object-Oriented Programming: An Overview
Lesson 6: Variables, Data Types, and Constants
Lesson 7: Understanding Objects and Collections
Lesson 8: Working with Ranges
Lesson 9: Making Decisions with VBA
Lesson 5
Object-Oriented Programming: An Overview
In Lesson 1, you saw a brief historical synopsis of VBA. One particular facet of VBA's evolution that is worth more explanation is object-oriented programming, or OOP.
Object-oriented programming came about in the 1980s as a new concept in computer programming. Its popularity grew over time and with good reason—OOP's original precepts are at the core of today's VBA programming language for Excel.
What “Object-Oriented Programming” Means
Visual Basic for Applications is an object-oriented programming language. The basic concept of object-oriented programming is that a software application (Excel in this case) consists of various individual objects, each of which has its own set of features and uses. An Excel application contains cells, worksheets, charts, pivot tables, drawing shapes—the list of Excel's objects is seemingly endless. Each object has its own set of features, which are called properties, and its own set of uses, called methods.
You can think of this concept just as you would the objects you encounter every day, such as your computer, your car, or the refrigerator in your kitchen. Each of those objects has identifying qualities, such as height, weight, and color. They each have their own distinct uses, such as your computer for working with Excel, your car to transport you over long distances, and your refrigerator to keep your perishable foods cold.
VBA objects also have their identifiable properties and methods of use. A worksheet cell is an object, and among its describable features (its properties) are its address, its height, its formatted fill color, and so on. A workbook is also a VBA object, and among its usable features (its methods) are its abilities to be opened, closed, and have a chart or pivot table added to it.
Therefore, we can say that object-oriented programming, upon which VBA is based, is a style of programming language that cares primarily about objects, and how those objects can be manipulated based on their intrinsic qualities.
The Object Model
The Excel object model is the heart and soul of how VBA is used in Excel. Although VBA is the programming language for Excel, it is also the programming language for Office applications in Word, Access, PowerPoint, and Outlook. Even though all these applications are programmable with VBA, they have their own programming needs because they are different software applications, and hence are designed to serve different functions. Excel does not receive e-mails as Outlook does, and Word does not produce reports from its own database tables as Access does.
Every VBA action you take in your Excel workbook sends a command through the Excel object model. The object model is a large list of objects that relate to Excel, such as worksheets, cells, ranges, and charts. The VBA code in your macro that adds a worksheet to the workbook will make sense to Excel because it is communicating with the objects that are recognized to be present in the Excel object model. For example, that same macro to add a worksheet would not work in Outlook. The Outlook object model does not include worksheets, because Outlook is an application that maintains e-mails and appointment calendars, not worksheets.
The object model of any VBA application is hierarchical by design. In the Excel object model, the Application object is at the top of the model because it is the entire Excel application. Under the Application object is a whole host of other objects, one of them being the Workbook object. Under Workbook is the Worksheet object, among many others, and under the Worksheet object are Range and Cell objects, and so on.
The result of this hierarchy is what drives the proper syntax for your VBA macros. For example, if you want to enter the word “Hello” in cell A1 of Sheet1 of the workbook you are currently working in, the line of code to handle that could be the following:
Application.ActiveWorkbook.Worksheets("Sheet1").Range("A1").Value = "Hello"
VBA is a smart language. It knows you are working in Excel if you are specifying a Workbook object. It also knows you are doing something in a workbook if you are specifying a Worksheet object. Therefore, the preceding line of code can be shortened to this:
Worksheets("Sheet1").Range("A1").Value = "Hello"
That can be shortened further if you are working on Sheet1 (that is, if Sheet1 is the active sheet) when the code line is executed. If the parent Worksheet object is not specified, VBA's default assumption is that you want the active worksheet to receive the word “Hello” in cell A1, and in that scenario the line of code would simply be this:
Range("A1").Value = "Hello"
A bit of theory on the subject of objects. In an object-oriented programming environment, VBA regards as an Excel object pretty much any element of the Excel application you can think of, whether it is a button, or a row, or a window—even the Excel application itself.
When you add an object to your workbook with VBA—for example, if you run a macro that creates a chart—VBA is at work behind the scenes, storing information about that Chart object, and assigning default values to its properties that were not specified in the macro. I mention this as a piece of good news, because with VBA filling in the blanks as it does, it's that much less about VBA you need to learn to start writing advanced macros. This advantage will become clearer as you progress into more complex programming techniques.
Properties
As noted earlier, VBA objects have inherent qualities, called properties, similar to any objects you may deal with in the real world. Properties define what the object looks like and how it acts. If you own a red bicycle, you can change its Color property by painting the bicycle a different color. For a Cell object on a worksheet, you can change its Color property by formatting the cell with a different fill color.
In VBA code, you refer to the property of an object by first referring to the object, then the property, separated by a dot. Following are examples of a few of the many properties belonging to the Cell, Worksheet, and Workbook objects.
This line of code would format the active cell's Locked property:
ActiveCell.Locked = True
The Name property of the Worksheet object represents the worksheet's tab name. For example, this expression in the Immediate window would return the name of the active worksheet:
? ActiveSheet.Name
This expression would change the Name property of the active worksheet to "Hello", and when executed would result in “Hello” being the active worksheet's new tab name:
ActiveSheet.Name = "Hello"
The following expression will change the Color property of the active worksheet's tab to yellow:
ActiveSheet.Tab.Color = vbYellow
Workbooks have a Saved property that indicates if the workbook has been saved since its most recent change. For example, if you save your workbook and then enter the following expression in the Immediate window, VBA will return True:
? ThisWorkbook.Saved
If you were to make some change to the workbook, such as entering a number in a cell, and immediately re-evaluate the expression ? ThisWorkbook.Saved, False would be returned because VBA knows that the workbook has not been saved since it was last changed.
Methods
Methods are actions that can be performed by objects. VBA objects have inherent behavioral abilities. Following are examples of Excel objects and some of their methods.
The Range object of A1:D10 can have its cells' contents cleared with the ClearContents method:
Range("A1:D10").ClearContents
Workbooks and worksheets can be activated with the Activate method:
Workbooks("Book1.xlsx").Activate
Worksheets("Sheet2").Activate
Here's a more complicated example, to call your attention to the fact that objects can contain objects, not just properties. Suppose you have three pivot tables on Sheet1, and you only want to refresh the pivot table named PivotTable2. As far as VBA is concerned, what you really want to refresh is the PivotCache object of the PivotTable2 object of the Sheet1 worksheet object. This line of code would accomplish that, using the Refresh method:
Worksheets("Sheet1").PivotTables("PivotTable2").PivotCache.Refresh
NOTE This multiple-object syntax might look daunting at first, but you can take some comfort in knowing that you've been writing VBA code in this manner since Day 1. All objects (except theApplication object, which is Excel itself) have a Parent property—that is, another object to which they belong. In many cases, you don't need to specify the Parent object because it is inferred by default. For example, if you are referring to cell A1 on your active worksheet, you do not need to (though you could) express it as ActiveSheet.Range("A1")—you only need to express it as Range("A1"). In the preceding example, however, pivot tables are embedded objects for which VBA requires you to specify the parent worksheet object. If all this talk of properties and methods is not clear yet, don't worry, it will all make perfect sense when you see the theory in action.
Collections
Some of the VBA programming you learn in later lessons involves the concept of collections, and it is a topic I'm touching on here. In object-oriented programming, a Collection is an object that contains a group of like objects. For example, there is a Worksheets collection object that is the entire group of Worksheet objects in your workbook. Even if one worksheet contains hundreds of formulas and another worksheet is totally empty, both those worksheets are like objects because they are both worksheets, and therefore they both are a part of the Worksheets collection.
As you'll see, invoking the Collection object in your code is a terrific way to take some action on all the objects in that collection, without needing to know anything specific about the collected objects. For example, say you want to add some boilerplate text to every comment on your worksheet. Employing a For . . . Each loop (loops are covered in Lesson 10) to edit every comment in the Comments collection would make the task simple because each comment would belong to the Comments collection, and you'd be confident knowing you hit all comments without needing to know what cells they are in.
NOTE A good rule of thumb in recognizing a Collections object is to notice that its name ends with the letter s, as a pluralized form of its singular object item name. Examples of this are the Namescollection of individual Name objects, the Charts collection of individual Chart objects, the Workbooks collection of individual Workbook objects, and so on.
Try It
This lesson provided an overview of object-oriented programming. There are no programming techniques to try based on the material in this lesson, but here are some important concepts to keep in mind:
1. Excel is replete with objects, such as workbooks, worksheets, and cells, and each object has its own set of properties that can be altered to suit your application project's design.
2. If you should need to refer to an object's container, such as when you refer to a worksheet in another workbook, just use the object's Parent property. All objects (except Application) have a Parent property that is the object within which they are contained. For example, if your active workbook object is Book2 but you want to refer to Sheet1 in Book1, you'd precede the Sheet1 object with its parent Book1 object name, like this:
Workbooks("Book1.xlsm").Worksheets("Sheet1").Range("A1").Value = "Hello"
3. The Application object indeed holds the highest order of Excel's objects, but as you will see, it also offers many useful methods and properties. The Application object provides the ability to insert worksheet functions (SUM, AVERAGE, VLOOKUP, and so on), as well as commands to control Excel's display options for worksheet gridlines, tabs, and window sizes.
REFERENCE There is no video or code download to accompany this lesson.