Modifying object properties

Knowing how to refer to objects is only the first step in working with them. In this recipe, we will be learning how to modify the properties of objects. You can think of properties as attributes that describe an object. In order to use them to do something of value, you must be able to do the following:

• Read an object's properties and then modify them.
• Specify a method of action to be used with that object (more about this in the next recipe).

Getting ready

Make sure that a blank workbook is active in Excel.

How to do it…

Let's see the steps for this recipe:

1. With Excel open, press Alt + F11 to activate the VBA Editor.
2. In the Project window of Explorer, double-click on Sheet1 under Book1. The corresponding code window will appear:
   

   Figure 3.3 – The code window in the VBA Editor

3. Type the following code:

   Sub ChangeValue()

         Worksheets("Sheet1").Range("A1").Value = 555

   End Sub

4. Press F5 to run the code, and then press Alt + F11 to switch back to Excel.
5. The cell A1 of Sheet1 now contains the value 555.

How it works…

Here's how the preceding steps worked out:

1. Cell A1 was empty. In other words, its value property was zero.
2. The ChangeValue Sub procedure first activated Sheet1, then cell A1, and finally set the value of cell A1 to 555.
3. By running the macro, we changed the value property of cell A1 from empty to 555:

Figure 3.4 – The new value property in cell A1

There's more…

With the preceding sample, we've only scratched the surface. Other properties, such as bold or italic, font color, font size, alignment, and many more, can be set in the same way.

The following code sample is extremely clumsy, but I need to make a point here.

First, clear cell A1. Then type any value into cells B1 and C1 of Sheet1. When done, activate the VBA Editor, and add the following lines to the existing Sub procedure:

Sub ChangeValue()

      Range("A1").Value = 555

      Range("B1").Font.Color = -16776961

      Range("C1").Font.Bold = True

End Sub

Run the macro.

Cell A1's value is once again set to 555, while cell B1's font color has changed to red. Similarly, cell C1's font weight is set to bold.