Multiple layers of dependents on UDF: excel crash

Add-in Express™ Support Service
That's what is more important than anything else

Multiple layers of dependents on UDF: excel crash
 
Henjo van Rees




Posts: 31
Joined: 2018-12-10
I have a parameterized UDF which writes to multiple ranges.

These ranges already have existing dependent cells in 2 layers of dependencies:
1. My UDF in 2 cells, writing to different ranges
2. 1 range of cells comparing the output of the 2 UDF calls
3. 1 cell which compares if all cells in 2 are equal

What happens is when I refresh 1 UDF, Excel crashes.
The "Excel has stopped working".

When I remove the last comparison, the sheet works fine. So 1 level of dependents works fine, 2 levels not.
When I disable EnableCalculation on the Worksheet, there is no crash (but ofcourse no calculation of dependents).

I have no logs, no output, nothing to work with to determine what goes wrong when Excel crashes.

Any idea what I can do?
Posted 07 May, 2020 13:56:35 Top
Andrei Smolin


Add-in Express team


Posts: 17306
Joined: 2006-05-11
Hello Henjo,

Henjo van Rees writes:
1. My UDF in 2 cells, writing to different ranges


I assume you have cell A1 with formula "=Udf({some parameters})" and cell A2 with formula "=Udf({some parameters})". Also I assume your UDF doesn't really write to cells; instead, it merely returns the calculated value for Excel to handle it. If, however, your UDF does write to cells, you should reconsider the architecture of your solution.

Your description suggests that you also have cell B1 with formula "=A1=A2". Finally you have cell B2 which "compares if all cells in 2 are equal" - I don't understand this.

Anyway, I suggest that you debug the issue. You can add a number of debug messages to your code; use System.Diagnostics.Debug.WriteLine(). If the add-in is built in the Debug configuration, you collect the messages at run time using DebugView (see http://technet.microsoft.com/en-us/sysinternals/bb896647.aspx).

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 08 May, 2020 02:31:28 Top
Henjo van Rees




Posts: 31
Joined: 2018-12-10
Our UDF writes to multiple cells (ranges) via Worksheet.Range.Value, because the UDF does not actually calculate anything, but displays a grid of data (from a REST service).

So A1 contains "=Udf({some parameters})" and J1 contains =Udf({some other parameters})".
Cells written may be for example A1 - E10 and J1 - N10 respectively.

In this case the output or both Udf's is compared (eg. Q2: =A2=J2, U10: =E10=N10).

In the last step all cells from Q2 to U10 are compared if they are TRUE

The problem is not in the Excel Add-in itself, so adding System.Diagnostics.Debug.WriteLine() statements will not work.
I am running the Add-in through the Visual Studio debugger, so debugging our own code is no problem.
When Excel crashes, nothing in our code is running, nothing to debug there. Or will that also debug crash output from Excel itself?

Rethinking our architecture (of writing to cells/ranges) is not trivial, and not a short term solution.
I prefer really understanding what's wrong and fixing that. Right now without properly understanding it I cannot justify a full rewrite.
I hope you can help understanding the real problem.
Posted 08 May, 2020 03:17:12 Top
Andrei Smolin


Add-in Express team


Posts: 17306
Joined: 2006-05-11
Oh, I see. I've wrongly assumed you use an XLL add-in.

Henjo van Rees writes:
The problem is not in the Excel Add-in itself, so adding System.Diagnostics.Debug.WriteLine() statements will not work.


I think you may be wrong. Printing debug messages shows how your UDF behaves in the wild, not in the debugger-controlled environment. Debugging in this way shows the order of calls to your UDF in complex cases like this one. It may show that Excel calls your UDF more times than expected.

If debug messages prove your UDF isn't involved in the crash, I would need to have some code to reproduce the issue. If I reproduce the issue and prove it is caused by Excel, I'll post a message on the Excel for Developers forum hoping that Microsoft will find it.

Does your UDF set ExceApp.Volatile or not?

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 08 May, 2020 03:46:42 Top
Henjo van Rees




Posts: 31
Joined: 2018-12-10
I checked while adding the System.Diagnostics.Debug.WriteLine() and DebugView:
The first time the UDF's are called works fine (I see the writeline I put at the top of the function).

The second time (which actually crashes Excel) I don't see the writeline entry, so I assume that the UDF is not executed. At least, our code is not reached. Maybe something in the Addin Express framework, but I doubt that.

I do not set ExcelApp.Volatile (verified: it is false).

There is something in the chain of dependent calculation that is going wrong, but I just can't understand what.
Posted 08 May, 2020 04:29:45 Top
Henjo van Rees




Posts: 31
Joined: 2018-12-10
Maybe related, but I have another crash situation:

With the same UDF in 1 cell (nothing else, no dependents),
when I set the sheet to "Manual calculation" and call the UDF, Excel also crashes.

Really strange...
Posted 08 May, 2020 04:42:07 Top
Andrei Smolin


Add-in Express team


Posts: 17306
Joined: 2006-05-11
Hello Henjo,

May I have some code for testing?

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 08 May, 2020 05:44:44 Top
Henjo van Rees




Posts: 31
Joined: 2018-12-10
I am going to try to make a minimal version which demonstrates the crash.
That will take some time, I will post it when I am done.
Posted 08 May, 2020 05:51:20 Top
Henjo van Rees




Posts: 31
Joined: 2018-12-10
Sorry for the delay. I have prepared an UDF which is able to crash Excel:

Addin Express UDF crashing Excel.zip

The zip contains:
- CRASHING_SPREADSHEET.xlsx : cell D2 contains the function. Call it twice. First time is okay, second time crashes Excel.
- Sources for the CrashingUDF (stripped version with mock data, no backend needed)

I hope you can take a look at it. I am out of ideas.
Posted 27 May, 2020 07:00:53 Top
Andrei Smolin


Add-in Express team


Posts: 17306
Joined: 2006-05-11
Hello Henjo,

No problem about a delay: we know you are busy.

I've reproduced the crash. Looking into the code.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 27 May, 2020 08:31:38 Top