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.

VBA Runtime Error Codes

I was teaching a VBA (Visual Basic for Applications) class for Excel today, and the question came up, "is there a list of all of the runtime error code numbers and what they mean?" You might be able to find one, but the easiest thing to do is to generate the list. Here is a small piece of VBA code that shows all of the runtime error code numbers and their descriptions.

Public Sub DisplayErrors()
    Dim i As Long
    
    For i = 1 To 65535
        If Error(i) <> "Application-defined or object-defined error" Then
            Debug.Print i & " " & Error(i)
        End If
    Next i
End Sub

When you run the code, it will print the list to the Immediate Window in the VBA Editor. Press Ctrl+G to make the Window visible.

It is possible to get other runtime errors, but only from some component that is called by VBA, not from VBA itself. When I run the code, this is the list that I get:

3 Return without GoSub
5 Invalid procedure call or argument
6 Overflow
7 Out of memory
9 Subscript out of range
10 This array is fixed or temporarily locked
11 Division by zero
13 Type mismatch
14 Out of string space
16 Expression too complex
17 Can't perform requested operation
18 User interrupt occurred
20 Resume without error
28 Out of stack space
35 Sub or Function not defined
47 Too many DLL application clients
48 Error in loading DLL
49 Bad DLL calling convention
51 Internal error
52 Bad file name or number
53 File not found
54 Bad file mode
55 File already open
57 Device I/O error
58 File already exists
59 Bad record length
61 Disk full
62 Input past end of file
63 Bad record number
67 Too many files
68 Device unavailable
70 Permission denied
71 Disk not ready
74 Can't rename with different drive
75 Path/File access error
76 Path not found
91 Object variable or With block variable not set
92 For loop not initialized
93 Invalid pattern string
94 Invalid use of Null
96 Unable to sink events of object because the object is already firing events to the maximum number of event receivers that it supports
97 Can not call friend function on object which is not an instance of defining class
98 A property or method call cannot include a reference to a private object, either as an argument or as a return value
321 Invalid file format
322 Can't create necessary temporary file
325 Invalid format in resource file
380 Invalid property value
381 Invalid property array index
382 Set not supported at runtime
383 Set not supported (read-only property)
385 Need property array index
387 Set not permitted
393 Get not supported at runtime
394 Get not supported (write-only property)
422 Property not found
423 Property or method not found
424 Object required
429 ActiveX component can't create object
430 Class does not support Automation or does not support expected interface
432 File name or class name not found during Automation operation
438 Object doesn't support this property or method
440 Automation error
442 Connection to type library or object library for remote process has been lost. Press OK for dialog to remove reference.
443 Automation object does not have a default value
445 Object doesn't support this action
446 Object doesn't support named arguments
447 Object doesn't support current locale setting
448 Named argument not found
449 Argument not optional
450 Wrong number of arguments or invalid property assignment
451 Property let procedure not defined and property get procedure did not return an object
452 Invalid ordinal
453 Specified DLL function not found
454 Code resource not found
455 Code resource lock error
457 This key is already associated with an element of this collection
458 Variable uses an Automation type not supported in Visual Basic
459 Object or class does not support the set of events
460 Invalid clipboard format
461 Method or data member not found
462 The remote server machine does not exist or is unavailable
463 Class not registered on local machine
481 Invalid picture
482 Printer error
735 Can't save file to TEMP
744 Search text not found
746 Replacements too long