iOfficeAI/OfficeCLI · error · ArgumentException

validation formula2 is {dvFormula2.Length} chars; Excel's li

Error message

validation formula2 is {dvFormula2.Length} chars; Excel's limit is 255.

What it means

Thrown by AddValidation when an optional formula2 property is supplied and is longer than 255 characters. The same 255-char Excel cap that applies to formula1 applies to formula2; the handler rejects the over-length upper bound instead of writing a file Excel will refuse.

Source

Thrown at src/officecli/Handlers/Excel/ExcelHandler.Add.Tables.cs:606

            // Non-list formulas land in the A1-only <x:formula1> element —
            // an R1C1-style ref makes real Excel refuse the file (0x800A03EC)
            // while schema validation stays green. (For type=list the text is
            // a literal option list, not a formula, so it's left alone.)
            if (dv.Type?.Value != DataValidationValues.List)
                ValidateNoR1C1Reference(dvFormula1);
            dv.Formula1 = new Formula1(NormalizeValidationFormula(dvFormula1, dv.Type?.Value));
        }
        else if (dv.Type?.Value == DataValidationValues.List)
        {
            // R28-A1 — type=list with no formula1 at all is also nonsense.
            throw new ArgumentException(
                "Property 'formula1' is required for validation type=list; supply options like formula1=\"1,2,3\" or a range reference.");
        }

        if (properties.TryGetValue("formula2", out var dvFormula2))
        {
            if (dvFormula2.Length > 255)
                throw new ArgumentException(
                    $"validation formula2 is {dvFormula2.Length} chars; Excel's limit is 255.");
            if (dv.Type?.Value != DataValidationValues.List)
                ValidateNoR1C1Reference(dvFormula2);
            dv.Formula2 = new Formula2(NormalizeValidationFormula(dvFormula2, dv.Type?.Value));
        }
        else if (dv.Operator?.Value == DataValidationOperatorValues.Between
                 || dv.Operator?.Value == DataValidationOperatorValues.NotBetween)
        {
            // operator=between/notBetween needs both bounds. Without formula2
            // Excel silently treats the rule as "anything passes" — the file
            // opens but validates nothing. Reject up front rather than land a
            // permissive no-op on disk.
            throw new ArgumentException(
                $"Property 'formula2' is required when operator='{dv.Operator.InnerText}'; supply both bounds (formula1=lower, formula2=upper).");
        }

        // CONSISTENCY(tracking-rebind): previously we copied `properties`
        // into a fresh OrdinalIgnoreCase dictionary, but the copy constructor

View on GitHub (pinned to 1ced45e900)

Solutions

Move the upper-bound logic into a cell and reference it in formula2.
Shorten formula2 to <=255 characters.
Where possible, share one cell-backed expression for both bounds.

Example fix

// before
handler.Add("/Sheet1", "validation", null,
    new() { ["sqref"] = "A1:A5", ["type"] = "decimal", ["operator"] = "between",
            ["formula1"] = "0", ["formula2"] = longExprOf300Chars });
// after (upper bound computed in Z1, referenced)
handler.Add("/Sheet1", "validation", null,
    new() { ["sqref"] = "A1:A5", ["type"] = "decimal", ["operator"] = "between",
            ["formula1"] = "0", ["formula2"] = "=$Z$1" });

Defensive patterns

Strategy: validation

Validate before calling

if (properties.TryGetValue("formula2", out var f2) && f2.Length > 255)
    throw new InvalidOperationException($"formula2 is {f2.Length} chars; Excel cap is 255");

Prevention

Move long upper-bound logic into a cell and reference it.
Apply the same 255-char budget to formula2 as to formula1.
Reuse a single cell-backed expression for shared bounds.

When it happens

Trigger: Call Add type "validation" with properties["formula2"] longer than 255 characters (typically a long inline value or computed expression for the upper bound).

Common situations: A between/notBetween rule with a very large literal bound; pasting a long formula as the upper bound; reusing a long shared expression for both bounds.

Related errors

AI-assisted analysis of iOfficeAI/OfficeCLI@1ced45e900 (2026-08-13). Data as JSON: /api/errors/423303135f5930fa. Report an issue: GitHub.