Trouble Adding VBA Code to Module; Bug in Excel/VBE?

[Mat P:son]

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

 

[Ivan Raiminius]

Hi,

I am not sure if this will solve your problem, but try to call your
procedure with "application.run" instead of "call" to let it recompile.

Regards,
Ivan

 

[Mat P:son]

Hi Ivan, and thanks for your reply!

I'm not entirely sure whether this is applicable or not; when you say "your
procedure", which one are you talking about?

If you are refering to the procedure I'm inserting into a worksheet code
module then I'm not the one who's calling it -- it's the BeforeRightClick
event handler of the worksheet, so Excel calls this handler in whichever way
it feels like.

And the method that calls the Excel objects to create the code is not part
of the XLA file (we've tried to keep the VBA code to a bare minimum, max a
few thousand lines or so). no, the generator is part of a VB6 DLL.

So even though it might somehow be theoretically possible to make the call
via Application.Run, in practice I suppose it would be pretty inconvenient --
and probably pretty risky as well.

But anyway, to recap: Which one of those two methods do you believe should
be called with Application.Run? And by the way: what is the underlying reason
for Excel not being able to re-compile code modules if Call is used rather
than Application.Run?

Cheers,
/MP

 

[Ivan Raiminius]

Hi,

I was referring the procedure you are inserting the code into.

Application.run is not applicable if the procedure is called by event.
I would suggest you to create a dummy sub which you will call with
application.run just when finished with generating the code. It should
force excel to recompile the code.

I don't know the reason why excel sometimes is not able to recompile
code if call is used, sorry.

Regards,
Ivan

 

[Mat P:son]

A little bit of experimentation and I've come up with a fairly plausible
theory: it seems as if Excel crashes as soon as it starts re-compiling the
code module. I still don't know why though, and I suppose I may need that
elusive patch from MS to fix it (if, indeed, there ever was one).

Here's what I've figured out:

1) When I inserted code, dropped the VBProject object (and everything else
beneath), and then tried to re-acquire it later on, I got a crash. Did the
re-acquisition trigger a re-compilation? Well, I think it may have.

2) When I held on to the VBProject object for a little longer, I could keep
on checking various properties, and things worked. However, I then exited the
code generation methods, and when I re-entered it later on, and started
asking for VBProjects and stuff, Excel blew up as usual. Re-compilation?
Yeah, maybe...

3) When I inserted not only the event handler code but also a dummy method,
everything seemed to work all right. At least until I called the dummy method
hoping to trigger the re-compilation. Kaboooom!

I don't think I can do much more about this issue, from the outside. I
probably need to fix Excel, and not my code. So the main question, I guess,
is how I'm supposed to do it... Has anyone heard anything about crashes due
to on-the-fly code insertion techniques, and has anyone come across a patch
from MS to fix some kind of VBE(?) critical bug?

Cheers,
/MP

 

[Mat P:son]

And sure enough, Excel fails on all systems on which I've tried the little
code insertion trick for the particular test file I'm using:

- WinXP SP2 + Off2003 SP1
- WinXP SP2 + Off2002 SP3
- Win2K SP4 + Off2000 SP3

This was well in line with my expectations. However, when trying the
following, final configuration I was a bit surprised that things were still
blowing up in my face:

- WinNT4 SP6 + Off97 SR2b

Excel 97?! It doesn't even have proper security features, so my theory about
this whole issue somehow being caused by some buggy VBE code/virus protection
mechanism turned out to be pretty much a red herring after all...

So now I'm pretty much at my wit's end, I suppose.. Ideas? Anyone?

Cheers,
/MP