Yes, apparently this is still possible to do on cue. The attached file will do it and here's how:

• Click the button and wait for the web page to load
• Switch to either Sheet2 or Sheet3

That's it, kiddies. You're very own spreadsheet that politely asks if it can send details on your porn collection to Microsoft when it crashes.

Info on the spreadsheet for you paranoid delusionals: It contains three sheets. Sheet1 contains a Microsoft Web Browser control and a command button. The other two sheets are blank.

It is free from virii to the best of my knowledge. There is a macro in it, the one that loads the web page when you click the button. It's contents in their entirety are as follows:

   Private Sub CommandButton1_Click()
      WebBrowser1.Navigate2 "www.google.com"
   End Sub

The GPF appears to occur only if an element on the web page you load has focus at the time you switch sheets. As luck would have it, the fine folks at Google set focus to the textbox for you when the page loads but if you substitute a different web page that doesn't do this, you won't get the error unless you click in a textbox first. Haven't tested with other form elements.

Enjoy! I know I did while researching this problem!

Here's one that ranks pretty high on the weird scale.

This is my .NET application that does a whole lot of mucking with Excel. Recently, I became acutely aware that I wasn't cleaning up after myself very well when Excel would regularly freeze up after working with my app for a while.

This became more obvious after pulling up Task Manager and noticing that the Excel process would remain in the list after doing some work. So in one place, I went through with a fine-toothed comb and made sure every single object I referenced was cleaned up with FinalReleaseComObject.

By the way, this includes indirect references to Excel objects. Example: I had a range object called cells referring to the entire Cells collection of a sheet. In one function call, I passed in cells[i + 1, j] as a parameter. That was a problem. Instead, I had to create another Range variable, set its value to cells[i + 1, j], pass it as a parameter, then call FinalReleaseComObject on the temporary variable. But I digress.

In another more complicated procedure, I was also getting problems with the Excel process remaining open. I say complicated but in reality, there was only one Excel object being used. No other objects, direct or indirect, to be found. And it was being cleaned up. Even getting a 0 back from the FinalReleaseComObject call. And yet, the Excel process still remained open afterward.

My high-tech debugging technique that has served me well for nigh on ten years: comment everything out until the problem goes away, then start uncommenting until it comes back.

Using this clandestine method, I narrowed it down to the very end of the procedure where I had declared some local variables for the purpose of reporting back to the user on the progress of the procedure. I comment these out and the Excel process goes away when its done.

If you're still with me, here's where it gets weird. These variables do nothing but some basic arithmetic and date calculations. Nothing at all to do with Excel. But I comment them out and all is well.

Then I comment them out and in their place add the line: int i;   That's it. I declare an uninitialized variable. The problem comes back. I comment out the offending variable declaration and it goes away.

In my head is swirling a myriad of thoughts, all variations on: WTF?!?! So I uncomment the variable declaration, verify that the problem has come back, then comment out one of my other variables earlier in the function (substituting a hard-coded value whenever it is used). Execute and again, the problem is gone!

So in essence, here's what appears to be happening: there is a limit on the number of actual variables I can declare. Not just variables holding COM objects, total variables including .NET ones. If I have too many variables declared, the Excel process doesn't get released, even if I release all the objects. I tried moving some variables higher up in the food chain and making them module-level but no dice.

I can't even begin to explain why this is and I'm fairly positive my explanation isn't exactly valid (because I haven't been able to duplicate it outside of this procedure). Something to do with the call stack or some other boring low-level aspect of the framework, I suppose. But here is how I solved it: Luckily, the variables I needed at the end could be refactored into a separate function. I did so and problem gone.

Well, postponed, anyway.

Here's something I had long forgotten. If you're using IsMissing in VBA, make sure you declare the variable within it as Variant. If not, the variable will be given the default value based on its type and IsMissing will always return false.

The Problem
After running a certain wizard in my application, the user is no longer able to open spreadsheets. Excel locks up and the only way to fix it is to kill the Excel process.

The Solution
Clearly, I was doing something funky in Excel in the wizard and not cleaning up after myself.

I have a wizard in my application and one of its functions is to copy a spreadsheet to a certain location, extract some data from it, and insert that data into a database. Here's the code for the second step which is the one relevant to my post (and I can explain about those hard-coded values...but I won't):

   public static void ExtractCategories( string fieldInputSheet )
   {
      OpenExcel( );

      Workbook book = m_application.Workbooks.Open( fieldInputSheet, f, t, o, o, o, t, o, o, f, f, o, f, o, o );

      Worksheet sheet = (Worksheet) book.Sheets[2];
      Range range = null;
      for ( int i = 21; i < 41; i++ )
      {
         range = (Range) sheet.Cells[i, 10];
         object category = range.Value2;
         // Do stuff with the category
      }

      book.Close( f, o, o );
      CloseExcel( );

   }

