Formula related procedures in class Range (Excel VBA)

This page presents procedures from class Range related to the theme Formula: Formula2, Formula2Local, Formula2R1C1, Formula2R1C1Local, FormulaArray, FormulaHidden, FormulaLocal, FormulaR1C1, FormulaR1C1Local and HasFormula

Formula2

Returns or sets a value that represents the object's formula in A1-style array notation.

This property is not available for OLAP data sources. In Dynamic Arrays enabled Excel, Range.Formula2 supercedes Range.Formula. Range.Formula will continue to be supported to maintain backcompatibility. A discussion on Dynamic Arrays and Range.Formula2 can be found here. If the cell contains a constant, this property returns the constant. If the cell is empty, this property returns an empty string. If the cell contains a formula, the Formula property returns the formula as a string in the same format that would be displayed in the formula bar (including the equal sign ( = )). If you set the value or formula of a cell to a date, Microsoft Excel verifies that cell is already formatted with one of the date or time number formats. If not, Excel changes the number format to the default short date number format. If the range is a one- or two-dimensional range, you can set the formula to a Visual Basic array of the same dimensions. Similarly, you can put the formula into a Visual Basic array. Formulas set using Range.Formula2 will always be evaluated as an array and may spill if more than 1 result is returned. Setting the formula for a multiple-cell range fills all cells in the range with the formula.

Worksheets("Sheet1").Range("A1").Formula = "=$A$4+$A$10"

Formula2Local

This is the Formula2 Variant of Range.FormulaLocal. See Range.Formula2 for more detail.

ActiveCell.Formula2Local =

Formula2R1C1

This is the Formula2 Variant of Range.FormulaR1C1. See Range.Formula2 and Range.FormulaR1C1 for more detail.

ActiveCell.Formula2R1C1 =

Formula2R1C1Local

This is the Formula2 Variant of Range.FormulaR1C1Local. See Range.Formula2 and Range.FormulaR1C1Local for more detail.

ActiveCell.Formula2R1C1Local =

FormulaArray

Returns or sets the array formula of a range. Returns (or can be set to) a single formula or a Visual Basic array. If the specified range doesn't contain an array formula, this property returns null.

The FormulaArray property also has a character limit of 255.

Worksheets("Sheet1").Range("A1:C5").FormulaArray = "=3"

FormulaHidden

Returns or sets a value that indicates if the formula will be hidden when the worksheet is protected.

This property returns True if the formula will be hidden when the worksheet is protected, Null if the specified range contains some cells with FormulaHidden equal to True and some cells with FormulaHidden equal to False. Don't confuse this property with the Hidden property. The formula will not be hidden if the workbook is protected and the worksheet is not, but only if the worksheet is protected.

ActiveCell.FormulaHidden = True

FormulaLocal

Returns or sets the formula for the object, using A1-style references in the language of the user.

If the cell contains a constant, this property returns that constant. If the cell is empty, the property returns an empty string. If the cell contains a formula, the property returns the formula as a string, in the same format in which it would be displayed in the formula bar (including the equal sign). If you set the value or formula of a cell to a date, Microsoft Excel checks to see whether that cell is already formatted with one of the date or time number formats. If not, the number format is changed to the default short date number format. If the range is a one- or two-dimensional range, you can set the formula to a Visual Basic array of the same dimensions. Similarly, you can put the formula into a Visual Basic array. Setting the formula of a multiple-cell range fills all cells in the range with the formula.

ActiveCell.FormulaLocal = "=A1"

FormulaR1C1

Returns or sets the formula for the object, using R1C1-style notation in the language of the macro.

If the cell contains a constant, this property returns the constant. If the cell is empty, the property returns an empty string. If the cell contains a formula, the property returns the formula as a string, in the same format in which it would be displayed in the formula bar (including the equal sign). If you set the value or formula of a cell to a date, Microsoft Excel checks to see whether that cell is already formatted with one of the date or time number formats. If not, the number format is changed to the default short date number format. If the range is a one- or two-dimensional range, you can set the formula to a Visual Basic array of the same dimensions. Similarly, you can put the formula into a Visual Basic array. Setting the formula of a multiple-cell range fills all cells in the range with the formula.

Worksheets("Sheet1").Range("B1").FormulaR1C1 = "=SQRT(R1C1)"

FormulaR1C1Local

Returns or sets the formula for the object, using R1C1-style notation in the language of the user.

If the cell contains a constant, this property returns that constant. If the cell is empty, the property returns an empty string. If the cell contains a formula, the property returns the formula as a string, in the same format in which it would be displayed in the formula bar (including the equal sign). If you set the value or formula of a cell to a date, Microsoft Excel checks to see whether that cell is already formatted with one of the date or time number formats. If not, the number format is changed to the default short date number format. If the range is a one- or two-dimensional range, you can set the formula to a Visual Basic array of the same dimensions. Similarly, you can put the formula into a Visual Basic array. Setting the formula of a multiple-cell range fills all cells in the range with the formula.

ActiveCell.FormulaR1C1Local =

HasFormula

True if all cells in the range contain formulas; False if none of the cells in the range contains a formula; null otherwise. Read-only Variant.

Dim booHasFormula As Boolean
booHasFormula = ActiveCell.HasFormula