Excel Bug Changes Color on Cell Not in Range

I'm in the process of writing an Excel add-in, using the relatively new mechanism of writing a TypeScript library. I had previously written this Excel add-in in C# on .NET Framework 4.8, but this mechanism doesn't work in .NET 5 or 6 for architectural reasons. (With a bunch of tricks, I got Excel calling .NET 5 code configured as a COM DLL, but it won't work if the user's machine is configured in some ways and I couldn't get the add-in recognized correctly.) The TypeScript actually calls a web API that then calls the C# on the server side, so this wasn't a rewrite from the ground up but a layer thrown on top of my previous code, but Excel never sees that. From Microsoft's perspective using JavaScript has the advantage that it is portable, meaning that these add-ins can work on Windows, iOS, the web, phones, or anywhere else that Microsoft can place Excel.

TypeScript is a language that adds strongly typed features to JavaScript. It compiles into JavaScript, so it works anywhere that JavaScript does. I dislike weakly typed languages like JavaScript, so TypeScript makes JavaScript acceptable.

So in the process of writing my add-in, I ran into a bug in the Excel Javascript/Typescripts APIs. I formatted cells A6:A11 to have a blue background, and oddly enough, cell A13 turned blue as well. I don't know what the internals of the Javascript API or Excel look like, but this bug is really strange, as it seems to be dependent on the data that is in a formatted cell. In any case, I reduced the code to a small easily reproducible case, wrote it up, and submitted it to Microsoft. They confirmed the bug the same day I submitted it and have assigned it to a developer.

More info: It turns out that this is not a Javscript API bug. It is a design flaw in Excel. If you format the backcolor of three or more cells (doesn't happen with two), say A6:A8, then change a cell within three cells, say A11, it will also change color. This came back as BY DESIGN. What a stupid design. It is unfathomable to me that this is what a user would want or expect.

Here is some equivalent VBA code (press Alt+F11, double-click on Sheet1, insert the code, press F5 to run it):

  Public Sub ExcelBug()
    Sheet1.Range("A1:A3").Interior.Color = vbBlue
    Sheet1.Range("A1").Value = "a"
    Sheet1.Range("A2").Value = "b"
    Sheet1.Range("A3").Value = "c"
  End Sub
Run the code. Now type anything into cell A5 and press enter. What do you think...should the backcolor change to be blue? Well, it does. Now delete the value and change the backcolor to be no fill. Add the following line just before the End Sub:
      Sheet1.Range("A5").Value = "d"
Now run the code. A5 doesn't change to be blue. Now delete the value and type it again. No blue. What are the rules on when it will change to be blue? Now put the same data into Excel on the web. Will it be blue or not? Answer: not. Regardless of whether they claim it is by design, this is a bug.

No comments :

Post a Comment

Note: Only a member of this blog may post a comment.