(Aside: f, t, and o are module level variables representing false, true, and System.Reflection.Missing.Value respectively. Shortened them because of the sheer number of parameters many Excel functions take.)

OpenExcel and CloseExcel are helper functions to manage all the crap involved with opening and closing Excel from managed code. I'll show you CloseExcel because it has some relevance:

        public static void CloseExcel( )
        {
             if ( m_application != null )
             {
                // Close all workbooks
                Workbooks workbooks = m_application.Workbooks;
                workbooks.Close( );
                Marshal.ReleaseComObject(workbooks);

                // Close the application
                m_application.Quit( );
                Marshal.ReleaseComObject(m_application);
                m_application = null;
                GC.Collect( );
             }
        }

Notice the ReleaseComObject calls. Now, hillbillies aren't what you'd call COM-based coders so I won't pretend to know anything about managing COM in .NET. My goto guy for those types of questions is James Kovacs. But I do recall adding the ReleaseComObject calls in many moons ago to combat a similar problem. So what the hey? Let's see if adding some of those to my troubled function will help:

   Marshal.ReleaseComObject( sheet );
   Marshal.ReleaseComObject( book );
   if ( range != null )
   {
      Marshal.ReleaseComObject( range );
   }

That covers all the COM-based objects I'm working with in my function. And adding those lines right before the call to CloseExcel solved the problem. I've glossed over articles on this kind of thing and probably read somewhere that managed code isn't able to clean up COM objects.

Note that I checked Task Manager while all this was running and even though the COM objects are released, the EXCEL process is still running after this function finishes. Not sure how to get rid of it but I suspect it sticks around just to be helpful. The same way WINWORD does when you use Word as your e-mail editor in Outlook. In any case, I don't think it's hurting.

Re: The leftover error from my last post. I did spend a little time mucking around with Regmon but other than a few missing keys that meant nothing to me, I didn't find anything. Then I opened SQL Server Management Studio and got the same error which was a big “uh oh” moment. I quickly realized I was out of my depth and re-installed Visual Studio 2005 so all is well for the moment. But that's not what I came to talk about.

In my last post, I mentioned an economic model posing as a spreadsheet. I also mentioned some VBA behind the scenes. The VBA doesn't do a whole lot; most of the work is done by Excel as it should be. There's a form with some buttons on it and the main button will “process” the model. Which is to say it copies the contents of some cells from one place to another. There's a lot of busy work to support that but at its core, that's what it does. And processing the model takes, on average, about four seconds. Not too bad considering what it's doing and most people would consider that good enough...

I also have a separate .NET application that makes use of this model. One of its functions is to automate the processing of the model for various dimensions. Among the dimensions: up to four different price levels, up to three cost levels, up to eight field scenarios and up to 500 sets of fiscal terms. Let me save you the math: that's 48,000 executions of the model to generate the entire set of results. So all of a sudden, my decent-looking execution time of four seconds translates to over two days straight of calculations. That doesn't include the time it takes to store the results in a database which brings it closer to three days.

And let's talk about storing the results. There are a lot of them. On average, about 300 - 400 individual measures (i.e. cell values) to store for each individual calculation. In my first pass at the application, I controlled everything from the .NET application. It was essentially four nested loops, one for each dimension. And two of the dimensions (the field scenarios and the fiscal terms) involved opening a separate spreadsheet and importing it into the model before processing. After processing, I iterated through the results sheet and picked out the individual cell values to store in the database.

This is INCREDIBLY time-consuming. A single run (one price, cost, field scenario and fiscal term set) took around forty-five seconds. Again, I'm no Interop specialist but from my own experience, retrieving the value of a cell in Excel is &*$% murder on a CPU. If I had to make something up about what is going on, I'd say it's because you have to cross COM boundaries for each cell retrieval but frankly, I'm not quite sure what that means. I've heard the words spoken before and they seem to apply here so that's my official technical summary.

To get around this, I had to sell a little bit of my soul and move code out of C# and into VBA. The application still controls importing the (up to) eight field scenarios and the (up to) 500 fiscal terms but everything else is done in VBA: setting the price, setting the cost level, processing the model and storing the results to the database. Net execution time dropped from forty-five seconds to 'twixt five and six on average.

I was ecstatic about the performance gain but this really sucks for more than just the obvious reasons. It means I now have this external application's code in two places. And one of those places is an Excel spreadsheet that can be (and has been) zipped up and e-mailed anywhere in the world regardless of whether someone uses the external app or not. It means I have a lot more work to do if I ever need to update the external application. It means I have a bigger job ahead of me when a decent alternative to VBA comes out. But as long as the model resides in Excel (and there is no business justification to moving it to anything else; let's face it, Excel rocks at this kind of thing), I'm stuck with VBA.

