Formula and FormulaRC Properties

The Formula property returns or sets the formula or value for each cell in the range. The formula must be expressed in A1-style notation, and must include a leading equal sign.

For instance, the line:

Range("A1").Formula = "=Sum(A2:A3)" sets the formula in cell A1. The line:

Range("A1:C1").Formula = "=Sum(A2:A3)"

places the formula in cells A1:C1, but because the formula uses relative references, these references will be altered as usual. If we want to put the exact same formula in each cell, we must use an array, as in:

Range("A1:C1").Formula = _ Array("=Sum(A2:A3)", "=Sum(A2:A3)", "=Sum(A2:A3)")

We can also return an array using the Formula property. To illustrate, consider the worksheet in Figure 19-13. The code:

Dim a As Variant a = Range("A1:C2").Formula sets the Variant variable a to an array, so that, for instance, a(2,3) = 7. Note that the Formula property returns a Variant, so that a must be declared as a Variant.

Figure 19-13. Illustrating the Formula property

If a cell contains a constant, the Formula property returns that constant. We can also assign a constant to a cell by writing, for example:

If the cell is empty, then the Formula property returns an empty string. If the cell contains a formula, then the Formula method returns the formula as a string, as it would be displayed in the formula bar (including the equal sign).

If we set the Formula property (or the Value property) of a cell to a date, then Excel checks to see whether that cell is already formatted with one of the date or time formats. If not, Excel uses the default short date format.

The FormulaR1C1 property is the analog to the Formula property but accepts and returns formulas in R1C1 style.

0 0

Post a comment