Topics   All   Mac OS X (Only)   Windows (Only)   Linux (Only, Not)   iOS (Only, Not)  
Components   Crossplatform Mac & Win   Server   Client   Old   Guides   Examples
New in version: 8.0   8.1   8.2   8.3   8.4   8.5   9.0   9.1   9.2   9.3    Statistic  


Writes a formula into cell.

Component Version macOS Windows Server FileMaker Cloud FileMaker iOS SDK
XL 3.5 Yes Yes Yes Yes Yes
MBS( "XL.Sheet.CellWriteFormula"; bookRef; sheetIndex; Row; Column; Formula { ; Format } )   More


Parameter Description Example value
bookRef The reference to the workbook. Please use XL.LoadBook to load a file. $ref
sheetIndex The index of the sheet. This number ranges from 0 to XL.Book.SheetCount-1. 0
Row The row for the cell. First row has index 0. $row
Column The column for the cell. First column has index 0. $column
Formula The new formula to write. "1+2"
Format Optional
The format for the cell. This is index from 0 to XL.GetFormatCount-1 in the book's list of formats.


Returns OK or error message.


Writes a formula into cell.
As LibXL does not calculate the result of the formula, it will not encode the result in the Excel file. It does only store the formula, so an app opening the file will have to do the calculation. But QuickLook on Mac OS X does not calculate, so results do not show there.
See also XL.Sheet.CellReadFormula and XL.Sheet.CellIsFormula.
Examples for formulas are: "TODAY()", "IF(C1>0;ABS(C1*D1);"""")", "SUM(E16:E38)" or "E39+E39*E40".


Loads book, writes some cells and saves back to container field:

#Load template excel file
Set Variable [$bookRef; Value:MBS( "XL.LoadBook"; XL::ExcelFile)]
Set Variable [$sheet; Value:0]
#First row
Set Variable [$row; Value:4]
Set Variable [$r; Value:MBS( "XL.Sheet.CellWriteNumber"; $BookRef; $sheet; $row; 0; 1 )]
Set Variable [$r; Value:MBS( "XL.Sheet.CellWriteText"; $BookRef; $sheet; $row; 1; "Cherries" )]
Set Variable [$r; Value:MBS( "XL.Sheet.CellWriteNumber"; $BookRef; $sheet; $row; 2; 5 )]
#Second row
Set Variable [$row; Value:5]
Set Variable [$r; Value:MBS( "XL.Sheet.CellWriteNumber"; $BookRef; $sheet; $row; 0; 2 )]
Set Variable [$r; Value:MBS( "XL.Sheet.CellWriteText"; $BookRef; $sheet; $row; 1; "Banana" )]
Set Variable [$r; Value:MBS( "XL.Sheet.CellWriteNumber"; $BookRef; $sheet; $row; 2; 3,5 )]
#Third row
Set Variable [$row; Value:6]
Set Variable [$r; Value:MBS( "XL.Sheet.CellWriteNumber"; $BookRef; $sheet; $row; 0; 3 )]
Set Variable [$r; Value:MBS( "XL.Sheet.CellWriteText"; $BookRef; $sheet; $row; 1; "Oranges" )]
Set Variable [$r; Value:MBS( "XL.Sheet.CellWriteNumber"; $BookRef; $sheet; $row; 2; 6,25 )]
#Last row
Set Variable [$row; Value:13]
Set Variable [$r; Value:MBS( "XL.Sheet.CellWriteFormula"; $BookRef; $sheet; $row; 2; "SUM(C5:C13)" )]
#Save to container and cleanup
Set Field [XL::OutputFile; MBS("XL.Save"; $bookRef; "test.xls")]
Set Variable [$r; Value:MBS( "XL.Book.Release"; $bookRef)]
Set Field [XL::Result; $r]

Writes a formula with bold font format:

# add a bold font based on another font
Set Variable [ $textFontBold ; Value: MBS( "XL.Book.AddFont"; $book; $textFont) ]
Set Variable [ $r ; Value: MBS( "XL.Font.SetBold"; $book; $textFontBold; 1) ]
# add format based on other format
Set Variable [ $dollarFormatBold ; Value: MBS( "XL.Book.AddFormat"; $book; $dollarFormat) ]
Set Variable [ $r ; Value: MBS( "XL.Format.SetFont"; $book; $dollarFormatBold; $textFontBold) ]
# use format
Set Variable [ $r ; Value: MBS( "XL.Sheet.CellWriteFormula"; $book; $sheet; 38; 4; "SUM(E16:E38)"; $dollarFormatBold) ]

See also

Example Databases

Created 18th August 2014, last changed 12nd November 2018

XL.Sheet.CellWriteError   -   XL.Sheet.CellWriteFormulaBoolean

Feedback: Report problem or ask question.

MBS FileMaker tutorial videos