HostApplication replacement?

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

HostApplication replacement?
 
Henjo van Rees




Posts: 46
 Joined: 2018-12-10
I am using HostApplication in my XLLModule for retrieving the calling cell as an Excel.Range: 


        public Range CallingCell
        {
            get
            {
                ADXExcelRef reff = (ADXExcelRef)CallWorksheetFunction(ADXExcelWorksheetFunction.Caller, new object[] { });
                return (HostApplication as _Application).Range[reff.ConvertToA1Style(), Type.Missing];
            }
        }


HostApplication will be deprecated in the future.

What is the new way to get the calling cell?

I only need to have it to get access to the Worksheet and cells below it.
Posted 20 Feb, 2020 09:42:09 Top
Andrei Smolin


Add-in Express team


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

There's no replacement. We recommend not to call into the Excel Object model (EOM)) from the context of an XLL call.

In your case, it looks like you return an Excel.Range for some code to use it, not to the end user. If so, your code might return the cell address and the receiving code could convert that address to Excel.Range itself.

In the generic case, you would use the machinery we describe in https://www.add-in-express.com/creating-addins-blog/2011/10/11/invoke-excel-addin-xll/; see section Invoke a method in the COM add-in via SendMessage. In terms of that blog, your code should consist of this parts:
- The XLL method calculating the cell address.
- A message (an integer > 1024) indicating the request to convert the address string (you can store it globally) to an Excel.Range.
- An event handler of the OnSendMessage event of the add-in module that handles the message and performs the conversion.


Andrei Smolin
Add-in Express Team Leader
Posted 21 Feb, 2020 02:10:42 Top
Henjo van Rees




Posts: 46
 Joined: 2018-12-10
Exactly, I want to know which cell (as Excel.Range) contained the function in the code.

All related code examples on the website and forums use HostApplication, which is deprecated and apparently has no replacement.

I am not quite sure how to use SendMessage to get the caller, do you have an example for that?
Posted 21 Feb, 2020 02:29:04 Top
Andrei Smolin


Add-in Express team


Posts: 18825
 Joined: 2006-05-11
No-no, you get the caller cell address in the XLL. And your function should return the address string so that the invoker could convert the address string to an Excel.Range object itself. In this case - the invoker is some code (e.g. a COM add-in) running in Excel - you don't need to use the SendMessage method at all.


Andrei Smolin
Add-in Express Team Leader
Posted 21 Feb, 2020 02:33:17 Top
Henjo van Rees




Posts: 46
 Joined: 2018-12-10
That flow does not work for us. The function returns an actual cellvalue which is displayed in the formula cell.

I'd like to request that HostApplication will not be removed in the future.
I can live with an Obsolete annotation with a clear explanation on how.

I feel that this XKCD is relevant here:
"Every change breaks someone's workflow"
https://xkcd.com/1172/
(Yes, I now know it's not good practice, but we depend on this function, please don't remove it)
Posted 02 Mar, 2020 09:18:09 Top
Andrei Smolin


Add-in Express team


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

Henjo van Rees writes:
I'd like to request that HostApplication will not be removed in the future.


The situation has two sides.

First off, from our experience we know that using the Excel object model in an XLL add-in produces errors. For this reason, we insist on your not calling into the Excel object model. You could also find my mentioning some page where Microsoft explicitly said "do not use the Excel object model in an XLL"; that page mysteriously disappeared and now I cannot point you to it.

On the other hand, there' no way to obtain an Excel.Application object *before* your XLL is invoked. And the current version can only obtain it some time *after* your XLL starts working.

This is a complete set of reasons behind our decision to declare HostApplication as deprecated. As said, the only actual change was the use of the corresponding attribute on the HostApplication declaration.

We will continue this policy unless Microsoft declares that it is useless and that such calls are okay; I doubt they'll ever do this - remember, Excel has old versions!

This said, I can only stress that the use of the Excel object model in an XLL add-in *is* the source of unwanted and unexpected behavior: even if something works on your PC, we state that it may not work somewhere else. This is how Excel functions.

Henjo van Rees writes:
The function returns an actual cellvalue which is displayed in the formula cell.


I assume you get the caller address by using Module.CallWorksheetFunction(ADXExcelWorksheetFunction.Caller). The ADXExcelRef returned by that method allows determining the index (indices) of the cell(s) on the worksheet the UDF is called from. You can also call the ADXExcelRef.ConvertToA1Style (or ConvertToR1C1Style) method and get a string representing the caller's address.


Andrei Smolin
Add-in Express Team Leader
Posted 02 Mar, 2020 10:22:28 Top