Automating Excel through the PIA makes VBA go squiffy.

Question

I have absolutely no idea how to start diagnosing this, and just wondered if anyone had any suggestions. I'm generating an Excel spreadsheet by calling some Macros from a C# application, and during the generation process it somehow breaks. I've got a VBA class containing all of my logging/error-handling logic, which I instantiate using a singleton-esque accessor, shown here:

Private mcAppFramework As csys_ApplicationFramework

Public Function AppFramework() As csys_ApplicationFramework
  If mcAppFramework Is Nothing Then
    Set mcAppFramework = New csys_ApplicationFramework
    Call mcAppFramework.bInitialise
  End If

  Set AppFramework = mcAppFramework
End Function

The above code works fine before I've generated the spreadsheet, but afterwards fails. The problem seems to be the following line;

Set mcAppFramework = New csys_ApplicationFramework

which I've never seen fail before. If I add a watch to the variable being assigned here, the type shows as csys_ApplicationFramework/wksFoo, where wksFoo is a random worksheet in the same workbook. What seems to be happening is that while the variable is of the right type, rather than filling that slot with a new instance of my framework class, it's making it point to an existing worksheet instead, the equivalent of

Set mcAppFramework = wksFoo

which is a compiler error, as one might expect. Even more bizarrely, if I put a breakpoint on the offending line, edit the line, and then resume execution, it works. For example, I delete the word 'New' move off the line, move back, re-type 'New' and resume execution. This somehow 'fixes' the workbook and it works happily ever after, with the type of the variable in my watch window showing as csys_ApplicationFramework/csys_ApplicationFramework as I'd expect.

This implies that manipulating the workbook through the PIA is somehow breaking it temporarily. All I'm doing in the PIA is opening the workbook, calling several macros using Excel.Application.Run(), and saving it again. I can post a few more details if anyone thinks that it's relevant.

I don't know how VBA creates objects behind the scenes or how to debug this. I also don't know how the way the code executes can change without the code itself changing.

As previously mentioned, VBA has frankly gone a bit squiffy on me... Any thoughts?

Answer 1

If you look in task manager, are there any instances of excel running in the background? Just curious if it is creating an Excel object and not disposing of it properly.

Answer 2

I would suggest that somehow the PIA's are not working correctly. I would recommend unregistering them, removing all instances of them from your PC, and then regenerating them.

Of course this is not the rational explanation I would like to give, but it seems like sometimes COM just doesn't want to behave. I would love to know what really happens when things break down like this, but the only thing I have ever seem work is lots of stabs in the dark, followed by an attempt to retrofit a rational explanation once the weird refusal to work randomly disappears again.

Sorry for the lack of a 'REAL' answer