FORMULA Syntax 1

Macro Sheets Only

Enters a formula in the active cell or in a reference. If the active sheet is a worksheet, using FORMULA is equivalent to entering formula_text in the cell specified by reference. Formula_text is entered just as if you typed it in the formula bar.

There are two syntax forms of this function. Use syntax 1 to enter numbers, text, references, and formulas in a worksheet. Although syntax 1 can also be used to enter values on a macro sheet, you will not generally use FORMULA for this purpose. Use syntax 2 to enter a formula in a chart. For information about setting values on a macro sheet, see "Remarks" later in this topic.

Syntax

FORMULA(formula_text, reference)

Formula_text can be text, a number, a reference, or a formula in the form of text, or a reference to a cell containing any of the above.

• If formula_text contains references, they must be R1C1-style references, such as "=RC[1]*(1+R1C1)". If you are recording a macro when you enter a formula, Microsoft Excel converts A1-style references to R1C1-style references. For example, if you enter the formula =B2*(1+$A$1) in cell C2 while recording, Microsoft Excel records that action as =FORMULA("=RC[-1]*(1+R1C1)").
• If formula_text is a formula, the formula is entered. Text arguments must be surrounded by double sets of quotation marks. For example, to enter the formula =IF($A$1="Hello World", 1, 0) in the active cell with the FORMULA function, you would use the formula FORMULA|USA|002|001|001|common|UFORMULA("=IF|USA|002|001|001|common|UIF(R1C1=""Hello World"", 1, 0)")

  If formula_text is a number, text, or logical value, the value is entered as a constant.

Reference specifies where formula_text is to be entered. It can be a reference to a cell in the active workbook or an external reference to a workbook. If reference is omitted, formula_text is entered in the active cell.

Remarks

Consider the following guidelines as you choose a function to set values on a worksheet or macro sheet:

• Use FORMULA to enter formulas and change values in a worksheet cell.
• SET.VALUE changes values on the macro sheet. Use SET.VALUE to assign initial values to a reference and to store values during the calculation of the macro.
• SET.NAME creates names on the macro sheet. Use SET.NAME to create a name and immediately assign a value to the name.

Examples

If the active sheet is a worksheet, the following macro formula enters the number constant 523 in the active cell:

FORMULA(523)

If the active sheet is a worksheet, the following macro formula enters the result of the INPUT function in cell A5:

FORMULA(INPUT("Enter a formula:", 0), !$A$5)

If you're using R1C1-style references and the active sheet is a worksheet, the following macro formula enters the formula =RC[-1]*(1+R1C1) in the active cell:

FORMULA("=RC[-1]*(1+R1C1)")

If the active sheet is a worksheet, the following macro formulas enter the number 1000 in the cell two rows down and three columns right from the active cell. The R1C1-style formula is shorter, but the OFFSET method may provide faster performance in larger macro sheets.

FORMULA(1000, OFFSET(ACTIVE.CELL(), 2, 3))
FORMULA(1000, "R[2]C[3]")

The following macro formula enters the phrase "Year to Date" in cell B4 on the sheet named SALES 1993:

FORMULA("Year to Date", 'SALES 1993'!B4)

Related Functions

FORMULA.ARRAY Enters an array

FORMULA.FILL Enters a formula in the specified range

SET.VALUE Sets the value of a cell on a macro sheet

Syntax 2 Enters formulas in a chart

List of Command-Equivalent Functions