Microsoft Office Tutorials and References
In Depth Information
Properties and Methods
The VBA MsgBox function provides an easy way to display results while your VBA code
is executing. I use it extensively throughout this topic.
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 doesn’t 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:
Worksheets(“Sheet1”).Range(“A1”).Value = 123.45
After executing this routine, cell A1 on Sheet1 has the value 123.45.
You may want to enter these procedures into a module and experiment with them.
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
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 doesn’t have a formula,
The Formula property is a read-write property, so you can also specify a formula by using VBA:
Range(“D12”).Formula = “=RAND()*100”
In addition to properties, objects also have methods. A method is an action that you perform with
an object. Here’s a simple example that uses the Clear method on a Range object. After you
execute this procedure, A1:C3 on Sheet1 is empty, and all cell formatting is removed.