Spread 8.0 Documentation
Formula Property
Support Options
ActiveX Reference > ActiveX Properties > Formula Property

Glossary Item Box

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

C++

CString CSpreadSheet::GetFormula( );
void CSpreadSheet::SetFormula(LPCTSTR value);

Visual Basic

[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

© 2002-2014 ComponentOne, a division of GrapeCity. All Rights Reserved.