Microsoft Office Tutorials and References
In Depth Information
The Comment Object: A Case Study
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
Notice that the first argument is omitted and that I specified the placeholder by using a comma.
Another approach, which makes your code more readable, is to use 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 arguments, all of which
are optional. 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.
The Comment Object: A Case Study
To help you better understand the properties and methods available for an object, I focus on a
particular object: the Comment object. In Excel, you create a Comment object when you choose
the Review➜Comments➜New Comment command to enter a cell comment. In the sections that
follow, you get a feel for working with objects.
Search JabSto ::

Custom Search