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 develop an XLL add-in, not an Automation add-in. The issue is: you use the Excel object model within the context of the XLL call. We strongly advise not to do so. This is based on the same recommendation from Microsoft. Unfortunately, their page doesn't exist any longer and this is a source of confusion.

To solve the issue, split the code in two parts: 1) XLL part and 2) object model part. The Object model part should be executed by the COM Add-in module *after* the XLL part is processed. To do this, your XLL call (which processes the XLL part) should end with a AddinModule.CurrentInstance.SendMessage(theMessage) call; theMessage is an integer >=1024. The "after" occurs when the add-in module receives that message (it should ignore all other messages) in the OnSendMessage event; at this moment, the add-in module executes the object model part.

Find a sample project demonstrating this idea on our blog; see https://www.add-in-express.com/creating-addins-blog/2011/10/11/invoke-excel-addin-xll/.

To transfer information to the add-in module, use a Dictionary <integer, {an object type here}> accessible by both XLL add-in and COM add-in. The XLL part adds an KeyValuePair to the dictionary, casts the key (it's an integer) to IntPtr, and passes it to the SendMessage call as WParam or LParam argument: SendMessage(theMessage, {the key here}, IntPtr.Zero). The OnSendMessage event handler extracts the key, retrieves the information and passes it to the object model part.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 27 May, 2020 09:22:24 Top
Henjo van Rees




Posts: 31
Joined: 2018-12-10
Yes we use the Excel object model in the XLL because we really want to write to a range of cells to print output.
I could not find any recommendations against this either.

The most important question for me is this: Is our current architecture (everything in XLL) the real cause of the Excel crash, or a likely cause?

I am not sure if I completely understand the concept you are proposing as a solution.
Can the COM Add-in module freely write to the whole spreadsheet without any risk of crashing/locking like we now do in the XLL module?

So, the message you propose would be the data we now write in the range?
The COM addin receives the message, extracts it and writes to the range?
Posted 28 May, 2020 01:45:49 Top
Andrei Smolin


Add-in Express team


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

Henjo van Rees writes:
Yes we use the Excel object model in the XLL because we really want to write to a range of cells to print output. I could not find any recommendations against this either.


The recommendation is: do not use the Excel object model in your XLL add-in. What Microaoft wrote on the page (the one which is missing now) is:

A function that is defined in an XLL can be called under three circumstances:
1. During the recalculation of a workbook
2. As the result of Excel's Function Wizard being called on to help with the XLL function
3. As the result of a VBA macro calling Excel's Application.Run Automation method
Under the first two circumstances, Excel's Object Model does not expect, and is not prepared for, incoming Automation calls. Consequently, unexpected results or crashes may occur.


So, you must be prepared that *some* calls to the Excel Object model from your XLL may crash or hang Excel.

Henjo van Rees writes:
The most important question for me is this: Is our current architecture (everything in XLL) the real cause of the Excel crash, or a likely cause?


I strongly believe that this issue is a direct result of your calling into the Excel object model. I've requested our developers to consider removing the HostApplication property from the XLL module. Yes, this would be a breaking change but it would have saved you a lot of time.

Henjo van Rees writes:
Can the COM Add-in module freely write to the whole spreadsheet without any risk of crashing/locking like we now do in the XLL module?


The COM add-in module is suitable for this but any module can do this. The only requirement is: you can use the Excel object model and the Excel.Range object in particular *after* the XLL call completes.

The point is: after the XLL call completes, Excel switches from the XLL call context to the COM add-in context.

It won't work if the XLL module simply calls a method in the COM add-in module. To let the XLL call complete, you call theAddinModule.SendMessage(). This method uses the Windows API to post that message to the hidden window that Add-in Express creates for your add-in; having posted the message, SendMessage() quits; your XLL call quits after that. Then Excel switches to the COM add-in context. Then, after performing other tasks - this creates a small delay - the Windows infrastructure delivers that message to the hidden window and let that window's window procedure process the message. Add-in Express overrides that window procedure: thus your add-in module receives the OnSendMessage event. You handle it in the COM add-in context. In that context, you can perform object model calls: the object model is already prepared to receive them.

Henjo van Rees writes:
So, the message you propose would be the data we now write in the range? The COM addin receives the message, extracts it and writes to the range?


Yes. The message itself is an integer. You pass a key to data in a parameter of the SendMessage method. The COM add-in receives the message, extracts the key, retrieves the data object associated with the key - the data object contains both range address and data to be written - constructs an Excel.Range object and uses Range.Value() to write data to the range. You can also use the data object to store info required to flower up the range with colors, fonts, etc.

Regards from Belarus (GMT+3),

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




Posts: 31
Joined: 2018-12-10
Ok thanks for your answer. I now understand what we are doing wrong, and will be making a plan to migrate writing to Ranges out of the XLL module to another module, using your suggestions.

I still don't fully understand why Excel crashes though, and I would like to understand exactly why.
We write to cells in our XLL, but how can that crash Excel? We use the Excel API from microsoft other than intended, but in my opinion that should not crash Excel.

Since this is not trivial to do (requires rewriting our addin) since we are not yet familiar with using other modules, I am looking at a short term fix for the crashes.

Do you know anything we can do, any workarounds/hacks in our current Add-in which will prevent the Excel crashes?
Anything you can think of to try would be useful.

That will give us time to properly rewrite the Add-in.
Posted 29 May, 2020 01:41:44 Top
Andrei Smolin


Add-in Express team


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

Henjo van Rees writes:
I still don't fully understand why Excel crashes though, and I would like to understand exactly why.


I don't think you would find anyone who could give you such an explanation. Maybe, someone in Microsoft.

They wrote: "{in some cases} Excel's Object Model does not expect, and is not prepared for, incoming Automation calls. Consequently, unexpected results or crashes may occur." We found that using the Excel Object model in an XLL call causes crashes typically, not always. I think that our experience confirms their warning.

Moreover, an XLL *function* is expected to provide results, not to write cells.

Henjo van Rees writes:
Do you know anything we can do, any workarounds/hacks in our current Add-in which will prevent the Excel crashes?


Sorry, I don't know any. I doubt that such workarounds exist.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 29 May, 2020 03:44:55 Top
Henjo van Rees




Posts: 31
Joined: 2018-12-10
I think I have a rough messaging set up between XLLModule and AddinModule.

I now want to write to cells below the calling cell (containing the function).
How can I access the CallingCell (Range) from within AddinModule_OnSendMessage?
Posted 02 Jun, 2020 08:32:38 Top
Andrei Smolin


Add-in Express team


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

The data object the key of which you pass to ADXAddinModule.SendMessage() should store that cell address.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 02 Jun, 2020 08:45:34 Top
Henjo van Rees




Posts: 31
Joined: 2018-12-10
Ok, got it.

Final question for now, what is a good convention for the SendMessage parameters?

My function can execute multiple times (different cells), with each function on a different place in the Worksheet.
I want to differentiate ofcourse, and write to different cells.

I can use:
int message, IntPtr wParam, IntPtr lParam)

