SheetXL - v0.8.0-beta.9+sites.210.e2d420901
    Preparing search index...

    Interface IFormula

    Represents a formula that can be evaluated.

    interface IFormula {
        isIFormula: true;
        autoCorrect(): IFormula | null;
        evaluate(
            options?: number | EvaluateOptions,
        ): Promise<Scalar | IRange<Scalar>>;
        getAnchor(): CellCoords | null;
        getCalculationStatus(): ICalculation.ResultStatus;
        getExpression(options?: FormulaTextOptions): string;
        getReferences(): ICellRanges | null;
        getTokens(evalIndex?: number): readonly FormulaToken[];
        hasRefError(): boolean;
        isCompiled(): boolean;
        isReferenceOnly(): boolean;
        isValid(): boolean;
        isVolatile(): Promise<boolean>;
        setReferenceAt(
            index: number,
            reference: ICellRange.Address,
            options?: string | ITransaction.OperationOptions,
        ): Promise<IFormula>;
        setReferenceModeAt(
            index: number,
            side?: ReferenceSide,
            mode?: number,
            options?: string | ITransaction.OperationOptions,
        ): Promise<IFormula>;
        shift(target: CellCoords): IFormula | null;
    }

    Hierarchy (View Summary)

    Index
    isIFormula
    autoCorrect evaluate getAnchor getCalculationStatus getExpression getReferences getTokens hasRefError isCompiled isReferenceOnly isValid isVolatile setReferenceAt setReferenceModeAt shift
    isIFormula: true

    For runtime type checking.

    • Attempt Excel-style Formula AutoCorrect on an invalid formula.

      Returns IFormula | null

      A corrected, DETACHED formula (not committed anywhere — the same shape ICalculation.createFormula returns, anchored at this formula's cell), or null when this formula is already valid or no safe correction exists.

      Currently ONE deliberately narrow rule: append the missing trailing closers (], ), }) — =SUM(A1:A2 → =SUM(A1:A2), =SUM(Sales[Amount → =SUM(Sales[Amount]) — and only when the repaired text parses clean. Excel closes parens and braces but not a structured reference's brackets. A repair that would still be invalid (=SUM( → =SUM() fails arity) is never returned, nor is anything attempted for over-closed / interleaved brackets or an unterminated string. Never mutates this formula. Interactive hosts call this at commit; programmatic writes (setValue, file load) never auto-correct.

    • Return the value for a formula.

      Parameters

      Returns Promise<Scalar | IRange<Scalar>>

      ICalculation.FormulaToken.evalIndex

    • The cell this formula is anchored at — the cell its relative references are written from.

      Returns CellCoords | null

      A1 in a formula anchored at B1 means "the cell to my left"; the same text anchored at B6 means "five rows up and one left". The anchor is what makes that text unambiguous, and a caller re-siting a formula (shift) needs it to know where it is starting from.

      null for a formula with no cell of its own — a temporary formula created without coords, which evaluates in a virtual space.

    • Return the formula's expression — the text WITHOUT a leading =.

      Parameters

      Returns string

      One shape for every kind of formula, a cell's and a defined name's alike. It is the STORED shape, which is what OOXML <f> and <definedName> hold and what setExpression takes back, so read/modify/write needs no adjusting in between.

      The form with the leading = — what Excel's API answers (Range.Formula) and what a cell is entered with — is ICell.getFormulaText.

      The text is the storage form (English names, , separators) whatever language or locale is active. For the form a person reads or edits, pass { target: 'display' }.

      // With a German language pack active:
      formula.getExpression();                       // 'SUM(A1,1.5)'
      formula.getExpression({ target: 'display' });  // 'SUMME(A1;1,5)'

    • Returns an ordered list of ranges referenced in this formula.

      Returns ICellRanges | null

      • Returns null if there are no references.
      • These are the resolved ranges from setReferenceAt()

    • Return the formula as an array of tokenized values.

      Parameters

      • OptionalevalIndex: number

        Allows for a partial evaluation based on a token index.

      Returns readonly FormulaToken[]

      These can be used by advanced editors.

      ICalculation.FormulaToken.evalIndex

    • Whether any reference in this formula is unresolvable — #REF!.

      Returns boolean

      Asks the compiled references, not the text: a "#REF!" STRING literal in the formula is not a reference error. A reference degrades to one when its target is deleted, or when a shift pushes a relative reference above or left of the sheet.

    • Whether the calculation engine has compiled this formula.

      Returns boolean

      A formula is model state: it is stored, and reads back, the moment it is set - in every engine state, a stopped or never-started one included. The COMPILED body arrives when the engine gets to it. Until then this is false; getExpression answers from the text as written, getTokens is empty, getReferences is null, a reference edit throws, and evaluate waits for a Starting engine and rejects on an Off one rather than starting it. The handle heals: it reads the engine's block the first time one exists, so one taken cold is fully functional once the engine has compiled the cell.

      A formula created detached (ICalculation.createFormula) or a defined name is compiled on creation and is always true.

    • Returns true if the formula consists entirely of cell or range references and range operators (:, , #, etc.) with no function calls or computed expressions.

      Returns boolean

      A pure-reference formula always resolves to a range, making it suitable for use as a navigation target. Named formulas that return true can meaningfully implement getRanges().

    • Whether the formula text parsed without error. false for an in-progress / malformed formula (=SUM(, =A1:, =1+) — the parser recovered a best-effort token stream but the input isn't a complete expression. This is a PARSE concern only: a well-formed formula that evaluates to an error (=1/0) is still valid. Lets an editor flag "more input expected" without re-parsing.

      Returns boolean

    • Returns true if this formula contains one or more volatile functions (e.g. NOW, RAND, OFFSET, INDIRECT).

      Returns Promise<boolean>

      • Volatile formulas are re-evaluated on every calculation cycle regardless of whether their inputs have changed.
      • This is a runtime property tracked by the calculation engine — it cannot be derived from static AST analysis alone.
      • Named formulas where isVolatile() is true should not be used as navigation targets even if isReferenceOnly() returns true, since their resolved range may differ by context.
      • Asked of the engine: resolves in order behind the writes made before it, and false while the engine is Off.

    • Replaces a specific reference in the formula with a new one.

      Parameters

      • index: number

        The zero-based index of the reference to be updated (from getReferences()).

      • reference: ICellRange.Address

        The new address or range to occupy that position.

      • Optionaloptions: string | ITransaction.OperationOptions

        Optional transaction or operation options.

      Returns Promise<IFormula>

      Error is index is out of bounds relative to getReferences().

      This is the primary way to "refactor" a formula—for example, if a user drags a highlighted range in the UI to a new location.

    • Cycles the absolute/relative ($) style of the reference at the specified index.

      Parameters

      • index: number

        The zero-based index of the reference to be cycled (from getReferences()).

      • Optionalside: ReferenceSide

        Which part of the reference to cycle (relative to a range operator :):

        • 'self' (default) — the reference has no range operator (a single cell); cycles the whole reference as a unit, keeping it a single cell.
        • 'left' — the left operand of a range (the "start" corner).
        • 'right' — the right operand of a range (the "end" corner). A single-cell range (e.g. A1:A1) is still a range — it has a 'left' and a 'right', so it is NOT 'self'. That distinction only survives in the tokens (see FormulaToken.refSide).
      • Optionalmode: number

        Optional target mode. If omitted, cycles to the next mode in the sequence.

      • Optionaloptions: string | ITransaction.OperationOptions

        Optional transaction or operation options.

      Returns Promise<IFormula>

      The modes are:

      • 0: Absolute (e.g., $A$1)
      • 1: Absolute row, relative column (e.g., A$1)
      • 2: Absolute column, relative row (e.g., $A1)
      • 3: Relative (e.g., A1)
      • This is the F4 Cycle behavior commonly found in spreadsheet editors.

    • This formula as it would read at target: a DETACHED copy, filled.

      Parameters

      • target: CellCoords

        The cell the copy is anchored at.

      Returns IFormula | null

      The filled copy, or null when this formula has no compiler behind it to mint one.

      Excel's fill/copy, not its move: relative references follow the formula to its new home and absolute ($) ones stay put, so the copy means at target what this one means at its own anchor. A1*2 anchored at B1, shifted to B6, is A6*2.

      The distinction matters and is easy to invert. Re-compiling getExpression's text at another cell keeps the TEXT and changes the meaning — that is a move, and a formula that meant "myself" starts meaning "some cells up". This keeps the meaning and changes the text.

      Nothing is committed; the copy is anchored at target whether or not anything lives there.

    Settings
    Member Visibility

    On This Page
    Properties
    Methods