Hi,

I've been fighting for a while trying to figure out how to get round a crash
in Excel, which seems to be related to my adding VBA code to a code module
through the VBProject.VBComponent.CodeModule.InsertLines() (using
..AddFromString() or similar methods makes no difference, Excel still blows up
in my face).

I'm running a standard XLA add-in, which acts as a glue layer between Excel
and the real functionality that resides in a number of VB6 DLL:s.

One of my VB6 DLL:s adds code to a worksheet -- and please note that this
worksheet is not part of the XLA, but belongs to an arbitrary workbook,
loaded by the user during run-time. (Info about how to insert code on the fly
can be found e.g. on Chip Pearson's site, at
http://www.cpearson.com/excel/vbe.htm)

It seems like there is no problem the first time I add the code. However,
if/when I try to re-acquire a pointer to the VBProject object I immediately
trigger a COM Automation exception in VBE. If I hold on to the objects I've
got I can still access the VBProject even after the code has been inserted.

The inserted code does not contain syntax errors or other (obvious) bugs,
and it does indeed work most of the time, because, strangely enough, not all
workbooks suffer from the problem -- there are only a few ones that do not
work. And when scrutinising these particular problematic workbooks I do not
notice any anomalies, not even a common pattern.

Excel security settings do not affect the outcome of the tests. The "Trust
VBProject access" checkbox is ticked, and my XLA is properly signed and
installed. Nevertheless, I still have a nagging feeling that this whole issue
is due to recompilation of VBA code during run-time, in combination with
security features present in Office Excel.

Interestingly enough, the Microsoft Office Crash Analysis (MOCA) screen
points me to a web page where MS claims that this crash is in fact a known
issue, and that a fix is available. This would have been really good news,
but unfortunately the web page says nothing about which patch I'm supposed to
apply, and by following the links I end up at the standard (generic) Office
Update page. And yes, I've already got all the recommended patches installed.

I'm currently running Win XP + Off 2003, but I suppose I will be able to
reproduce this on other Office platforms as well; I'm about to run some basic
tests on three or four virtual machines with different configurations.

Now, before I start posting source code and stuff I thought I'd better
submit an initial post to figure out whether any of you guys have run across
this issue before, and might even be able point me in the right direction.
(Indeed, I found quite a few posts on different websites, where developers
had been running into problems while using the
InsertLines/AddFromString/CreateEventProc methods, so even though they didn't
really help me, I'm pretty optimistic :-)

Cheers,
/MP