SSGetFormula, SSSetFormula, SSSetFormulaRange Functions

Applies To

fpSpread DLL control

Description

Set or return the formula of a cell, a column, a row, or the entire sheet, or set the formula for a block of cells.

Syntax

int SSGetFormula(HWND hWnd, SS_COORD Col, SS_COORD Row, LPTSTR lpFormula);

BOOL SSSetFormula(HWND hWnd, SS_COORD Col, SS_COORD Row, LPCTSTR Formula, BOOL BuildDependencies);

BOOL SSSetFormulaRange(HWND hWnd, SS_COORD Col, SS_COORD Row, SS_COORD Col2, SS_COORD Row2, LPCTSTR Formula, BOOL BuildDependencies);

C++

int TSpread::GetFormula(SS_COORD Col, SS_COORD Row, LPTSTR lpFormula);

BOOL TSpread::SetFormula(SS_COORD Col, SS_COORD Row, LPCTSTR Formula, BOOL BuildDependencies);

BOOL TSpread::SetFormulaRange(SS_COORD Col, SS_COORD Row, SS_COORD Col2, SS_COORD Row2, LPCTSTR Formula, BOOL BuildDependencies);

Parameters

The SSGetFormula function has the following parameters:

Parameter	Description
hWnd	Window handle of the fpSpread control
Col	Column number of cell
Row	Row number of cell
lpFormula	Buffer to hold formula

The SSSetFormula and SSSetFormulaRange functions have the following parameters:

Parameter	Description
hWnd	Window handle of the fpSpread control
Col	Column number of cell or column number of upper-left cell of the block if specifying a block for the SSSetFormulaRange function
Row	Row number of cell or row number of upper-left cell of the block if specifying a block for the SSSetFormulaRange function
Col2	Column number of lower-right cell of the block if specifying a block for the SSSetFormulaRange function
Row2	Row number of lower-right cell of the block if specifying a block for the SSSetFormulaRange function
Formula	Pointer to string containing cell formula
BuildDependencies	Determines when the dependency tables are rebuilt

This function is applied to the current sheet setting unless you first call the SSSetSheet function to specify the sheet for which you are calling this function. To determine the settings you want for the Col, Row, Col2, and Row2 parameters, see Using Column and Row Properties.

Remarks

Use the SSSetFormulaRange function to specify the formula for a block of cells.

When using the SSSetFormula and SSSetFormulaRange functions, do not set the Col parameter to SS_ALLCOLS and the Row parameter to SS_ALLROWS simultaneously.

For the SSSetFormula and SSSetFormulaRange functions, set the BuildDependencies parameter to TRUE to rebuild the dependency tables at the time the function occurs.

Note: For best efficiency, build the dependency tables after the last formula is assigned.

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 using the SSAddCustomFunction function. 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 SSSetRefStyle function. 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
3.1415*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, A12, A13)	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 formula using the SSGetFormula function 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.

Return Value

SSGetFormula: Length of the formula (in characters).
SSSetFormula, SSSetFormulaRange: TRUE if the function completes successfully; FALSE otherwise.

ActiveX Correspondence

Formula property