Trouble with CalculateFull - ExcelDnaUtil.Application.Caller property not set to the UDF cell

Feb 13, 2013 at 12:41 PM
Hi Govert,
Help pleeeaaassee!

Some Context: I Have solution with two projects(.Net 4.0):
  • Project (A) is a class library project with UDF functions using ExcelDna (with nugget -Thanks for that by the way! Very useful!).
  • Project (B) is a VSTO Add-in project deployed with click-once. In order to get the UDF functions deployed with the add-in, I have added the “packed xll” created by (A) to this project with Build Action set to content and copy to output set to always. By dooing this the packed xll is also deployed with the add-in, this in turn allowed me to use “RegisterXLL” to register the UDF xll when the add-in is loaded, more specifically hooking the OnStartup event of the ThisAddIn class created by the VSTO scaffolding.
My Problem!!!

I actually have two problems and they might be related.
  1. Trouble with “CalculateFull” of ExcelDnaUtil.Application. It works in the sense that the UDF functions are being called/executed, but… the caller property of the application object I get in the UDF through ExcelDnaUtil.Application is set to the cell/range of the active cell when CalculateFull is called instead of the cell containing the UDF being executed.
    This is not what happens if one calls CalculateFull on the Application object supplied by excel in a VBA xla add-in. In fact I have such an add-in, with a button which does just that, and if I click on it, the various UDF functions of (A) are called with the caller property set to the respective cell in which the UDF resides.
  2. When I open a saved excel document with UDF functions on it they are not executed immediately. Again this is not what happens when I open a saved excel document with UDFs from a XLA add-in.
I am at a loss as to why these behaviors are different and would much appreciate some help.

Regards,
Henrique Nunes

PS- I have said this before, but it´s never enough... Thank You! Superb Job!
Coordinator
Feb 13, 2013 at 12:58 PM
Hi Henrique,

What are you using the VSTO add-in for? your life might become simpler to move everything into the Excel-DNA add-in.

I'm not sure why the Application.Caller would be different in those contexts, but you might investigate the ExcelReference object that you get from a call to
XlCall.Excel(XlCall.xlfCaller)
I certainly expect that to always be the right cell.

The Application object you get in VBA is exactly the same COM object that you get from ExcelDnaUtil.Application. However, Excel might consider the Application.Caller to be different whether the CalculateFull is called triggered in VBA or an Excel-DNA macro, and the Application.Caller might be different for an Excel-DNA UDF or a VBA UDF.

I generally recommend that you not use the COM automation interface from inside Excel-DNA UDF functions, but I have no firm evidence of problems. (From macros, ribbon handlers etc, it should be fine.)

I don't normally expect the UDF functions to recalculate when you open a workbook. I'm not sure why or when the VBA add-in functions recalculate. I don't know the rules behind Excel's decision whether to recalculate upon opening or not - you might ask that on StackOverflow or the Excel for Developers forum. You probably should not rely on whether a sheet recalculates on opening or not.

Regards,
Govert
Feb 13, 2013 at 3:09 PM
Hi Govert,

I have obtained an excel reference object with XlCall.Excel(XlCall.xlfCaller) as you indicated and yes it has the correct cell, the one with the UDF being calculated, thank you for the help.

The 2º problem is not to problematic, especially with the calculate stuff working. :)

By the way, I am using VSTO for two reasons mainly:
  • First and most importantly, that is how the project was set-up when it “landed” on my lap due to a colleague leaving the company. I know! That is a poor excuse! But In my defense…This is my first add-in and the first time I have used (or heard of) ExcelDna and no one else in the company has knowledge in this area to help me.
    Therefore, I simply did not have the knowledge to realize the way the project was set-up was not the best way to go.
  • Second, this Add-in is supposed to be deployed through clickonce in order to simplify deployment and subsequent updates to the add-in, The VSTO Add-in project directly supports this.
I have and still am considering moving everything to an ExcellDna add-in but, as is often the case, we have some serious time constraints and the decision is not mine alone to make.

Thanks again for your help.

Regards,
Henrique
Coordinator
Feb 13, 2013 at 7:18 PM
Hi Henrique,

If everything works fine, there is no reason to move away from VSTO. But in future you might find you want to share information between the UDF part and the VSTO parts, then it becomes easier if everything lives together.

Good luck,
Govert