{"record":{"id":"5c024e80357147e6","repo":"iOfficeAI/OfficeCLI","slug":"defined-name-ref-reftext-has-a-dangling","errorCode":null,"errorMessage":"Defined name ref '{refText}' has a dangling '!' — a sheet qualifier must be followed by a range (e.g. Sheet1!$A$1:$B$5).","messagePattern":"Defined name ref '(.+?)' has a dangling '!' — a sheet qualifier must be followed by a range \\(e\\.g\\. Sheet1!\\$A\\$1:\\$B\\$5\\)\\.","errorType":"validation","errorClass":"ArgumentException","httpStatus":null,"severity":"error","filePath":"src/officecli/Handlers/Excel/ExcelHandler.Helpers.Validation.cs","lineNumber":644,"sourceCode":"        // apostrophes are all legal). Reject only the empirically fatal\n        // patterns that pass schema validation but make real Excel refuse\n        // the file: doubled/trailing '!' (\"乱码!!!\") and stray '#' outside\n        // the known error literals (\"乱码###\").\n        // Formula-length ceiling (8192) applies to defined-name bodies too.\n        ValidateFormulaLength(refText, \"defined-name ref\");\n        var body = (refText ?? \"\").TrimStart('=').Trim();\n        if (body.Length == 0) return;\n        if (body.Contains('\"')) return; // string literals — leave to Excel\n        // Strip the known error literals first: \"#REF!\" legitimately ends\n        // with '!' and must not trip the dangling-bang check below.\n        var probe = System.Text.RegularExpressions.Regex.Replace(body,\n            @\"#(REF!|N/A|NAME\\?|DIV/0!|VALUE!|NULL!|NUM!|SPILL!|CALC!|GETTING_DATA)\",\n            \"\", System.Text.RegularExpressions.RegexOptions.IgnoreCase);\n        if (probe.Contains('#'))\n            throw new ArgumentException(\n                $\"Defined name ref '{refText}' contains '#' outside a known error literal — not valid formula text.\");\n        if (probe.Contains(\"!!\") || probe.EndsWith(\"!\", StringComparison.Ordinal))\n            throw new ArgumentException(\n                $\"Defined name ref '{refText}' has a dangling '!' — a sheet qualifier must be followed by a range (e.g. Sheet1!$A$1:$B$5).\");\n    }\n\n    /// <summary>Text to store in a numeric cell's &lt;v&gt;: the literal digits\n    /// when already canonical (preserves >15-significant-digit values that\n    /// double cannot represent), else the parsed double re-serialized.</summary>\n    internal static string NormalizeNumericCellText(string text, double parsed)\n        => CanonicalNumericLiteral.IsMatch(text)\n            ? text\n            : parsed.ToString(System.Globalization.CultureInfo.InvariantCulture);\n}\n","sourceCodeStart":626,"sourceCodeEnd":656,"githubUrl":"https://github.com/iOfficeAI/OfficeCLI/blob/1ced45e900782c5083ed550ddf328ee974e425e7/src/officecli/Handlers/Excel/ExcelHandler.Helpers.Validation.cs#L626-L656","documentation":"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.","triggerScenarios":"Setting refersTo to a body ending in '!' (e.g. 'Sheet1!') or containing '!!' (e.g. 'Sheet1!!A1'). The EndsWith('!') or Contains('!!') check trips.","commonSituations":"Truncated refs from a partial copy; double sheet qualification when concatenating; UI that appended a qualifier but no range; typo of two bangs.","solutions":["Add the range after the sheet qualifier: 'Sheet1!A1' or 'Sheet1!$A$1:$B$5'.","Remove the stray '!' if no sheet qualifier is intended.","Build refs from sheet name + '!' + A1 range via helpers to avoid truncation."],"exampleFix":"// before\nwb.AddDefinedName(\"MyName\", \"Sheet1!\");\n\n// after\nwb.AddDefinedName(\"MyName\", \"Sheet1!$A$1:$B$5\");","handlingStrategy":"validation","validationCode":"static bool HasDanglingBang(string body) {\n    var probe = System.Text.RegularExpressions.Regex.Replace(body,\n        @\"#(REF!|N/A|NAME\\?|DIV/0!|VALUE!|NULL!|NUM!|SPILL!|CALC!|GETTING_DATA)\",\n        \"\", RegexOptions.IgnoreCase);\n    return probe.Contains(\"!!\") || probe.EndsWith(\"!\", StringComparison.Ordinal);\n}","typeGuard":null,"tryCatchPattern":"try { wb.AddDefinedName(name, refersTo); }\ncatch (ArgumentException ex) when (ex.Message.Contains(\"dangling '!'\")) {\n    // append a default range, e.g. refersTo + \"$A$1\"\n}","preventionTips":["Always pair a sheet qualifier with an explicit range.","Concatenate sheet + '!' + A1 range via a helper to avoid truncation."],"tags":["excel","defined-name","formula","validation"],"backgroundTag":null,"analyzedSha":"1ced45e900782c5083ed550ddf328ee974e425e7","analyzedAt":"2026-08-13T13:01:07.193Z","schemaVersion":2},"datasetVersion":"2026-08-13T14:17:21.547Z"}