JChem4Excel crashes Excel with VBA add-in with events.

Installed JChem 4 Excel 5.9.3.150 in Excel 2007 on Windows XP SP3.

Create a new VBA add-in that has this code only in ThisWorkbook:

==================================================
Option Explicit

Private WithEvents App As Application

' Initialize
Private Sub Workbook_Open()
  Set App = Application
End Sub
==================================================

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.

Kind regards

-- Jan Holst Jensen

Hi Jan,

 

I have installed your demo xlam to

C:\Users\<USER>\AppData\Roaming\Microsoft\AddIns\

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.

Viktor

 

 

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
End Sub

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).

Kind regards

-- 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.

Thank you,

Viktor

Hi Viktor,

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 5.3.2.1900, and it has a file timestamp of 2010-05-15 here (which probably reflects the day that I downloaded it).

Kind regards

-- Jan Holst Jensen

Thanks, it is really pretty old then.

As far as I remember the last C# (Shutdown) version was 5.4.x

https://www.chemaxon.com/download.php?d=/data/download/jchem4xl/JChem_For_Excel_5.4.1.446.msi

and from 5.5 we used C++ shutdown process, could you please check this change if you have time?

https://www.chemaxon.com/download.php?d=/data/download/jchem4xl/JChem_For_Excel_5.7.2.220.msi

and could you you please check what steps I did for reproducing the problem was good?

Viktor

 

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.

Cheers

-- Jan

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.

Start Excel.

Activate the VBA add-in.

Close Excel.

Start Excel.

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.

Kind regards

-- Jan

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.

Best regards

-- Jan

Jan,

what happens if you disable the CXNSharedAddin rather than the JChemExcel?

Thanks,

Viktor

Hi Viktor,

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.

Kind regards

-- Jan

Hi Jan,

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.

 

Viktor

Hi Jan,

 

could you please uninstall 5.9.3.150 of JChemExcel and install

http://www.chemaxon.com/download.php?d=/data/download/jchem4xl/Development/JChem_For_Excel_5.9.4.164.msi

this new 5.9.4 candidate instead? This packs the new CXNSharedAddin.

And please try it out with your test program on your machine?

 

Thank you,

Viktor

Hi Viktor,

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 :-).

Cheers

-- Jan

Thank you for the test!

Viktor