MESSAGE BOXES

To VBA, every item in the Excel environment is considered an object rather than an abstract set of data structures or an arrangement of pixels on the screen.

Objects can contain other objects.

At the top of the hierarchy, the largest object within the Excel object model is the Excel application. Objects contained within this largest container include workbooks. Workbooks contain worksheets and chart sheets, worksheets contain ranges (and can also contain chart objects), and so on.

When you record a macro, Excel follows the Sub statement with some comment lines. These lines, which always begin with the apostrophe character and are, by default, displayed in green, include the name and description of your macro as well as the keyboard shortcut you chose. These lines are ignored when you run the macro.

The first VBA statement in the CompanyAddress macro after the Sub statement and the comments is the following:

Range("A2").Select
This line illustrates an important characteristic of VBA code: The syntax of many statements specifies first an object and then an action. An object can be a range, a worksheet, a graphic object, a workbook, or any of the more than 100 types of objects in Excel. Here, we specify a range object—the absolute cell reference A2—and an action—select.

The behaviors, or sets of actions, that an object “knows” how to perform are called the methods of the object. Methods are like verbs. To understand this concept, imagine that we are programming a robotic dog through VBA. To cause the dog to bark, we might use this statement:
Dog.Bark
Robotic dogs, however, are (or ought to be) capable of more than just barking. For example, you might want the dog to be able to do the following actions:
Dog.Sit
Dog.RollOver
Dog.Fetch
The tricks our robodog can perform, such as barking, rolling over, and fetching, are its methods.

The list of methods an object can perform depends on the object. A range object, for example, supports almost 80 different methods that you can use to copy and paste cells, sort, add formatting, and so on.

Like objects in the “real” world, objects in VBA also have properties. If you think of objects as the nouns of VBA and methods as the verbs, properties are the adjectives. A property is a quality, characteristic, or attribute of an object, such as its color or pattern. Characteristics such as our robodog’s color (such as brown or black) in RGB values, size (big or small) in pounds, and noise level (quiet or constantly making barking noises) in decibels.

You set a property by following the name of the property with an equal sign and a value.

Continuing our robotic dog example, we could set the size of a 10 pound dog with
Dog.size = 10
For example, the following executable statement in our MyPolyAddress macro: ActiveCell.FormulaR1C1 = "Prof. Ming Leung" changes one of the properties, FormulaR1C1, of the active cell, setting that property to the value “Prof. Ming Leung”.

The remaining statements in the MyPolyAddress macro consist of three more cell-selection and text-entry couplets. The macro selects cells A3, A4 and A5 and enters text into each cell.

The Object Browser:

You can view the various types of objects, methods, and properties available to Excel by switching to the VBE.

To do so, select Tools, Macro, Visual Basic Editor (or press Alt+F11). Then choose View, Object Browser (or press F2). A window is then displayed on the right of the screen.

Select Excel from the dropdown list of libraries at the top of the Object Browser screen. On the left is a list of the various classes of objects available to Excel.

You can think of a class as a template or description for a type of object; a specific chart, for example, would be an object that is an instance of the Chart class.

In VBA, classes belong to a project or library. The Object Browser lists the object classes belonging to the library Excel. If you scroll down the classes and select a class—the Range class, for example—the right side of the Object Browser lists the properties and methods (called the members of the class) that belong to that object.

Collections of Objects:

You can have more than one instance of the same VBA object. Together, such instances comprise a collection.

Each instance in a collection of objects can be identified by either its index value (its position within the collection) or its name. For example, the collection of all sheets in a workbook is Sheets(), and a specific instance of a sheet, the third one in the collection, is Sheets(3).

If the third sheet were named Summary, it could also be identified as Sheets("Summary").

Manipulating Collections with For … Each:

In VBA, each item in a collection has its own index, but the index numbers for an entire collection are not necessarily consecutive. If you delete one instance of an object in a collection, the index values of the remaining instances might not be renumbered. For example, if you delete Sheets(3) from a collection of 12 sheets in a workbook, there’s no guarantee that Excel will renumber Sheets(4) through Sheets(12) to fill the gap.

In other programming languages, you might use a For … Next construction such as the following to repeat an operation many times:

For n = 1 to 12 ' Activate each sheet
    Sheets(n).Activate
Next n
If you run this code in a VBA macro after deleting Sheets(3), VBA displays an error message and stops the macro because Sheets(3) no longer exists.

To allow for nonconsecutive indexes, VBA offers For Each … Next, a control structure that applies a series of statements to each item in a collection, regardless of the index numbers.

For example, suppose you’d like to label each sheet in the active workbook by entering the text Sheet 1, Sheet 2, and so on, in cell A1 of each sheet. As you won’t, in general, know how many sheets there are in any given workbook, you might use the following VBA macro:
Sub EnterSheetNum()
    n = 0
        for Each Sheet In Sheets()
        n = n + 1
        Sheet.Activate
        Range("A1").Select
        ActiveCell.FormulaR1C1 = "Sheet" + Str(n)
    Next
End Sub