Excel crashes when editing cell while office-addin is running

1
votes

I have already had several cases of Excel crashing.

The starting point is that an Office addin is currently busy with longer lasting actions (searching for functions across all sheets with subsequent retrieval of data from the server and subsequent writing of this data to Excel). If you click into a cell during this time and enter a value, you will get an "InvalidOperationInCellEditMode", if you are currently working in an Excel.run(asyn context ... - range in the addin. The crash of Excel does not always occur, sometimes it takes dozens of times.

However, such a crash is always annoying for the user who is currently working with the Addin in Excel. By the way, it doesn't help if you catch an Excel.run range, even then Excel crashes.

1
exceloffice-js
Could you please share the sample code that will crash the excel? it would be nice if you could share the script lab gist, therefore we can repro in our side. - Raymond Lu
Hi Raymond, it is the same code, i posted in stackoverflow.com/questions/61465017/… . The only thing I did is to add a catch arround Excel.run(). Then you just need to create a workbook with lets say 100 sheets, each containing an UDF so that the addin runs a little bit longer. As I mentioned, Excel does not crash all the tim but quite often,. - Marco Siebert
Here is the gist: gist.github.com/MSiebert77/c1417541e0bdb23a035bb8e26f6a5803 - Marco Siebert
i just tried on this gist, but i am not be able to repro the issue. any tips to repro the crashes? - Raymond Lu
This issue does not happen all the time, I needed 10+ tries to get it. If you click run, enter a value to a cell while runtime. If you see the exception without crashing in about 5 seconds, you have to click run again for the second try. I just tried it, on the 11th try Excel crashed. Btw.: do you have 100 sheets with functions so the addin runs longer and is not finished before you enter a value? - Marco Siebert

1 Answers

1
votes

The fact that edit-mode makes any API call fail is a "known-issue", and is not really fixable -- even VBA exhibits that same problem for [most] of API calls. and most actions in the ribbon are disabled. So does Office Add-in APIs.

Therefore when entering cell edit mode, API call fail and throw exception InvalidOperationInCellEditMode is by design

There is a workaround not sure if you could leverage in your scenario:

Excel.run has an overload that takes in a RunOptions object. This contains a set of properties that affect platform behavior when the function runs. there is a property: delayForCellEdit Determines whether Excel delays the batch request until the user exits cell edit mode. When true, the batch request is delayed and runs when the user exits cell edit mode. When false, the batch request automatically fails if the user is in cell edit mode (causing an error to reach the user). The default behavior with no delayForCellEdit property specified is equivalent to when it is false.

You can refer to this article: https://docs.microsoft.com/en-us/javascript/api/excel/excel.runoptions?view=excel-js-preview#delayforcelledit

For the Crash issue, I actually not be able to repro it. it would be great if you could share more tips on repro information, thanks.