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
 
Andrei Smolin


Add-in Express team


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

You should check if Excel is waiting for your calls; see How to check programmatically if the user is editing an Excel cell.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 05 Jun, 2020 00:55:36 Top
Henjo van Rees




Posts: 31
Joined: 2018-12-10
I will try that. The problem is not occurring right now, so it may have something to do with too many COM objects opened maybe? Maybe a cause of me restarting Excel so often. Fingers crossed.

One more question:

I need to get the correct Worksheet in de AddinModule, in the message handler.
Which means that I have to look it up in the XLLModule, in the UDF.

I can use the SheetNm function, which returns something like this: "[FILE.xlsx]This is my sheet name"
Can I use that sheet name to lookup the Worksheet in de AddinModule?

Or can I lookup the worksheet index in the XLLModule, that would work too.
Posted 05 Jun, 2020 01:53:48 Top
Andrei Smolin


Add-in Express team


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

Henjo van Rees writes:
The problem is not occurring right now, so it may have something to do with too many COM objects opened maybe?


I didn't see such symptoms. BTW, if you use DoEvents, this may cause this issue as well. We suggest: never ever use DoEvents.

Henjo van Rees writes:
Can I use that sheet name to lookup the Worksheet in de AddinModule


Of course. The Worksheets collection allows using a sheet name as index.

Henjo van Rees writes:
Or can I lookup the worksheet index in the XLLModule, that would work too.


I suppose I don't understand this phrase.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 05 Jun, 2020 02:02:16 Top
Henjo van Rees




Posts: 31
Joined: 2018-12-10
Regarding the 0x800AC472 COMException:

I have a spreadsheet which calls my various UDF's about 30 times (Ctrl-Alt-Shift-F9). What I see in the debugger is that the MessageHandlers are triggered _while_ while some of the UDF's are still executing.
The UDF's are not writing anything to cells anymore, they are executing and calculating.

So a 0x800AC472 COMException is thrown in the MessageHandlers when I change properties (Value etc) while Excel is still handling functions (it is indeed in Edit mode using your example).

If I read correctly then the XLLModule and AddinModule run in 1 thread, so waiting until the edit mode is cleared won't work.

Any suggestions?
Posted 05 Jun, 2020 02:27:51 Top
Andrei Smolin


Add-in Express team


Posts: 17306
Joined: 2006-05-11
You can resend the message requiring handling that cell(s).

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 05 Jun, 2020 03:15:14 Top
Henjo van Rees




Posts: 31
Joined: 2018-12-10
Good idea, but would I do the resending of messages?

The XLLModule does not know if the spreadsheet is being edited (using your IsEditing() example) since in the XLLModule I cannot may not use HostApplication anymore since it is deprecated).

So, how would I detect that resending would be necessary?
Posted 05 Jun, 2020 03:38:18 Top
Andrei Smolin


Add-in Express team


Posts: 17306
Joined: 2006-05-11
The idea is: the XLLModule simply sends requests, the add-in module handles them when possible. Your OnSendMessage handler can resend the request if IsEditing() returns true.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 05 Jun, 2020 03:49:06 Top
Henjo van Rees




Posts: 31
Joined: 2018-12-10
That did not work for me, but catching COMException and resending the message did the trick.
So far my tests are good, even with many calls!
Posted 05 Jun, 2020 05:29:53 Top
Andrei Smolin


Add-in Express team


Posts: 17306
Joined: 2006-05-11
It's great!

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 05 Jun, 2020 07:10:33 Top