Heavy VBA loop crashes Excel if sheet has JChem structures.

The attached JChemStressTest.xlsm file is able to repeatedly crash Excel 2007 on my VM. The VM is Windows XP SP3 with Office 2007 SP3 and on top of that JChem for Excel 5.9.4.182.

You can run the sheet's macro by opening the file, enabling macros, and pressing "Button 1". This can be done without ill effects - will take about 20 seconds on my VM. There will be a Word process left running afterwards but you can safely kill that, or just let it be.

Now, close Excel and re-open the JChemStressTest Excel sheet. Select cell A1 and use JChem's Add/Edit to insert a structure in A1, e.g. Naphtalene. Press "Button 1" again.

On my system I have then observed any of the following behaviors:

1) Excel just disappears - full-blown crash.
2) I get a runtime error 80010108: "Method '_Default' of object 'Range' failed. This happens at the line that does "StartCell.Offset(RowOffset, 1 + I) = CellText". End the VBA macro and when you then try to exit Excel (without saving changes) Excel crashes and generates an error report.
3) Excel starts using 100% CPU and never recovers - I haven't seen it with this macro but often otherwise, see below.

An annoying side-effect is that Excel may disable the JChem add-in when it crashes so I have to remove the add-in from the DisabledItems in the registry to get the JChem ribbon section back.

I first observed this issue when importing data into an Excel sheet with JChem structures using Proteax for Spreadsheet's import function. With Proteax for Spreadsheets I have always seen behavior # 3): That the import would freeze up and Excel would then start using 100% CPU and never recover. I could only make this happen when the sheet had JChem structures.

Proteax for Spreadsheets' import function is a loop that reads data from a file and puts that data into the sheet and also calls into the Proteax COM service to do protein entry conversions.

When I commented out the COM calls to the Proteax COM service I could not reproduce the issue.
I next switched out the Proteax COM service with the MS WinHTTP service and did dummy calls into that service instead but then I could not reproduce the issue, perhaps because the WinHTTP service is in-process (?). When I switched to using Word.Application as the COM service I had no problems reproducing the issue again.

The issue was first observed with JChem4Excel 5.9.4.164 and Proteax for Spreadsheets 2.0 Beta. However, I noticed your recent release of JChem4Excel 5.94 so I reproduced the issue on a clean VM with JChem4Excel 5.9.4.182 and no Proteax add-in installed.

So, the minimal test case seems to be a VBA loop that does a combination of sheet updating and calling into an out-of-process COM service.

Best regards
-- Jan Holst Jensen, CEO, Biochemfusion Aps

PS: See you next week in Budapest :-).

Hi Jan,

the VBA code is pretty slow without adding any JChemExcel structures to the spread sheet. But yes, if I add JChemExcel structures it never returns. Such a situation please use our API recommendation and switch off structure drawing for a whole and long process you were about to simulate here.

https://www.chemaxon.com/jchem4excel/developerguide/introduction.html

Please use EnableDisable drawing proeprty (you can save and reset) before after the long process.

Uploading a modified code for you.

Viktor

 

Dear Viktor,

Thanks for the suggested workaround (switching off JChem structure drawing while modifying sheet data). It does indeed seem to prevent the crash and also speeds up processing (I think - did not measure it objectively).

However, there are several reasons why this is not a good solution in general.


First of all, I will have to add JChem-workaround code to my own add-in. Before any potential lengthy sheet-modifying operation (a "dangerous" operation) I will have to check whether the JChem add-in is installed and apply the workaround if it is present. This means extra code from my side only for the sake of not crashing the JChem add-in. Not so attractive - plus it may cause the JChem add-in to load unnecessarily (see third reason).

Second, I will have to add extra error-handling code to all the "dangerous" operations to ensure that JChem drawing will be enabled again afterwards even when errors occur. Due to VBA's simplistic error handling this is cumbersome (trivial, but cumbersome and ironically error-prone).

Third, even when I work with a sheet that does not have JChem structures, I will still have to wait for the JChem add-in to load before I can use any of the potentially "dangerous" operations in my add-in. So the first time I attempt a "dangerous" operation the JChem add-in has to be loaded in order to disable structure drawing and that gives me a performance penalty. On my system this means an extra waiting time of 5-7 seconds plus increased memory usage in Excel, going from 45 MB to 115 MB according to Task Manager.


