Formulas are saved in OLJ and OLB files.
To create a column formula, click Column in the Formula tab. You can set a column formula to be the default value for the column.
Different formulas have different names. For example, the formula for cell B10 is named FormulaCellB10.
Function FormulaCellB10(nCol, nRow)
FormulaCellB10 = "Value"
The global formula function is named Formula.
Formulas return "Value" by default. You can return a different value by setting FormulaName=myvalue.
All formulas are sent nCol and nRow parameters.
You can access other cells with Grid.Cell(nCol, nRow). For example, the cell to the left is:
You can also access columns by name. For example:
gets the Customer value in the same row.
You can click Type in the Formula tab to change formula types (for example: from a cell formula to a column formula). You can also remove a formula. If you convert a formula to None it is calculated once and then converted to text.
When formulas are active FX displays in the status bar.
Function FormulaColTimestamp(nCol, nRow)displays date and time.
FormulaColTimestamp = date & " " & time
You can access any number of cells in your (aggregate) function using for-next loops.
For example, the following cell formula (A10) calculates a total from the cells above it:
Function FormulaCellA10(nCol, nRow)
For nr = 1 To nRow - 1
On Error Resume Next 'ignore non-numbers
FormulaCellA10=fTotal 'modify to match the formula name if not A10
If you wanted an average instead of a total replace FormulaCellA10=fTotal with FormulaCellA10=fTotal/(nRow-1):
You cannot use Grid.Cell to read a cell that is itself a formula. Instead, call the formula function for that cell directly. If you attempt to read a formula cell you will get a Script reentry evaluating cell error.
You can dowload a sample here: formulagrandtotal.olz.
Formulas are calculated as they are displayed on the screen.
If you have a very large Offline document (for example: 100,000 rows) only the formulas that you see on the screen are calculated.
However if you convert a formula to text, the entire column is first calculated. This allows you to disable a formula but keep the value.
All rows are are also calculated if you synch Write to a data file or server. This allows you to update a data column from a formula.
SQL Offline © 2014 Interscape Corporation