On a semi-related closing note: Every once in a while, my reference to the Excel interop library gets lost. I know now that this is because of the registry problems I had yesterday but I still don't know what causes it. Anyway, when this problem happens, I have to reset the reference to the Excel library and the namespace changes from Microsoft.Core.Interop.Excel to just plain Excel. It seems Microsoft.Core.Interop.Excel is the namespace for the PIA and Excel is the namespace for the plain old IA. So when I lost access to my PIA and re-added the reference to the Excel library, instead I got a reference to the IA. When I fixed the PIA problem and reset the reference to the Excel library, I got my Microsoft.Core.Interop.Excel namespace back.

Can I get a Hoo-ah! for the Coding Hillbilly?

Gotta give some consideration to changing the name of this blog to the Coding Hillbilly with ADD. The topic today is not Atlas, coding conventions or any of my other half-finished topics. It's Visual Studio 2005 Tools for Office. Well, it might be in a coming post because I had some trouble which I'll talk about first.

The background: I do some side work for someone who has built a spreadsheet. A BIG spreadsheet. Closing in on 14Mb. It's an economic model for oil and gas and judging from this fellow's reputation worldwide, my guess is that it's a pretty damn good one.

I'll talk a bit about the spreadsheet from a technical standpoint another time but the part I want to mention today is that it has some VBA code behind it. And I wanted to see if VSTO2005 might be able to whip up some holy water and exorcise that little demon. So away I went installing the beast.

After the installation, I tried to create a new Excel Workbook project from the Visual Studio templates. This message appeared:

The primary interop assembly required by this project is not installed in the global assembly cache. Microsoft Office 2003 includes the primary interop assemblies for each application as an installation option. For details on installing the Office primary interop assemblies, see the Help topic, "Installing Office Primary Interop Assemblies."

This message, to be frank, scared me. I'm not what you would call an Interop guru. I've never fully understood them because well, they're boring. They kinda freak me out actually. Or, to paraphrase Frank Zappa, “I, for one, could care less for them.” Zappa was talking about cupcakes but you get the idea.

But in order to get this to work, it looked like I'd at least have to read up on them. Before that, though, I tried to create a Word project and it worked fine which did NOT bode well for me getting anything remotely productive done this evening.

I found a couple of articles that weren't very helpful. Both suggested the same thing and it didn't work. Basically, they say that the Primary Interop Assembly (PIA) is messed up. That kind of triggered a memory of another problem I had which I'll get to in another post because it is related to this spreadsheet I'm working on. The solution they suggested is to repair Office which didn't work for me, nor did uninstalling and re-installing .NET Programmability Support. Not sure what uninstalling and re-installing Excel would have done but that was a lot closer to the “last resort” end of my list of things to try.

The other solution was to use regasm to register the PIA. This is where I started to pinpoint the trouble because when I did so, I got a message:

RegASM : error RA0000 : An error occurred while writing the registration information to the registry. You must have administrative credentials to perform this task. Contact your system administrator for assistance

(On a side note: I love messages that say to contact your system administrator because I've met a lot of system administrators.)

Of course, I'm running as a local administrator because I'm not ready to admit yet that I have a problem. Again, this message appeared when I ran regasm on the Excel interop but not the Word one, which worked fine.

Enter SysInternals Regmon utility (and congrats, guys, on your newfound wealth and fame). With it, I zeroed in on the exact registry entry that was causing the trouble: HKCR\TypeLib\{00020813-0000-0000-C000-000000000046}\1.5 ACCESS DENIED. Double-click on the entry and check the permissions and lo! my problem is revealed!

For whatever reason, the 1.5 key had not been granted any access to anyone. I double-checked the same key for the Word interop and it was fine. I can't imagine what on Bill's earth caused this but it is fixed now...sort of. I gave Everyone full control over the key (because I can't be bothered to debug the inevitable security problems that will arise by assigning a more obtuse ACL then promptly forgetting it) and I am now the proud owner of an almost working VSTO2005 installation.

I saw almost because when I start up Visual Studio 2005 now, I get the following:

The property type library could not be found in the registry.

An attempt to repair this condition failed because you do not have the permissions to write to the system registry or because the type library could not be loaded.

This can be fixed (tee hee) by a system administrator running this program once, which will cause the proper file to be registered. If problems persist, then try repairing this application from the setup program.

I'm hoping another round with Regmon will take care of this one in a jiffy, too, but I'm in no mood to tackle it tonight.

To wrap up, I'll point out that this little troubleshooting session took a LOT longer than I've implied here. One little quirk that came up is that I originally ran regasm from the V2.0.50727 folder which gave the message described above. When I ran regasm from the V1.1.4322 folder, it was a lot more helpful because it listed the exact registry key that it was trying to access.