Microsoft Office Tutorials and References
In Depth Information
Working with Range Objects
To display the value in the last cell in a worksheet (XFD1048576), use this statement:
You can also use this syntax with a Range object. In this case, the cell returned is relative to the
Range object referenced. For example, if the Range object is A1:D10 (40 cells), the Cells
property can have an argument from 1 to 40 and can return one of the cells in the Range object. In
the following example, a value of 2000 is entered into cell A2 because A2 is the fifth cell
(counting from the top, to the right, and then down) in the referenced range:
Range(“A1:D10”).Cells(5) = 2000
In the preceding example, the argument for the Cells property isn’t limited to values
between 1 and 40 . If the argument exceeds the number of cells in the range, the
counting continues as if the range were taller than it actually is. Therefore, a statement like
the preceding one could change the value in a cell that’s outside of the range A1:D10.
The statement that follows, for example, changes the value in cell A11:
Getting information from a cell
If you need to get the contents of a cell, VBA provides several properties. Following are the
most commonly used properties:
● The Formula property returns the formula, if the cell has one. If the cell doesn’t contain a
formula, it returns the value in the cell. The Formula property is a read/write property.
Variations on this property include FormulaR1C1 , FormulaLocal , and FormulaArray .
(Consult the Help system for details.)
● The Value property returns the raw, unformatted value in the cell. This property is a read/
● The Text property returns the text that is displayed in the cell. If the cell contains a
numeric value, this property includes all the formatting, such as commas and currency
symbols. The Text property is a read-only property.
● The Value2 property is just like the Value property, except that it doesn’t use the Date
and Currency data types. Rather, this property converts Date and Currency data
types to Variants containing Doubles . If a cell contains the date 12/6/2010, the Value
property returns it as a Date , while the Value2 property returns it as a double (for