Unable to get the Cell which is calling the UDF

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

Unable to get the Cell which is calling the UDF
 
Colin Blakey


Guest


I've created a Excel Automation addin and added an XLL module to it for functions. In the XLL function code I'm using the ExcelApp.get_Caller(Type.Missing) method where ExcelApp is a public value passed from the automation addin class. This is as per the section 'Determining What Cell / Worksheet / Workbook Your UDF Is Called From' in the documentation PDF. What I end up with is the value of the cell. This is running in Excel 2007 on Server 2008 R2 64bit using VS 2008 SP1. This is I think as per the documentation but it's not working.

Code:
In the AddInInitialize method (event handler) of the Excel Automation module (class inherits from ADXAddinModule) I have the following
UserDefinedFunctions.XLLContainer.ExcelApp = HostApplication as Excel.Application;

In the UserDefinedFunctions class I have a public variable
[ComVisible(false)]
internal static Excel._Application ExcelApp { get; set; }

and in the UDF
Excel.Range callingCell = (Excel.Range) ExcelApp.get_Caller(Type.Missing);
Posted 05 Nov, 2009 15:04:53 Top
Andrei Smolin


Add-in Express team


Posts: 18817
 Joined: 2006-05-11
Hello Colin,

That section suggests using get_Caller (Caller in VB.NET) in Excel Automation add-ins, not XLL add-ins. In addition, that section describes how to get the caller cell in XLL add-ins.

I've found that the ExcelApp property of the ADXXLLModule class mentioned in that section isn't created when you add an XLL module to an existing COM add-in; it's an obvious bug in our code. Besides, that property is generated for XLLModule, not for ADXXLLModule; that bug I'll fix myself :). The ExcelApp property is generated, however, if you create an XLL add-in project from scratch. Here is that property in C#: 

public Excel._Application ExcelApp
{
    get
    {
        return (HostApplication as Excel._Application);
    }
}


So you can use that property instead of passing Excel.Application to the XLLContainer class.

Also, please read http://www.add-in-express.com/docs/net-office-tips.php#multiple-office-extensions. When your assembly is loaded as an XLL add-in, the COM add-in part of the assembly is uninitialized and vice-versa, when your assembly is loaded as a COM add-in, the XLL add-in part of the assembly isn't initialized. In other words, when you assign a value to UserDefinedFunctions.XLLContainer.ExcelApp, you do this for the XLL module which is not initialized (and will never be).

A COM add-in can communicate with an XLL add-in (as well as with an Excel Automation add-in) by calling any of its public methods. Note that XLL allows hiding a public function so that it isn't shown in the UI. An XLL add-in (any application) can call any public property or method of a COM add-in using the following VB syntax:

ExcelApp.COMAddins.Item(strMyComAddinProgId).Object.MyPublicPropertyOrMethod.

1. strMyComAddinProgId - see the ProgId attribute of your add-in module.
2. MyPublicPropertyOrMethod is called via late binding (see System.Type.InvokeMember in MSDN)

Note that ADXAddinModule is a base class for COM add-ins. Excel Automation add-ins descends from ADXExcelAddinModule.

Hope this helps.


Andrei Smolin
Add-in Express Team Leader
Posted 05 Nov, 2009 17:10:17 Top
Colin Blakey


Guest


Hello Andrei,

I'm not sure that I'm fully understanding your reply so bear with me while I respond to your comments.

Andrei Smolin writes:
That section suggests using get_Caller (Caller in VB.NET) in Excel Automation add-ins, not XLL add-ins. In addition, that section describes how to get the caller cell in XLL add-ins.

I'm using an Excel COM add-in (from new project) which I'm presuming is what you mean by an Excel Automation add-in.

Andrei Smolin writes:
I've found that the ExcelApp property of the ADXXLLModule class mentioned in that section isn't created when you add an XLL module to an existing COM add-in; it's an obvious bug in our code. Besides, that property is generated for XLLModule, not for ADXXLLModule; that bug I'll fix myself :). The ExcelApp property is generated, however, if you create an XLL add-in project from scratch.

So if I wait for the next release, the ExcelApp property will exist in a new XLL module class added to the project?

Andrei Smolin writes:
Also, please read Developing multiple Office extensions in the same project. When your assembly .......

So the assembly is loaded twice, in two separate instances and the only way to communicate between the two elements (automation and UDF) is via the Excel application property specified. So with this, it would seem that it doesn't matter whether you write an assembly containing both an automation class and a UDF class, or two separate assemblies, one for automation and one for the UDF.

I still don't understand why the get_Caller does not work. With the referenced ExcelApp I can get the active cell and it's contents without issue but the get_Caller returns a COM Error of -2146826265.

Would it be possible to get a working sample of an addin getting the calling cell?

Thanks

Colin
Posted 09 Nov, 2009 08:47:25 Top
Andrei Smolin


Add-in Express team


Posts: 18817
 Joined: 2006-05-11
Hello Colin,

Would it be possible to get a working sample of an addin getting the calling cell?


Here are test projects for you:

XLL - http://www.add-in-express.com/files/projects_pub/as/MyXLLAddin29.zip
Excel Automation Add-in - http://www.add-in-express.com/files/projects_pub/as/MyAddin312.zip

I'm using an Excel COM add-in (from new project) which I'm presuming is what you mean by an Excel Automation add-in.


Please see {Add-in Express install folder}\Docs\adxnet.pdf; in the chapter called Sample Projects, I described both the terminology and creation of COM add-ins, Excel Automation add-ins and XLL add-ins.

So if I wait for the next release, the ExcelApp property will exist in a new XLL module class added to the project?


Yes, it will. But you can add it yourself - see the code of that property in my previous post.

Sorry, if I don't reply to some of your questions; I just don't understand them. Please (please!) check the manual as the terminology is essential.


Andrei Smolin
Add-in Express Team Leader
Posted 09 Nov, 2009 15:27:58 Top
Colin Blakey


Guest


Andrei,

Thank you! The samples gave me what I need to get my addin working.

Thanks

Colin
Posted 10 Nov, 2009 09:52:06 Top