Читать книгу Excel VBA Programming For Dummies - Dick Kusleika - Страница 77

Every object has properties. You can think of properties as characteristics that describe the object. An object’s properties determine how it looks, how it behaves, and even whether it’s visible. Using VBA, you can do two things with an object’s properties:

 Examine the current setting for a property.

 Change the property’s setting.

For example, a single-cell Range object has a property called Value. The Value property stores the value contained in the cell. You can write VBA code to display the Value property, or you can write VBA code to set the Value property to a specific value. The following macro uses the VBA built-in MsgBox function to bring up a box that displays the value in cell A1 on Sheet1 of the active workbook (see Figure 4-1):

Sub ShowValue() Contents = Worksheets("Sheet1").Range("A1").Value MsgBox ContentsEnd Sub

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

MsgBox is a very useful function. You can use the MsgBox function, for example, to display results while Excel executes your VBA code. You find out more about this function in Chapter 15, so be patient (or flip ahead and read all about it).

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.

A collection is also an object. This means that a collection also has properties. For example, you can determine how many workbooks are open by accessing the Count property of the Workbooks collection. The following VBA procedure displays a message box that tells you how many workbooks are open:

Sub CountBooks() MsgBox Workbooks.CountEnd Sub

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