Automating Excel through the PIA makes VBA go squiffy.
- by Jon Artus
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?
