2

I'm using EPPlus in my C# code to create an Excel workbook.  My code includes this statement:

ws.Cells[1, 1].Formula = "=UNICHAR(160)";

But when I execute my code and open the Excel workbook that it generates, cell A1 displays the error #NAME?  The Excel error message states, "The formula contains unrecognized text."

The reason for the error is that cell A1 doesn't contain the formula that I entered.  It actually contains the formula =@UNICHAR(160).  Excel has inserted an extraneous @ as the second character of the formula, thereby corrupting it.

Why is Excel converting my valid formula =UNICHAR(160) into an invalid formula =@UNICHAR(160), and how can I make it stop corrupting what I've written?

swoxo
  • 33
  • 1
  • 6
  • 1
    I don't program in C# but I imagine there is another method or a parameter you can pass to `Formula` to remove the implicit intersection operator. – Bathsheba Apr 28 '22 at 15:20
  • @ isn't necessary invalid, depending on your version. It is the implicit intersection operator that was introduced in versions that support dynamic arrays. https://support.microsoft.com/en-us/office/implicit-intersection-operator-ce3be07b-0101-4450-a24e-c1c999be2b34 – Chris Strickland Apr 28 '22 at 15:21
  • Digging a little online, I'm seeing multiple suggestions to not include the `=` in your formula. I'm not an EPPlist user though, so I can't test. That feels like a silly change, but fingers crossed. – JNevill Apr 28 '22 at 15:23
  • I believe you can use .Formula2 to prevent this, but not 100% sure. – Chris Strickland Apr 28 '22 at 15:24
  • @JNevill I'm seeing the same thing when looking at the sample projects, see [this GitHub file](https://github.com/EPPlusSoftware/EPPlus.Sample.NetCore/blob/master/06-FormulaCalculation/AddFormulaFunction.cs). Also not an EPPlus user – Adam Taylor Apr 28 '22 at 15:25
  • and the = is not necessary as far as I know – Chris Strickland Apr 28 '22 at 15:25
  • 1
    See https://stackoverflow.com/q/68139622/4961700 – Solar Mike Apr 28 '22 at 15:35
  • 1
    @JNevill The `=` is optional. Some EPPlus tutorials use it, such as [https://riptutorial.com/epplus/example/26433/add-formulas-to-a-cell](https://riptutorial.com/epplus/example/26433/add-formulas-to-a-cell), but if the `=` is omitted, then it'll automatically get added upon execution.  Either way, the unwanted `@` still gets inserted. @Chris Strickland EPPlus doesn't recognize .Formula2 as valid.  Is that a new feature added to a later release? @Solar Mike Thanks, your StackOverflow link indicates that this problem occurs in VBA as well (implicit intersection), and there's no remedy. – swoxo Apr 29 '22 at 13:37
  • That's a bummer. Thanks so much for following up with the comment. Every other site that hit for this same issue suggests solutions without any followup. This will likely be helpful to future searchers, even if it's a dead end. – JNevill Apr 29 '22 at 14:29

1 Answers1

-1

It appears that there's no way to prevent the insertion of @ into the =UNICHAR(160) formula.  The answer to the Stack Overflow question noted by Solar Mike indicates that implicit intersection is here to stay.

As a workaround, I was able to use the formula =CHAR(160) instead, and Excel didn't corrupt that.  For some reason, Excel appears to treat =CHAR(160) as returning a single value, but =UNICHAR(160) as returning a range of values, so it adds the @ symbol to return only the first value in the range, even though the unicode character 160 isn't really a range.

Although replacing UNICHAR() with CHAR() isn't a workable solution for rendering any unicode character, it appeared to fix my specific problem.

swoxo
  • 33
  • 1
  • 6
  • Your Q mentions `UNICHAR` and this A mentions `UNICODE`. which are you actually trying to use in your epplus code? – chris neilsen May 01 '22 at 05:01
  • I was using `UNICHAR`, so thank you for pointing out my mistake, which I've just corrected in my edit of my answer. (I had been using UNICODE as one of the diagnostic tools to analyze what was happening to my UNICHAR function, and accidentally got that stuck in my mind, leading to my incorrect post.) – swoxo May 02 '22 at 12:32