cell (counting from the top, to the right, and then down) in the referenced range:
Range("A1:D10").Cells(5) = 2000
NOTE
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 the range A1:D10. The statement that follows, for example, changes the value in cell A11:
Range("A1:D10").Cells(41) = 2000
The third syntax for the Cells property simply returns all cells on the referenced worksheet. Unlike the other two syntaxes, in this one, the return data isn't a single cell. This example uses the ClearContents method on the range returned by using the Cells property on the active worksheet. The result is that the content of every cell on the worksheet is cleared.
ActiveSheet.Cells.ClearContents
Getting information from a cell
If you need to get the contents of a cell, VBA provides several properties. The following are the most commonly used properties:
The Formula property returns the formula in a single cell, 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/write property.
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 5/1/2019, the Value property returns it as a Date, while the Value2 property returns it as a double (for example, 43586).
The Offset property
The Offset property, like the Range and Cells properties, also returns a Range object. But unlike the other two methods discussed, the Offset property applies only to a Range object and no other class. Its syntax is as follows:
object.Offset(rowOffset, columnOffset)
The Offset property takes two arguments that correspond to the relative position from the upper-left cell of the specified Range object. The arguments can be positive (down or to the right), negative (up or to the left), or 0. The example that follows enters a value of 12 into the cell directly below the active cell:
ActiveCell.Offset(1,0).Value = 12
The next example enters a value of 15 in the cell directly above the active cell:
ActiveCell.Offset(-1,0).Value = 15
If the active cell is in row 1, the Offset property in the preceding example generates an error because it can't return a Range object that doesn't exist.
The Offset property is useful, especially when you use variables in looping procedures. We discuss these topics in the next chapter.
When you record a macro using the relative reference mode, Excel uses the Offset property to reference cells relative to the starting position (that is, the active cell when macro recording begins). For example, we used the macro recorder to generate the following code. We started with the cell pointer in cell B1, entered values into B1:B3, and then returned to B1.
Sub Macro1() ActiveCell.FormulaR1C1 = "1" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "2" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "3" ActiveCell.Offset(-2, 0).Range("A1").Select End Sub
The macro recorder uses the FormulaR1C1 property. Normally, you want to use the Value property to enter a value in a cell. However, using FormulaR1C1 or even Formula produces the same result. Also, the generated code references cell A1—a cell that wasn't even involved in the macro. This notation is a quirk in the macro recording procedure that makes the code more complex than necessary. You can delete all references to Range ("A1"), and the macro still works perfectly.
Sub Modified_Macro1() ActiveCell.FormulaR1C1 = "1" ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = "2" ActiveCell.Offset(1, 0).Select ActiveCell.FormulaR1C1 = "3" ActiveCell.Offset(-2, 0).Select End Sub
In fact, you can enter this much more efficient version of the macro. In this version, you don't do any selecting.
Sub Macro1() ActiveCell = 1 ActiveCell.Offset(1, 0) = 2 ActiveCell.Offset(2, 0) = 3 End Sub
Essential Concepts to Remember
In this section, we cover some additional essential concepts for would-be VBA gurus. These concepts will become clearer when you work with VBA and read subsequent chapters:
Objects have unique properties and methods. Each object has its own set of properties and methods. Some properties and methods are common to various objects. For example, many objects in Excel have a Name property and a Delete method.
You can manipulate objects without selecting them. This idea may be contrary to how you normally think about manipulating objects in Excel. After all, to work with an object in Excel, you have to select that object manually first, right?Well, this is not so when using VBA. It's usually more efficient to perform actions on objects without selecting them first.However, when you record a macro, Excel records every step you take, including selecting objects before you work with them. These are unnecessary steps that may make your macro run more slowly. You can generally remove the lines of code in your recorded macro that selects objects.
It's important that you understand the concept of collections. Most of the time, you refer to an object indirectly by referring to the collection in which it's located. For example, to access a Workbook object named Myfile, reference the Workbooks collection as follows: Workbooks("Myfile.xlsx")
This reference returns an object, which is the workbook with which you're concerned.
Properties can return a reference to another object. For example, in the following statement, the Font property returns a Font object contained in a Range object. Bold is a property of the Font object, not the Range object.
Range("A1").Font.Bold = True
You can refer to the same object in many ways. Assume that you have a workbook named Sales, and it's the only workbook open. Then assume that this workbook has one worksheet, named Summary. You can refer to the sheet in any of the following ways:
