Microsoft Office Tutorials and References

In Depth Information

**19.3.28 FormulaArray 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:

Range("A1").Formula = 1

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.

19.3.28 FormulaArray Property

The FormulaArray property returns or sets an array formula, which must be in R1C1 style, for a

range. To illustrate, consider the worksheet shown in
Figure 19-14
.
The code:

Range("A9:C11").FormulaArray = "=A1:C3 + A5:C7"

produced the values in cells A9:C11 in
Figure 19-14
. The formula on the left says to add the

contents of each cell in the uppermost 3-by-3 array to the corresponding cell in the middle 3-by-3

array, and place the result in the corresponding cell in the lower 3-by-3 array.

Figure 19-14. Illustrating the FormulaArray property

Note also that the code:

Debug.Print Range("A9").FormulaArray

prints the array formula: