Compiled Application Crashes

[onlyng]

Symptom: Compiled application crashes while same uncompiled
application works OK.
Platform: XL 2003 on winXP

I have an XL application with about 150 Subs and 1500 lines of code.
All the subs are accessed via a custom CommandBar, created by code.
It has been working fine for a long time.

Recently, I made a small update, involving some cells and just one
sub. I compiled and saved the application.
The application started crashing, when another sub was called - not
the modified one.

To my surprise, I discovered, that if I saved the application without
compiling it, it works!

I tried VBA Code Cleaner, and same pattern persists. The compiled
version crashes while the uncompiled version works.

Any idea?


Thanks
Dovy

 

[Jim Thomlinson]

There is very little benefit to compiling code in VBA. Normally compiled code
will run faster but not in VBA. What compiling does however is it increases
the file size. So your compiled file will take longer to load since it is
marginally larger but it will not run any faster. Take a look at this site
where you can get a decomipler utility...

http://cpap.com.br/orlando/VBADecompilerMore.asp

 

[onlyng]

There is very little benefit to compiling code in VBA. Normally compiled code
will run faster but not in VBA. What compiling does however is it increases
the file size. So your compiled file will take longer to load since it is
marginally larger but it will not run any faster. Take a look at this site
where you can get a decomipler utility...

http://cpap.com.br/orlando/VBADecompilerMore.asp
--
HTH...

Jim Thomlinson











- Show quoted text -

Thanks Jim
I read with interest your reference, but I have another experience
which is in favor of compilation. It has nothing to do with time
saving, rather with crash avoiding.

I experienced frequent crashes with another application (>350 Subs,

4000 lines of code).

The application creates custom CommandBar on Workbook_Open and cashed
often (but not always), when one of the menu items was used.
(Attempting to open a csv data file to import data).

I found an ugly workaround which eliminated the crashes: after
launching the app, I used a menu item to expose XL normal worksheet
CommandBar, than saved the app without any changes. Then I switched
back to the custom CommandBar and worked normally. This is an
application I work with on a daily basis (always on same machine), and
I had the habit of compiling after any code change. I kept following
this hated and time consuming procedure for a long time, until I
discovered by chance that sometimes, the application opened in an
uncompiled state (the compile menu item was enabled), even though I
compiled it before saving and closing.

At this point, I added the following code to Workbook_Open, and I have
not experienced one single crash ever since. (There is no more need to
re-save the app immediately after opening).

Sub Compile()

With Application.VBE.CommandBars.FindControl(ID:=578)
If .Enabled = True Then .Execute
End With

End Sub

It appears that the compiling issue in VBA has more than one aspect to
it.

Thanks
Dovy