In addition to the characteristics of an object, the properties of an object include the Collections of other objects that belong to the object. That is why we can reference a collection by dot notation, e.g., ActiveWorkbook.Sheets.
Some properties return an object. In fact, a property that refers to a collection really returns a collection object. For example, when we access the Sheets property of a workbook by using the expression ActiveWorkbook.Sheets, it returns a Sheets object that is a collection of all the sheets in the workbook. The practical importance of this is that by getting the returned collection object, you have access to that collection object's properties and methods, as in Sheets.Count.
If you think of what you do when you use a dialog box in a program, you see that there are several different ways to enter a value (and thus set a property). An edit field represents a value the user enters in the edit field, such as row height; a checkbox represents true/false, such as wrap text for a cell; and a drop-down list represents a choice from a restricted set of values, such as horizontal alignment for a cell.
Examples of assigning a value to a property follow (the ActiveCell.HorizontalAlignment example requires a bit more explanation below, since it uses an enumeration, xlcenter).
Rows(5).RowHeight = 20
ActiveCell.WrapText = True
ActiveCell.HorizontalAlignment = xlCenter
Since we can get the value of a property, we can use the obtained value in setting a property elsewhere. For example, if we want to set the height of row 5 to the same height as row 8, we can code Rows(5).RowHeight = Rows(8).RowHeight. Note that this does not "link" the row heights. After the assignment statement is executed, subsquently changing the height of row 8 has no effect on the height of row 5.
The above assignment statement demonstrates the principle that you can use a property wherever you can use the value that the property returns (as in a condition expression, covered later in this tutorial).
You can use this number to set the value of the property, but numbers are hard to remember so they are almost never used. To make things simpler, VBA provides a verbal representation for each value for a property that has a limited set of possible values. These verbal representations are called enumerations. The macro code produced by recording a macro will have enumerations rather than numeric values for properties where the set of possible values is limited.
Enumerations in VBA for Excel start with the characters "xl". Most enumerations are easily understandable, as in the example above where "xlCenter" represents the value for "center" for alignment of text in a cell. When you are writing macros rather than recording them, you will need to include enumerations where necessary. Unfortunately, finding the specific enumeration you need is not always as easy as it should be in VBA Help. Usually it is simplest to record a macro, and then look at the resulting code to obtain the enumeration.
previous chapter, the methods of an object refer to actions that an object can take, or actions that can be done to it. Each object will have a set of methods appropriate to that particular object. An example of a method is ActiveWorkbook.Close. Using a object's method in a macro is referred to as "calling" or "invoking" the method.
You will sometimes see the term parameter used to refer to the values passed to a method. For the purposes of this tutorial the distinction between parameters and arguments is not crucial, but there is a difference. Parameters is the term used to describe the values to be passed, when the method is defined. When you look at the documentation for a method in Help, the method will be described in general terms, and its parameters will be given. When the method is called, the values passed to the method are its arguments. Another way of looking at it is that each item of information you provide has a name, called a parameter, and a value, called an argument.
We can use the following code as an illustration:
Sheets.Add Before:=Sheets(2), Count:=3
Looking at the Sheets.Add method, we find it has four parameters, named "Before," "After," "Count," and "Type." (The VBA documentation will give the data types for the arguments to be passed for each of the parameters.) All of the parameters of the Add method are optional. The documentation tells us that the default value for Count is 1, and the default value for Type is xlWorksheet (rather than a chart). The documentation also tells us that if both Before and After are omitted, a new worksheet will be added before the active sheet.
In the example above, we see that arguments are passed by using a colon and equal sign (":="); multiple arguments are separated by commas. Since the Type parameter was omitted, the method will use the default value for that parameter (xlWorksheet). The parameter After is omitted since we are using the Before parameter. Thus, the example code above will add 3 worksheets before the second worksheet in the active workbook.
Macro TUT2 that you recorded earlier should show the command Sheets.Add. Inserting a worksheet manually always inserts one worksheet before the active sheet. No arguments need to be passed to the Add method, since using all the default values causes the same result.
Strictly speaking, using the parameter names with passed arguments is optional. But if you do not use named arguments your code becomes much harder to read, you have to use placeholder commas to indicate an omitted optional argument, and you must pass the arguments in the order specified in the function definition. These disadvantages outweight the slight saving in time and effort in omitting the names. The moral is, always use named arguments for object methods.
Chapter 10 of this tutorial.