Com Automation addin and Xll addin Appdomains

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

Com Automation addin and Xll addin Appdomains
 
Xavier N




Posts: 75
Joined: 2010-12-03
Hello Andrei,
Coming back with questions ; )

Excel's Object model is accessible in functions with VBA and Com Automation. And used : ASAP Utilities - a rather large-scale distributed addin - use custom VBA functions based on cell colors... It's Ok to access properties in functions (with the exception of the Property DisplayFormat- cf Office Help). But most of the the time it fails to assign properties or run methods. To assign properties or run methods, i understood (and checked) you can defer the execution with SendMessage process (In Com automation or in Xll).
But SendMessage cannot be used to return the value of a property in a function...


You shouldn't call into the Excel object model while in the context of the XLL call. That means, accessing ExcelApp is sort of prohibited: it might work but this depends on how Excel manages COM and XLL calls internally. Once they change something, this may fail. Note that this doesn't mean that XLL or COM may fail. What may fail is the way Excel object model calls are handled while an XLL function is executed.


For this reason I understand that you do not recommend the use of the Excel Object model in Xll functions.
Nevertheless Nwein occasionally uses the Object model to retrieve a reference to a range (caller range or pas range). The example above did the same.
Can we consider that in theory we should not do it (it "may fail= there no garantee in future"), but that in practice it works at present if we limit ourselves to access to properties (ie without modifying). What mechanism could during the calculation of a function allow the use of the Excel model in VBA/Com and prevent it in a Xll? Are there any known examples where users have reported that Excel crash because of the use of such functions ?

(Sorry to insist a bit..)
Regards. Xavier
Windows 10 TH 2 - VS 2019 Community - Addin Express Release 9.4.4644
Posted 12 Mar, 2020 02:56:47 Top
Andrei Smolin


Add-in Express team


Posts: 17178
Joined: 2006-05-11
Hello Xavier,

You might have found me telling somewhere that Microsoft deleted a web page containing a recommendation NOT to use the Excel object model in an XLL call. The Add-in Express manual - see section Using the Excel Object Model in an XLL; see also https://www.add-in-express.com/docs/net-excel-udf-tips.php#excel-object-model-xll - contains a citation from that page:

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.


I don't know whether the deletion of that page means the restriction is gone (and if so, whether this applies to all Excel versions) or anything else. If you google for "kb/301443" (with double quotes), you'll find a number of pages referring to that page.

In practice, yes, calling into the Excel object model may work. You can even find a code fragment in the Add-in Express manual. We don't know whether this works in all scenarios in all Excel versions. Actually, the restriction above says that this shouldn't work.

Xavier N writes:
What mechanism could during the calculation of a function allow the use of the Excel model in VBA/Com and prevent it in a Xll?


This is the way Excel invokes the Excel object model and XLL.

A small story. There is no BeforeClick event in Office (e.g. in Excel). To provide developers with it, we used Windows API to subclass the window and handled the MouseDown message (Windows sends it a window when the cursor is over that window and the mouse is clicked) to raise a BeforeClick event on the add-in module; one of the expected advantages was the ability to prevent the click - it would be a really nice feature. It turned out this was a simplistic approach. Really soon after that we received a bug report describing some property (was it ActiveDocument?) returning a wrong value if accessed in the event we provided. We solved the issue by implementing the event differently (the ability to cancel the click was lost) but here's the lesson learned the hard way: the object model may not be initialized properly in situations that aren't foreseen by Excel developers.

I understand it so that the story and the restriction above, they both talk about "is not prepared for incoming Automation calls".

I don't think calling into the Excel object model is something that will work everywhere. I don't think there's a solution, approach, method or paradigm that we are missing.

Unless Microsoft declares that they support COM calls from an XLL (in all Excel versions?), you can either try and hope, or you can redesign your add-ins.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 12 Mar, 2020 03:52:51 Top
Xavier N




Posts: 75
Joined: 2010-12-03
Thanks again Andrei for your explaination and experience return...

I also don't think the restriction is gone because the page is gone. I suppose it's a non voluntary act from Microsoft... But i also fear Microsoft take care of its Javascript Api documation and less and less of it's Com/Xll technology..

Anyway, i keep in mind what you said about the risk... A day to day use will say if Excel crash or return Error result for the few function...

Thanks again Andrei,
Xavier
Windows 10 TH 2 - VS 2019 Community - Addin Express Release 9.4.4644
Posted 12 Mar, 2020 04:48:55 Top
Andrei Smolin


Add-in Express team


Posts: 17178
Joined: 2006-05-11
You are welcome.

Xavier N writes:
But i also fear Microsoft take care of its Javascript Api documation and less and less of it's Com/Xll technology.


I have the same understanding.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 12 Mar, 2020 04:59:11 Top