Peter T - thanks for the pointer - that reduces the number of crashes but it
does not fix it. Strangely it seems to have a slightly different effect to
manually running the compile VBA project button.


--
www.alignment-systems.com


"Peter T" wrote:

Hi John,

I have also found adding code programmatically behind object modules can be
fraught with problems, most especially if coding form thisworkbook to
thisworkbook, but that I take it is not what you are doing.

Some of your problems might depend on what you are doing (changing CodeName
?) and whether or not the VBE is open when you make the changes.

Have you tried wb save/close/open, and/or flashing (programmatically) the
VBE open/closed. Or maybe adding code to a sheet in a temporary wb and move
the sheet to yours.

To compile, ensuring the wb is active in the vbe
Set oCtrl = xlApp.VBE.CommandBars.FindControl(ID:=578)
oCtrl.Execute

I think its 578 in all versions though perhaps not in XL-2007
msgbox oCtrl.caption

Regards,
Peter T

"John.Greenan" wrote in message
...
I have inherited a VB6 application that runs unattended and creates an
Excel
2002 spreadsheet dynamically using the Excel library.

The VB6 code then uses the VBA Extensibility library to access the created
worksheet and then adds a "worksheet_calculate" event using
mycodemodule.CreateEventProc("Calculate", "Worksheet")

This "worksheet_calculate" event code is then populated by the VB6 code
writing some VBA using

mycodemodule.InsertLines lngTargetLine, strHeaderLine1

This works ok and we see that the VBA code works properly, but when the
worksheet is opened by a user we find that we cannot change any of the
data
in the worksheet with calculation set to automatic - editing any cell will
repeatably cause Excel to crash out.

To change the worksheet values we either
(a) switch calculation to manual, change the sheet and then put
calculation
back to automatic
OR
(b) we enter VBA and compile the worksheet using "Tools""Compile VBA
Project".

If we change the worksheet without making one of these changes then we
find
that Excel just crashes out. This appears to be an Excel bug.

So, if we could find a "proper" way to get the VBA in the
worksheet_calculate to be compiled we would not face this problem.

As you will know, the VBE library has no inbuilt method to compile VBA
(MakeCompiledFile and BuildFileName are only for VB6 code to create a .dll
rather than compile VBA) and I am not happy about trying to implement a
nasty
send keys method.

Has anyone encountered this and worked out a way to do this that does not
use sendkeys???

Cheers,

John
--
www.alignment-systems.com