Programming and Intuition? Come on.

Sounds like it happened to all of us, right? Let’s talk about the last time it happened to me.

Beside a Azure cloud solution I currently work on, I also work on a solution for Excel/ Office automation. Excel is a beast – believe me.

The most problematic area is the environment where Excel runs in. Btw. I do mean the real desktop version, not Excel Services or the like. Actually we do the same thing, we run Excel as a service. No user interface. No interaction. Just automation. To be honest: This is not complex, this is complicated, certainly due to the fact that Excel and all the other Office products haven’t been made for being used like this.

I got this comment here somewhen on this posting, quite funny:

Actually it’s true, it is a *real* problem. And if you believe running Excel as a service is uncommon – Microsoft does the same thing, hidden behind Termin Services for Excel Services. No, it doesn’t make it easier only because other people face the same problem 😀

Nevertheless, there is a big lack of alternatives when it comes to run VBA macros or usage of custom AddIns.

Back to the point. Intuition. A considerable big company uses that product to create its reporting. Being in the Business Intelligence industry, reporting is pretty common – and reporting is still Excel or at least Excel-based. Controllers love it. Programmers… I am not sure :-).

What do they do? They have a lot of Excel workbooks with Analysis Services connections to a pretty huge database. Excel Workbooks shall be

• opened
• rendered
• calculated
• then exported to PowerPoint.

This sounds not like dramatic complicated work, but we automate Excel. And Excel changes behavior based on the workbooks it has to process. It can be the same Workbook, slightly changed, that can lead to Excel calculating forever. Forever? Right. I saw Excel calculating the whole night when it normally finished the calculation in seconds.

And this was exactly the fact when I came into the play at customer side to solve the problem. The creation of PowerPoint presentations is pretty comprehensive. This is a job based solution, so it is possible to run multiple Excel instances side by side controlled by a multi threading solution. We identified a problem with closing a workbook somewhere between the 50th and 60th job, which is equal to the count of Excel instances that has been created, remote controlled and closed.

That means 50 Excel instances with about 20 Excel workbooks run just fine and then Excel starts to calculate forever. In this case actually when a workbook has to be closed. As I did work with Excel quite a while, I know that if there should not be any message boxes in any case when trying to close an Excel workbook, you have to set the Saved property of the workbook to true. That is normally completely sufficient to tell Excel there is no need to calculate, ask or whatever, just close the workbook.

Here, Excel behaved differently. To emphasize it again, the same workbook has been processed successfully min. 50 times before, but this time, Excel needed one full CPU and calculates. Forever.

We certainly have a mechanism in place that allows to kill the Excel instance and inform the user about what’s happening. But that doesn’t solve the problem.

I was starring at the code lines.

// avoid alerts from excel.
context.ActiveWorkbook.Object.Saved = true;

context.ActiveWorkbook.Object.Close();
context.ActiveWorkbook.Dispose();
context.ActiveWorkbook = null;

If you wonder about .Object here, there is a small wrapper around each Interop object to be able to dispose Excel interop objects properly. This is not much code, right? Excel will stay at calling Close(). What now?

This is where intuition and certainly experience comes into play. And it raises questions:

• What does make Excel behave like this?
• How can this prevented?

In this case, I didn’t have to ask myself these questions. It was just there: If it calculates, the only possibility to keep Excel from doing so is set Calculation Mode to manual. This is what I immeditaly condensed from feelings.

You can ask Dr. Google and improve your Google Fu but you will find as much information as for running Excel as a service. Nothing.

Created a new build, shipped it, let it run – worked!.

You can call it experience, but experience is not enough. It is feeling, is it intuition – certainly combined with experience.

P.S: A friend of mine was telling me some time ago, when he searches for new employees, he wants to have “instinct programmers”. Smart guys, smelling it. These guys do it. Faster and better. They are rare, but they exist! 😉