Posts 1 - 10 of 29
First | Prev. | 1 2 3 | Next | Last
|
|
|
|
Henjo van Rees
Posts: 46
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: 18817
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).
Andrei Smolin
Add-in Express Team Leader |
|
|
Posted 08 May, 2020 02:31:28
|
|
Top
|
|
|
Henjo van Rees
Posts: 46
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: 18817
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?
Andrei Smolin
Add-in Express Team Leader |
|
|
Posted 08 May, 2020 03:46:42
|
|
Top
|
|
|
Henjo van Rees
Posts: 46
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: 46
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: 18817
Joined: 2006-05-11
|
Hello Henjo,
May I have some code for testing?
Andrei Smolin
Add-in Express Team Leader |
|
|
Posted 08 May, 2020 05:44:44
|
|
Top
|
|
|
Henjo van Rees
Posts: 46
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: 46
Joined: 2018-12-10
|
Sorry for the delay. I have prepared an UDF which is able to crash Excel:
https://www.dropbox.com/s/djvxx6ob8kpkltr/Addin Express UDF crashing.zip?dl=1
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: 18817
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.
Andrei Smolin
Add-in Express Team Leader |
|
|
Posted 27 May, 2020 08:31:38
|
|
Top
|
|
|
Posts 1 - 10 of 29
First | Prev. | 1 2 3 | Next | Last
|
Forums
Topics
Search
Help
Login
Register