Formula Property
See Also DLL Example 1 Example 2 Example 3
Applies To
fpSpread control
Description
Sets or returns a formula for a cell. This property is available at run time only.
Syntax
CString CSpreadSheet::GetFormula( );
void CSpreadSheet::SetFormula(LPCTSTR value);
[form.]fpSpread.Formula[ = text$]
Remarks
You must set the Col and Row properties to specify a cell before you set the Formula property. This setting is applied to the current sheet setting unless you first set the Sheet property to specify the sheet for which you are setting the property.
Set the Formula property equivalent to a mathematical expression made up of cell or sheet references, operators, and functions (either provided or custom functions you define).
The cell type determines how data is handled with formulas. For example: If the formula adds two numbers such as A1+B1, and A1 is an edit cell, then the formula will not evaluate correctly. A1 and B1 should both be number type cells for the formula to evaluate correctly.
For a list of the operators and functions you can use in formulas, see Built-In Functions. If the control does not provide the logical or mathematical function you need, you can define your own. For instructions, see Creating and Using Custom Functions.
The fpSpread control can use absolute or relative cell references. You define the cell reference style for the control by using the SetRefStyle method. For more information, see Specifying Cell References. Note that your formula cannot contain both absolute and relative row or column references.
The following table contains examples of valid formulas:
| Function | Description |
|---|---|
| SUM(A1:A10) | Sums rows 1 through 10 in the first column |
| PI( )*C6 | π times the value in cell C6 |
| A# * G# | Value in cell in column A, this row, multiplied by the value in cell in column G, this row |
| (A1 + B1) * C1 | Adds the values in the first two cells and multiplies the result by the value in the third cell |
| IF(A1>5, A1*2, A1*3) | If the contents of cell A1 are greater than 5, then multiply the contents of cell A1 by 2, else multiply the contents of cell A1 by 3 |
Returning the value of the Formula property provides a string containing the written expression of the formula, for example, SUM(A1:B1). However, if you have changed the cell reference style to a style that cannot represent the formula, the control provides the formula with question marks as placeholders for cell references that cannot be represented.
For example, if you have defined relative cell references used in a formula in cell B1 as RC[-1]+R[-1]C, the formula is interpreted as add the value in the cell to the left (A1) to the value in the cell above ("B0"). The control treats the value in the cell "B0" as an empty cell. If you change the cell reference style to the A1 style, the formula becomes A1+B?, because the A1 style cannot represent cell "B0". However, the control still evaluates the formula as it would using the R1C1 reference style.
The formula uses invariant culture settings (decimalChar = "." and separatorChar = ","), when you use numbers in the actual formula string.
You can customize how the control handles formulas, as explained in Entering Formulas.
Spread Designer
Choose the Cell menu, then the Formula menu, and then the Formula tab, and then type a formula in the Formula edit box in the Cell Settings dialog box.
Data Type
String
See Also
Entering Formulas
Built-In Functions
AllowUserFormulas, AutoCalc, FormulaSync, Sheet properties
CustomFunction event
GetCustomName, GetRefStyle, IsFormulaValid, ReCalc, ReCalcCell, SetCustomName, SetRefStyle methods
DLL Correspondence
SSGetFormula, SSSetFormula, SSSetFormulaRange functions