Читать книгу Excel 2019 Power Programming with VBA - Michael Alexander, Dick Kusleika - Страница 111

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 Protect method for a workbook object. Check the Help system, and you'll find that the Protect method 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 Address property of a Range object takes five optional arguments. Because the Address property 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.