Installed JChem 4 Excel 126.96.36.199 in Excel 2007 on Windows XP SP3.
Create a new VBA add-in that has this code only in ThisWorkbook:
Private WithEvents App As Application
Private Sub Workbook_Open()
Set App = Application
Install and activate the VBA add-in.
Now, when you open Excel, (you don't need to do anything), then close Excel, Excel will crash with the infamous "Microsoft Office Excel has encountered a problem and needs to close.".
If you only have JChem4Excel active Excel doesn't crash. Or, if you do not have JChem4Excel installed then the VBA add-in can of course be used. But not together.
This affects e.g. Biochemfusion's Proteax for Spreadsheets which has the UI part implemented in VBA and relies on being able to use events.
For your convenience I have also attached a VBA add-in that implements the simple code above and nothing else.
-- Jan Holst Jensen
I have installed your demo xlam to
and it does not fail. What I did was to open up an Excel instance (checked the xlam loaded state with Alt+F11) and after closed the application.
Nothing negative happens.
Please try out your system with setting the App reference to Nothing
in the Closing event.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set App = Nothing
Thanks, the workaround in Workbook_BeforeClose() resolves the issue for me. I will add this workaround to the next release of Proteax for Spreadsheets just in case.
I will see if I can reproduce the issue on an even more clean install of XP + Excel, even though I have seen it on two of my virtual machines now.
It should not be necessary to manually release the App reference in the VBA code and it indicates to me that there is a problem somewhere in the JChem 4 Excel shutdown code. Previous versions of JChem 4 Excel did not have this issue (at least not the one that I installed last year).
-- Jan Holst Jensen
Thanks for your feedback.
We have not changed the shutdown code for a year.
Could you please tell us which version worked for you properly on your environment. It would help a lot to investigate a possbile change in code (we do not remember now).
One year ago there was a C# to C++ optimization in the code, more change I do not know about.
I checked my install files. Actually, the version that used to work for me is more than a year old - it is two years old, sorry.
The old version which had no shutdown issues on my systems is version 188.8.131.520, and it has a file timestamp of 2010-05-15 here (which probably reflects the day that I downloaded it).
-- Jan Holst Jensen
Thanks. I am downloading both now and will try them sometime later today.
The steps you did sound correct to me. To re-state the steps:
1) Add SimpleAddInWithEvents.xlam to C:\Documents and Settings\<user>\Application Data\Microsoft\AddIns\
2) Start Excel.
3) Activate the SimpleAddInWithEvents add-in.
4) Close Excel.
5) Start Excel.
6) Check that the SimpleAddInWithEvents is active - open the VBA editor and check that it is loaded.
7) Close Excel. On my machines Excel will then crash (when JChem4Excel 5.9.3 is also installed).
I am currently setting up a completely clean Windows XP with Office 2007 to see if I can reproduce the error on that.
Checked this on a separate VM, Windows7 and Office2007. The problem does not come.
I rebuilt a clean configuration from a blank Windows XP image.
Windows XP SP3 clean install. Danish keyboard, time, and date set.
Office 2007 installed, minus Outlook and minus Publisher. Tons of updates applied, bringing it to Office 2007 SP3.
** Snapshot1 taken **
.NET 3.5 SP1 installed. Security updates applied in several rounds. Windows Update now replies that there are no further critical security updates for this machine.
** Snapshot2 taken **
Install JChem4Excel 5.9.3. Restart.
Put SimpleAddInWithEvents in Microsoft\AddIns\ dir.
Activate the VBA add-in.
When Excel is closed it crashes.
Adding the Workbook_BeforeClose() workaround "solves" the problem.
After a little searching I found that I did have a Windows 7 VM with Office 2007. Not entirely
sure how clean its configuration is but I took a snapshot and installed
JChem4Excel 5.9.3 on that. I could *not* reproduce the problem there so
it is likely that the issue only shows itself on Windows XP.
I will see if I have some time tomorrow to compare the older versions and see when the issue started to appear.
Thanks jan for putting effort on this.
I found an XP machine and Office 2003 the issue is reproducible.
it seems it is relting to the Windwos version rather than the Office version.
We try to fix this.
Glad to hear that you could reproduce it. And thanks in advance for fixing it.
what happens if you disable the CXNSharedAddin rather than the JChemExcel?
Sorry, I don't understand. I don't have any CXNSharedAddin in Excel - at least not any I can find. I have a "JChemExcel Functions" listed under "Add-Ins". I looked under Automation servers for CXNSharedAddin but did not see it there either.
This is with JChem4Excel 5.9.3 in Office 2007 on WinXP.
CXNSharedAddin is a shared component and JChemExcel installs it automatically, it is sitting next to the Marvin OLE component in the installer merge module.
Please find it among COM addins rather than among automation addins. Because we found that this addin caused the crash when exiting not the JChemExcel on our environment.
I rolled back to snapshot 2 on my VM (the snapshot right before installing 5.9.3), then installed the 5.9.4 candidate.
I rebooted the VM, installed the simple VBA add-in, enabled it, restarted Excel and checked that the simple VBA add-in was active. I can now shutdown Excel without problems :-).
I tested it several times and I also double-checked that the VBA add-in only did something in Workbook_Open() - it did not have a Workbook_BeforeClose() event workaround.
Well done! Thanks :-).