Re: VBA Extensibility library and dynamic code compilation

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I always get rid of COM references (object pointers) by freeing/clearing
and
then "set mypointer = nothing" but thanks for the idea.

Pretty sure I've done all that but suspect something circular is going on
with my thing.

Your live data feeds etc is way outside what I know about so this may seem
naive -
If, as you say, you think your problems are related to picking up external
events, wouldn't disabling your events help until everything is ready to go
again.

Regards,
Peter T


"John.Greenan" <JohnGreenan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:48D9945E-D9D5-432C-927E-1ACFA38DBE7D@xxxxxxxxxxxxxxxx
"slightly different effect" means that if I manually hit ALT+F11 and then
compile I seem to be able to changes values a few more times before Excel
crashes. [my intial statement that manually doing this was crash-free was
incorrect].

I always get rid of COM references (object pointers) by freeing/clearing
and
then "set mypointer = nothing" but thanks for the idea.

I think that the problem I am now seeing is being caused by the high
volume
of real time data updates that are hitting the workbook. So, while I
think
the compilation problem is fixed we can still crash out Excel, it just
takes
a bit longer.

And just for fun and games, Microsoft do not have the ability (as things
stand) to replicate this bug, since they do not have any real time data
feeds
such as Reuters going into their development systems - at least according
to
David Gainer last time I spoke with him.

I think this problem is caused by the external events that we pick up.

Lots of real time data + worksheet_calculate = unstable ?????


--
www.alignment-systems.com


"Peter T" wrote:

Sounds like things are moving in the right direction.

What "slightly different effect".
Did you try the other 3 of the 4 pointers (if not already before the
OP).

Any object pointers in your main app that might be related, not released
before moving on. A wild guess, only mention it as I have a something
obscure along those lines at this very moment I can't track down,
causing my
Excel to crash on close.

Regards,
Peter T

"John.Greenan" <JohnGreenan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A72BA2B7-7316-4C97-BF20-5CAD42E43BA7@xxxxxxxxxxxxxxxx
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" <JohnGreenan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:0B0C569C-88BB-4FB2-9B6F-8462A56B7CF6@xxxxxxxxxxxxxxxx
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








.

Relevant Pages

  • Re: VBA Extensibility library and dynamic code compilation
    ... compile I seem to be able to changes values a few more times before Excel ... of real time data updates that are hitting the workbook. ... The VB6 code then uses the VBA Extensibility library to access the ...
    (microsoft.public.excel.programming)
  • Re: Cannot lock project
    ... I'm not sure how anything code related (bloat included) could live ... Peter T wrote: ... >> Then modify the code, compile, and save to see what happens. ... >> Dave Peterson ...
    (microsoft.public.excel.programming)
  • Re: Can OpenNet.CF SmartList be used as is in VB.NET?
    ... As Peter said you'd better off wating fore a few days for a release. ... >> compile this file in VS.NET and then somehow have the control be ... >> usable from within VB.NET or is SmartList dependant on other new files ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: a problem with excel addin
    ... Can you not change the LoadBehavior setting in the IDE before you compile ... "Peter T" wrote: ... program and that external program is set to MSExcel. ... Am able to see my commandbar with the ...
    (microsoft.public.excel.programming)
  • Re: Compiling Lisp
    ... > Peter> can't see how to create an executable file, ... > Peter> do it because when i go to compile it just produces a .fsl file ... > executable image requires buying Lispworks. ...
    (comp.lang.lisp)