You use 2824 as message key. Any ideas on how I should differentiate between function calls?
Or just use the worksheet name + cell address as key in the dictionary?
Posted 02 Jun, 2020 08:57:08 Top
Andrei Smolin


Add-in Express team


Posts: 17306
Joined: 2006-05-11
Henjo van Rees writes:
Or just use the worksheet name + cell address as key in the dictionary?


Originally, I supposed you'll simply increment an integer as key and the value would be a data object containing *all* required info. Say, your data object may contain string CallerCellAddress.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 02 Jun, 2020 09:08:11 Top
Henjo van Rees




Posts: 31
Joined: 2018-12-10
I have rewritten our Add-in per your suggestions.

I am now writing to cells inside AddinModule_OnSendMessage(), which results in no Excel crashes for simple calls.

However, I am now running into 0x800AC472 COMExceptions when I write to any property (eg. Value or Comment for a Range)
when I use a large spreadsheet (multiple tabs, multiple functions).

After some reading I found that it is Excel's way of telling me that it is not ready to handle property changes at the time I am requesting them in the AddinModule's message handler.
( https://social.msdn.microsoft.com/Forums/vstudio/en-US/9168f9f2-e5bc-4535-8d7d-4e374ab8ff09/hresult-800ac472-from-set-operations-in-excel?forum=vsto )

So, I effectively now have another type of crash :(

Any suggestions?
Posted 04 Jun, 2020 10:04:21 Top