Michael Alexander - Excel 2019 Power Programming with VBA

FIGURE 2.15 The General tab of the Options dialog box

Figure 2.16shows the Docking tab. These options determine how the various windows in the VBE behave. When a window is docked, it is fixed in place along one of the edges of the VBE program window. This makes it much easier to identify and locate a particular window. If you turn off all docking, you have a big, confusing mess of windows. Generally, the default settings work fine.

FIGURE 2.16 The Docking tab of the Options dialog box

VBA 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. An Excel application contains workbooks, worksheets, cells, charts, pivot tables, shapes, and the list goes on. Each object has its own set of attributes, which are called properties , and its own set of actions, called methods .

You can think of this concept just as you would of the objects you encounter every day, such as your computer or your car. Each of those objects has attributes, such as height, weight, and color. They also have their own distinct actions. For instance, your computer boots up, and your program starts.

VBA objects also have their identifiable attributes and actions. A workbook, for example, is an object with attributes (properties), such as its name, the number of worksheets it contains, and the date it was created. A workbook object also has actions (methods) such as Open, Close, and Save.

In Excel, you deal with objects such as workbooks, worksheets, and ranges on a daily basis. You likely think of each of these “objects” as all part of Excel, not really separating them in your mind. However, Excel thinks about these internally as all part of a hierarchical model called the Excel Object Model. The Excel Object Model is a clearly defined set of objects that are structured according to the relationships among them.

In the real world, you can describe everything you see as an object. When you look at your house, it is an object. Your house has rooms; those rooms are also separate objects. Those rooms may have closets. Those closets are likewise objects. As you think about your house, the rooms, and the closets, you may see a hierarchical relationship among them. Excel works in the same way.

In Excel, the Application object is the all-encompassing object—similar to your house. Inside the Application object, Excel has a workbook. Inside a workbook is a worksheet. Inside that is a range. These are all objects that live in a hierarchical structure.

To point to a specific object in VBA, you can traverse the object model. For example, to get to cell A1 on Sheet 1, you can enter this code:

Application.ThisWorkbook.Sheets("Sheet1").Range("A1").Select

In most cases, the object model hierarchy is understood, so you don't have to type every level. Entering this code also gets you to cell A1 because Excel infers that you mean the active workbook and the active sheet:

Range("A1").Select

Indeed, if you have your cursor already in cell A1, you can simply use the ActiveCellobject, negating the need to spell out the range.

Activecell.Select

Many of Excel's objects belong to collections. Your house sits within a neighborhood, for example, which is a collection of houses called a neighborhood. Each neighborhood sits in a collection of neighborhoods called a city. Excel considers collections to be objects themselves.

In each Workbook object, you have a collection of Worksheets. The Worksheets collection is an object that you can call upon through VBA. Each worksheet in your workbook lives in the Worksheets collection.

If you want to refer to a worksheet in the Worksheets collection, you can refer to it by its position in the collection as an index number starting with 1 or by its name as quoted text. If you run these two lines of code in a workbook that has only one worksheet and that worksheet is called MySheet, they both do the same thing:

Worksheets(1).Select Worksheets("MySheet").Select

If you have two worksheets in the active workbook that have the names MySheetand YourSheet, in that order, you can refer to the second worksheet by typing either of these statements:

Worksheets(2).Select Worksheets("YourSheet").Select

If you want to refer to a worksheet in a workbook called MySheetin a particular workbook that is not active, you must qualify the worksheet reference and the workbook reference. Here's an example:

Workbooks("MyData.xlsx").Worksheets("MySheet").Select

Properties are essentially the characteristics of an object. Your house has a color, a square footage, an age, and so on. Some properties can be changed, like the color of your house. Other properties can't be changed, like the year your house was constructed.

Likewise, an object in Excel, like the Worksheetobject, has a sheet name property that can be changed, and a Rows.Countrow property that cannot.

You refer to the property of an object by referring to the object and then the property. For instance, you can change the name of your worksheet by changing its Nameproperty.

In this example, you are renaming Sheet1to MySheet:

Sheets("Sheet1").Name = "MySheet"

Some properties are read-only, which means you can't assign a value to them directly—for instance, the Textproperty of a cell. The Textproperty gives you the formatted appearance of value in a cell, but you cannot overwrite or change it.

Some properties have arguments that further specify the property value. For instance, this line of code uses the RowAbsoluteand ColumnAbsolutearguments to return the address of cell A1 as an absolute reference ( $A$ 1).

MsgBox Range("A1").Address(RowAbsolute:=True, ColumnAbsolute:=True)

When you're working with Excel, only one workbook at a time can be active. In that workbook, only one sheet can be active. And if the sheet is a worksheet, one cell is the active cell (even if a multicell range is selected). VBA knows about active workbooks, worksheets, and cells, and it lets you refer to these active objects in a simplified manner.

This method of referring to objects is often useful because you won't always know the exact workbook, worksheet, or range on which you want to operate. VBA makes object referencing easy by providing properties of the Applicationobject. For example, the Applicationobject has an ActiveCellproperty that returns a reference to the active cell. The following instruction assigns the value 1to the active cell:

ActiveCell.Value = 1

In the preceding example, we omitted the reference to the Applicationobject and to the active worksheet because both are assumed. This instruction will fail if the active sheet isn't a worksheet. For example, if VBA executes this statement when a chart sheet is active, the procedure halts and you get an error message.