I’ve been dabbling with complex spreadsheet work, and I needed to actually write some code to run in Excel. It turns out that Excel, while it is programmable, is fragile and easy to break.
The first problem I ran into was that as my program was running, I could start another Excel application which would interfere with the Excel instance I was using. I was surprised by this, but reasonably happy that the solution was to simply set the Application.IgnoreRemoteRequests in my instance of Excel, and no longer broken by other Excel instances.
However, a few days later, I discovered Excel was completely broken on my machine. It refused to open a spreadsheet from an email attachment, or from the desktop! This was quite scary, and initially I didn’t know the cause at all. Turns out there are lots of posts on the net about this – and it is relatively easy to fix – go to the Tools|Options|General tab in excel, and make sure that the item “Ignore Other Applications” is UNchecked.
Wait a minute – doesn’t “Ignore Other Applications” and “IgnoreRemoteRequests” sound pretty similar? Why yes it does. It turns out that when an Excel extension temporarily sets this setting, Excel persists it when it closes! Whoa! That is very fragile. This means that if a plugin ever sets this setting and forgets to unset it, the user is left in the lurch forever!
For the non-programmers out there, you might think that this is the fault of the Plugin, and that isn’t completely unreasonable. But more accurately, this is the fault of Excel’s API being simply too fragile. Very few APIs in the programming world work this way – where what seems to be a transient setting is actually saved permanently. Further, if anything goes wrong with the plugin, there is no way to make a “failsafe” for this from the Plugin side. Excel saves this setting behind the programmer’s back, and the programmer has no way to know when this setting is saved. If you can’t know when it is saved, you obviously can’t know exactly when to ‘undo’ it either. We can “hack” it, but we can’t fix it.
So, if you run into this problem, where Excel refuses to load files from email or the desktop, you’ll likely find lots of sites which tell you this same fix (Tools|Options|General|”Ignore Other Applications” = unchecked). And it will work. Those sites may further say something like, “this is usually due to a faulty plugin”. All right, sort of. But Excel is a far too easily broken interface. If you are a programmer, you probably would do best to not write addins for Excel. Your code will probably never be robust.