Using Excel Object Model in XLL

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

Using Excel Object Model in XLL
Dos and don'ts 
nwein




Posts: 566
Joined: 2011-03-28
Citing from ADX guide:
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 for the fact that some calls to the Excel Object model from your UDF may crash or hang Excel.


I'm more than aware of this and the way we're doing things is by passing any such automation onto a structure that is being analyzed during the sheet calculate event. This is all fine and dandy.
I'm also aware that some automation can be passed through the XLL, as (again) mentioned in your guide:
Despite the restrictions introduced by internal context management in Excel (see Using the Excel Object Model in an XLL), some things are possible to do. Below is a sample (thank you, Thilo!) demonstrating the following aspects of XLL programming:  Determining if Your UDF Is Called from the Insert Formula Dialog  Determining What Cell / Worksheet / Workbook Your UDF Is Called From  Returning Values When Your Excel UDF Is Called From an Array Formula  Returning an Error Value from an Excel UDF


Nonetheless I wanted to know what you think about setting and clearing styles (Microsoft.Office.Interop.Excel.Style) through XLL?
From my limited testing it seems to work pretty well without any hiccups, but I wanted to consult with you as you might have some more insight about this.
As I've mentioned before, we're not doing any sort of automation from the XLL, but doing it from the XLL would be a lot easier with significant performance improvement.
Posted 27 Mar, 2013 17:52:45 Top
Andrei Smolin


Add-in Express team


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

nwein writes:
Nonetheless I wanted to know what you think about setting and clearing styles (Microsoft.Office.Interop.Excel.Style) through XLL?


If it works for you then it is probably okay. Do you have a back-door way incase one day you find that it doesn't work?

I suppose you modify the styles with the caller cell(s) only, correct? As far as I remember the sample demonstrated in the section "Returning Dates from an XLL" produced problems if this code was used to modify an arbitrary cell. I suppose modifying the NumberFormat (in that sample) and the Style is actually the same operation. This is a guess as I don't know how Excel implements this.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 28 Mar, 2013 05:36:03 Top
nwein




Posts: 566
Joined: 2011-03-28
Yes, it is only meant to automate the calling cell. As mentioned, we do have a way around it via the sheet calculate event in the COM addin, but doing it (in some cases) directly from the XLL would be much easier.
As always, thanks for the reply!
Posted 28 Mar, 2013 10:15:48 Top