Excel VBA Programming For Dummies. Dick Kusleika

Чтение книги онлайн.

      FIGURE 4-1: This message box displays a Range object’s Value property.

      The code in the preceding example displays the current setting of a cell’s Value property. What if you want to change the setting for that property? The following macro changes the value in cell A1 by changing the cell’s Value property:

       Sub ChangeValue() Worksheets("Sheet1").Range("A1").Value = 994.92End Sub

      After Excel executes this procedure, cell A1 on Sheet1 of the active workbook contains the value 994.92. If the active workbook doesn’t have a sheet named Sheet1, the result of executing that macro is an error message. VBA just follows instructions, and it can’t work with a sheet that doesn’t exist.

      Each object has its own set of properties, although some properties are common to many objects. For example, many (but not all) objects have a Visible property. Most objects also have a Name property.

      Some object properties are read-only properties, which means that your code can get the property’s value, but it can’t change it. For example, a Workbook object has a Name property that returns the workbook’s name. You can’t change the Name property directly because it’s read-only. To change a workbook’s name, you must use the Save or SaveAs methods.

       Sub CountBooks() MsgBox Workbooks.CountEnd Sub

      For more about collections, see the previous section, “Referring to objects.”

      Taking action with object methods

      In addition to properties, objects have methods. A method is an action you perform with an object. A method can change an object’s properties or make the object do something.

      This simple example uses the ClearContents method on a Range object to erase the contents of 12 cells on the active sheet:

       Sub ClearRange() Range("A1:A12").ClearContentsEnd Sub

      Some methods take one or more arguments. An argument is a value that further specifies the action to perform. You place the arguments for a method after the method, separated by a space. Multiple arguments are separated by a comma.

      The following example activates Sheet1 (in the active workbook) and then copies the contents of cell A1 to cell B1 by using the Range object’s Copy method. In this example, the Copy method has one argument, which is the destination range for the copy operation:

       Sub CopyOne() Worksheets("Sheet1").Activate Range("A1").Copy Range("B1")End Sub

      Notice that the worksheet reference is omitted when referring to the Range objects. You can do this safely due to the statement to activate Sheet1 (using the Activate method).

Another way to specify an argument for a method is to use the official name of the argument followed by a colon and an equal sign. Using named arguments is optional, but doing so can often make your code easier to understand, particularly if the method takes many parameters. The second statement in the CopyOne procedure could be written like this:

       Range("A1").Copy Destination:=Range("B1")

Figure 4-2 shows the little prompt that appears as I type a statement. That prompt indicates the official name of the argument.