iOfficeAI/OfficeCLI · error · ArgumentException

Defined name ref '{refText}' has a dangling '!' — a sheet qu

Error message

Defined name ref '{refText}' has a dangling '!' — a sheet qualifier must be followed by a range (e.g. Sheet1!$A$1:$B$5).

What it means

The defined-name body has a dangling '!' — a sheet qualifier with no following range. After stripping known error literals, the probe still ends with '!' or contains '!!'. Real references look like Sheet1!$A$1:$B$5, where the '!' separates a sheet name from a range.

Source

Thrown at src/officecli/Handlers/Excel/ExcelHandler.Helpers.Validation.cs:644

        // apostrophes are all legal). Reject only the empirically fatal
        // patterns that pass schema validation but make real Excel refuse
        // the file: doubled/trailing '!' ("乱码!!!") and stray '#' outside
        // the known error literals ("乱码###").
        // Formula-length ceiling (8192) applies to defined-name bodies too.
        ValidateFormulaLength(refText, "defined-name ref");
        var body = (refText ?? "").TrimStart('=').Trim();
        if (body.Length == 0) return;
        if (body.Contains('"')) return; // string literals — leave to Excel
        // Strip the known error literals first: "#REF!" legitimately ends
        // with '!' and must not trip the dangling-bang check below.
        var probe = System.Text.RegularExpressions.Regex.Replace(body,
            @"#(REF!|N/A|NAME\?|DIV/0!|VALUE!|NULL!|NUM!|SPILL!|CALC!|GETTING_DATA)",
            "", System.Text.RegularExpressions.RegexOptions.IgnoreCase);
        if (probe.Contains('#'))
            throw new ArgumentException(
                $"Defined name ref '{refText}' contains '#' outside a known error literal — not valid formula text.");
        if (probe.Contains("!!") || probe.EndsWith("!", StringComparison.Ordinal))
            throw new ArgumentException(
                $"Defined name ref '{refText}' has a dangling '!' — a sheet qualifier must be followed by a range (e.g. Sheet1!$A$1:$B$5).");
    }

    /// <summary>Text to store in a numeric cell's &lt;v&gt;: the literal digits
    /// when already canonical (preserves >15-significant-digit values that
    /// double cannot represent), else the parsed double re-serialized.</summary>
    internal static string NormalizeNumericCellText(string text, double parsed)
        => CanonicalNumericLiteral.IsMatch(text)
            ? text
            : parsed.ToString(System.Globalization.CultureInfo.InvariantCulture);
}

View on GitHub (pinned to 1ced45e900)

Solutions

1. Add the range after the sheet qualifier: 'Sheet1!A1' or 'Sheet1!$A$1:$B$5'.
2. Remove the stray '!' if no sheet qualifier is intended.
3. Build refs from sheet name + '!' + A1 range via helpers to avoid truncation.

Example fix

// before
wb.AddDefinedName("MyName", "Sheet1!");

// after
wb.AddDefinedName("MyName", "Sheet1!$A$1:$B$5");

static bool HasDanglingBang(string body) {
    var probe = System.Text.RegularExpressions.Regex.Replace(body,
        @"#(REF!|N/A|NAME\?|DIV/0!|VALUE!|NULL!|NUM!|SPILL!|CALC!|GETTING_DATA)",
        "", RegexOptions.IgnoreCase);
    return probe.Contains("!!") || probe.EndsWith("!", StringComparison.Ordinal);
}

try { wb.AddDefinedName(name, refersTo); }
catch (ArgumentException ex) when (ex.Message.Contains("dangling '!'")) {
    // append a default range, e.g. refersTo + "$A$1"
}

When it happens

Trigger: Setting refersTo to a body ending in '!' (e.g. 'Sheet1!') or containing '!!' (e.g. 'Sheet1!!A1'). The EndsWith('!') or Contains('!!') check trips.

Common situations: Truncated refs from a partial copy; double sheet qualification when concatenating; UI that appended a qualifier but no range; typo of two bangs.

Related errors

• Defined name ref '{refText}' contains '#' outside a known er
• Invalid array constant: '{badElem}'. Inline arrays {...} may
• Literal braces '{...}' around a formula create an Excel-reje
• Property 'sqref' (or 'range'/'ref') is required for validati
• validation formula1 is {dvFormula1.Length} chars; Excel's li

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