Object properties

Every object has properties. For example, a Range object has a property called Value. You can write VBA code to display the Value property or write VBA code to set the Value property to a specific value. Here's a procedure that uses the VBA MsgBox function to pop up a box that displays the value in cell A1 on Sheet1 of the active workbook:

Sub ShowValue()

Msgbox Worksheets("Sheet1").Range("A1").Value End Sub

Note The VBA MsgBox function provides an easy way to display results while your VBA code is executing. I use it extensively throughout this book.

The code in the preceding example displays the current setting of the Value property of a specific cell: cell A1 on a worksheet named Sheet1 in the active workbook. Note that if the active workbook does not have a sheet named Sheet1, the macro generates an error.

What if you want to change the Value property? The following procedure changes the value displayed in cell A1 by changing the cell's Value property:

Sub ChangeValue()

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

After executing this routine, cell A1 on Sheet1 has the value 123.45.

You might want to enter these procedures into a module and experiment with them.

Note Most objects have a default property. For a Range object, the default property is the Value property. Therefore, you can omit the .Value part from the preceding code, and it has the same effect. However, it's usually considered good programming practice to include the property in your code, even if it is the default property.

The statement that follows accesses the HasFormula and the Formula properties of a Range object. If Range("A1").HasFormula Then MsgBox Range("A1").Formula

I use an If-Then construct to display a message box conditionally: If the cell has a formula, then display the formula by accessing the Formula property. If cell A1 does not have a formula, nothing happens.

The Formula property is a read-write property, so you can also specify a formula by using VBA:

Range("D12").Formula = "=RAND()*100"

0 0

Post a comment