I will have a closer look on how much extra add-in code this will cost me.

In the meantime, can you suggest a fast way to detect whether the JChem add-in has already been loaded ? If the JChem add-in has not been loaded it will mean that structures are not present and so I won't have to apply the workaround, right ? That should at least save me from the performance penalty in sheets without structures.

Kind regards
-- Jan Holst Jensen

Hi Jan,

sorry but the life is not easy just checking your points. So if you would like to use JChem Excel addin then you need to somehow handle the situation even if it reuires some extra codes (to me it is not an inacceptable).

I suppose that you also knwo that it is also not an easy think to "hack" the structures in Excel obviously Microsoft does not provide any interface for that.

If JChemExcel addin is not installed or not loaded or the drawing has not been started yet, you do not need to switch the drawing off. So the workaround can be skipped.

Why do not you use Open XML API from Microsoft it does not require to load Excel or use complicated COM http solution.

By the way we also have Open XML API if you even need structures: http://www.chemaxon.com/jchem4excel/developerguide/open_xml_api.html

Viktor

 

Hi Viktor,

Yes, I can surely add the extra code. The point is that I need to add the extra workaround-code even when I *don't* use the JChem for Excel API. The (fabricated, yes I know) example that I sent you is a good example of code that is not in any way related to JChem but is severely affected by it.

I could readily accept adding the workaound to the bits of code where I directly call the JChem for Excel API. But now I also need to add workaround-code to routines that are not related in any way to JChem for Excel and do not touch the JChem API at all. I may be forced to live with it but I won't find it acceptable.

I fully appreciate your trouble with adding custom cell rendering to Excel. I have done some work in that area myself and it is surely not trivial. It is really a shame that MS doesn't provide a standard interface for that. However, I still consider it a bug that JChem for Excel may crash if too many re-draw operations are queued (I assume that is what causes the crash). It may not be fixable, I will be able to understand that, but it is still a bug.

If I were to build a stand-alone reporting application then using the Open XML API would be a great solution, thanks. However, I have an Excel add-in so I need to work within the Excel environment, which means going through the VBA object model. And I expect the VBA object model to work reliably even when other add-ins are installed.

 

With regards to detecting whether JChem is activated or not, I haven't been able to find a solution. Can you help ? I have tried this but both attempts tell me that "JChem is loaded" even though it is in fact inactive. Perhaps I am barking up the wrong tree - or the wrong object :-) ?

Sub Button1_Click()
 
  Dim JChemComAddIn As Office.COMAddIn
  ' Throws a "Subscript out of range" if JChem add-in is not installed.
  Set JChemComAddIn = Application.COMAddIns("JChemExcel.JChemExcelAddin")
 
  If JChemComAddIn.Connect Then
    MsgBox "Via .Connect: JChem is loaded"
  Else
    MsgBox "Via .Connect: JChem is inactive"
  End If

  Dim JChemApi As Object
  Set JChemApi = JChemComAddIn.Object
 
  If Not (JChemApi Is Nothing) Then
    MsgBox "Via .Object: JChem is loaded"
  Else
    MsgBox "Via .Object: JChem is inactive"
  End If

End Sub

Cheers
-- Jan

Hi Jan,

 

I see the problem. We need to have another API routine which tells us if the JChemExcel managed part is loaded or not. I try to implement this in 5.10 then (because it is important feature to make the state retrievable if there is need for that like in this situation - IMHO this should have been implemented a long time ago).

Dim JChemApi As Object
  Set JChemApi = JChemComAddIn.Object

here

  if JChemApi.IsLoaded => wont load the infrastructure explicitly because we are about to retrieve it.

 and if it is true, then you will be able to check the

 if JChemApi.EnableDrawing

 

Viktor

Hi Viktor,

Thanks. Adding the IsLoaded property will at least make it possible for me to add workaround code that does not load all of JChem unnecessarily. Looking forward to it :-).

Cheers

-- Jan

Hi Jan,

 

the release date is planned to be done by 1st of June.

https://www.chemaxon.com/forum/viewpost42085.html#42085

Please check this in Release Planning site, I also have added the new API interface to the improvements category.

 

Viktor

Hi Viktor,

Super. Looks great :-).

Cheers

-- Jan