Michael Alexander - Excel 2019 Power Programming with VBA

If a range is selected in a worksheet, the active cell is a cell within the selected range. In other words, the active cell is always a single cell (never a multicell range).

The Applicationobject also has a Selectionproperty that returns a reference to whatever is selected, which may be a single cell (the active cell), a range of cells, or an object such as ChartObject, TextBox, or Shape.

Table 2.1lists the other Applicationproperties that are useful when working with cells and ranges.

TABLE 2.1 Some Useful Properties of the Application Object

The advantage of using these properties to return an object is that you don't need to know which cell, worksheet, or workbook is active, and you don't need to provide a specific reference to it. This allows you to write VBA code that isn't specific to a particular workbook, sheet, or range. For example, the following instruction clears the contents of the active cell, even though the address of the active cell isn't known:

ActiveCell.ClearContents

The example that follows displays a message that tells you the name of the active sheet:

MsgBox ActiveSheet.Name

If you want to know the name and directory path of the active workbook, use a statement like this:

MsgBox ActiveWorkbook.FullName

If a range on a worksheet is selected, you can fill the entire range with a value by executing a single statement. In the following example, the Selectionproperty of the Applicationobject returns a Rangeobject that corresponds to the selected cells. The instruction simply modifies the Valueproperty of this Rangeobject, and the result is a range filled with a single value.

Selection.Value = 12

If something other than a range is selected (such as a ChartObjector a Shape), the preceding statement generates an error because ChartObjectand Shapeobjects don't have a Valueproperty.

The following statement, however, enters a value of 12into the Rangeobject that was selected before a non- Rangeobject was selected. If you look up the RangeSelectionproperty in the Help system, you find that this property applies only to a Windowobject.

ActiveWindow.RangeSelection.Value = 12

To find out how many cells are selected in the active window, access the Countproperty. Here's an example:

MsgBox ActiveWindow.RangeSelection.Count

Methods are the actions that can be performed with an object. It helps to think of methods as verbs. You can paint your house, so in VBA, that translates to something like house.paint.

A simple example of an Excel method is the Selectmethod of the Rangeobject.

Range("A1").Select

Another is the Copymethod of the Rangeobject.

Range("A1").Copy

Some methods have arguments that can dictate how they are applied. For instance, the Pastemethod can be used more effectively by explicitly defining the Destinationargument.

ActiveSheet.Paste Destination:=Range("B1")

An issue that often leads to confusion among new VBA programmers concerns arguments. Some methods use arguments to clarify further the action to be taken, and some properties use arguments to specify additionally the property value. In some cases, one or more of the arguments are optional.

Consider the Protectmethod for a workbook object. Check the Help system, and you'll find that the Protectmethod takes three arguments: Password, Structure, and Windows. These arguments correspond to the options in the Protect Structure and Windows dialog box.

If you want to protect a workbook named MyBook.xlsx, for example, you might use a statement like this:

Workbooks("MyBook.xlsx").Protect "xyzzy", True, False

In this case, the workbook is protected with a password (argument 1). Its structure is protected (argument 2) but not its windows (argument 3).

If you don't want to assign a password, you can use a statement like this:

Workbooks("MyBook.xlsx").Protect , True, False

The first argument is omitted, and we specified the placeholder by using a comma.

You can make your code more readable by using named arguments. Here's an example of how you use named arguments for the preceding example:

Workbooks("MyBook.xlsx").Protect Structure:=True, Windows:=False

Using named arguments is a good idea, especially for methods that have many optional arguments and also when you need to use only a few of them. When you use named arguments, you don't need to use a placeholder for missing arguments.

For properties (and methods) that return a value, you must use parentheses around the arguments. For example, the Addressproperty of a Rangeobject takes five optional arguments. Because the Addressproperty returns a value, the following statement isn't valid because the parentheses are omitted:

MsgBox Range("A1").Address False ' invalid

The proper syntax for such a statement requires parentheses as follows:

MsgBox Range("A1").Address(False)

You can also write the statement using a named argument:

MsgBox Range("A1").Address(RowAbsolute:=False)

These nuances will become clearer as you gain more experience with VBA.

Much of the work that you will do in VBA involves cells and ranges in worksheets. That being the case, let's take some time to use the Rangeobject as a case study on how to explore and get familiar with a specific object.

Open the Visual Basic Editor and then go up to the menu and click Help ➪ Microsoft Visual Basic for Applications Help. You'll be taken to the Microsoft Developer Network (MSDN) website. While on MSDN, search for the word Range to see the page for the Rangeobject. There you will discover that the Rangeobject exposes three properties that can be used to manipulate your worksheets via VBA.

The Range property of a Worksheet or Range class object

The Cells property of a Worksheet object

The Offset property of a Range object

The Rangeproperty returns a Range object. If you consult the Help system for the Rangeproperty, you learn that this property has two syntaxes.

object.Range(cell1) object.Range(cell1, cell2)