At which point during an Excel XLL startup is the HostApplication available?

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

At which point during an Excel XLL startup is the HostApplication available?
 
Jason Rodman




Posts: 33
Joined: 2016-11-01
I will see what my organization will allow. TeamViewer is not recommended since it poses a security threat since it was hacked last year. Maybe something along the lines of gotomeeting possibly. We will talk on wednesday.

So using an XLL poses a challenge for me that maybe you can come up with a creative solution for. My addin stores settings in the excel workbook's DocumentProperties collection that I need to access when a UDF is run. It changes the behavior of the UDF, and since multiple workbooks can be open, I have to read it on demand in each UDF call. If there was a way to read these properties when a workbook opens and cache them, I could avoid having to read them in realtime. The problem is knowing which workbook is calling the UDF so I can find the cached setting for that workbook. I can always tell when settings change since it is updated from my addin thru a UI - so updating my cache is not a problem. Any thought on how I could get this to work in a multi-threaded XLL UDF scenario? Is there a mechanism to uniquely identify the workbook calling the UDF (both saved and not saved yet).
Posted 21 Apr, 2017 11:11:29 Top
nwein




Posts: 510
Joined: 2011-03-28
You can use the below code to get the full address of the executing UDF and extract the workbook name from it:
var callerRef = Module.CallWorksheetFunction(ADXExcelWorksheetFunction.Caller) as ADXExcelRef;
if (callerRef != null)
{
	string caller = callerRef.ConvertToA1Style();
	...
}


the above caller variable will be in the format: [WorkbookName]WorkSheetName!$Column$Row
Posted 21 Apr, 2017 11:19:56 Top
Jason Rodman




Posts: 33
Joined: 2016-11-01
So I took your suggestion and ran with it and came up with a workable solution. I have abandoned trying to access the Excel dom from the XLL all together and instead read the document properties of any workbook that is opened or created from the COM addin side and then cache it. When an XLL udf is run, it just reads the cache instead which is a much better solution anyway and far more performant. It also allows me to enable multi-threading the XLL UDFs which I couldn't do before. The CallWorksheetFunction of the XLL module made this possible by being able to figure out the workbook name which I use as a key to store these settings by in memory. That coupled with excel's limitation of not allowing more than one workbook open with the same name open at the same time make the workbook name unique, solving the problem of being able to uniquely identify the workbook in memory. The solution so far works well, so enjoy your holidays guys. Thanks for the help in sorting this out.
Posted 24 Apr, 2017 09:32:46 Top
Andrei Smolin


Add-in Express team


Posts: 14106
Joined: 2006-05-11
My congratulations, Jason!

Thank you very much, Nir!

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 26 Apr, 2017 04:02:14 Top