Application Caller

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

Application Caller
Get full Path of caller excel file  
Posts 1 - 10 of 15
First | Prev. | 1 2 | Next | Last 
Michael Kaden


Guest


Dear Andrei,

Following your earlier post I am using

Module.CallWorksheetFunction(ADXExcelWorksheetFunction.Caller); call it from a function defined in the XLLContainer class. 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 to get a string representing the caller's address.


This gives me:

[Filename.xlsm]SheetName!Range for example

[Formula.xlsm]Sheet1!$C$6

to be able to refer to this I Need the FullName (including Path as I would get by VBA

Application.Caller.Worksheet.Parent.FullName

thank you & regards

Michael
Posted 10 Jan, 2018 09:24:51 Top
Michael Kaden


Guest


Dear Andrei,

I have solved it now by following Eugene in the topic
Convert ADXExcelRef to Excel.Range


It seems to work perfect.

But as I put it into the XXL Module, I am calling offSet cells from there.

The comment in the manual:

Returning dates from an XLL

Nevertheless, you should be very accurate when using this approach because the Excel Object Model doesn't expect such calls to be made when a formula is calculated. If you ever run into a problem with the code above, you can create a COM add-in that uses the SheetChange event in order to parse the formula just entered and format the corresponding cells as required.


Should I call the range.offset Values & do the calculations from within the AddInModule.vb and then pass the result back to the UDF in the XLLModule instead ?

thank you & regards

Michael
Posted 10 Jan, 2018 13:06:16 Top
Andrei Smolin


Add-in Express team


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

Using the Excel object model (say, manipulating an Excel.Range object) in the context of an XLL call looks dangerous to me.

Imagine a method located in an add-in module. You can call that method from the Click event of a Ribbon button or from an Excel event (such as SelectionChange); this call will be executed in the COM add-in context. If you call that method from your XLL function that Excel invokes, the method will be executed in the context of an XLL call - this may result in an exception, crash, whatever. Even more, I *suppose* if such a call works for you, it may fail if you install an update.

I admit that my position is based on the information they deleted; they didn't provide or I'm unable to find an explanation. We have a copy of that info; check section Using the Excel Object Model in an XLL at https://www.add-in-express.com/docs/net-excel-udf-tips.php#excel-object-model-xll.


Andrei Smolin
Add-in Express Team Leader
Posted 11 Jan, 2018 09:46:10 Top
Michael Kaden


Guest


Dear Andrei,

thank you for your explanation. I have now done the following:

UDF in the XLL Module

Public Shared Function alera(Prog As String, Optional D1 As String = "") As Double

....code...

red

Select Case Prog
Case "LCVOCHC"
alera = AddinModule.CurrentInstance.FLCVOCHC(range)
.......more cases....
End Select
End Function

I moved the function FLCVOCHC into the Com AddIn Module, range is the caller address from which FLCVOCHC will retrieve range.Offset(x,y).value to do its calculation.

The above red marked code to determine the caller address will remain in the UDF in the XLL.

I hope I understood your comments correctly and the above is the correct way to handle it.

kindly comment or confirm

thank you & regards

Michael
Posted 11 Jan, 2018 12:23:43 Top
Andrei Smolin


Add-in Express team


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

Dim range As Excel.Range = _Module.ExcelApp.Range(AddressNow)

This is a call into the Excel object model from the XLL. I suggest that you perform this call *after* the XLL call completes. To do this, your XLL should call a method in the COM add-in and return a value for Excel to calculate the formula; the method should prepare data (if any) for the next step, invoke ADXAddinModule.SendMessage() passing it an integer > 1024 and return. Calling the SendMessage method actually sends a message to a hidden window that add-in Express creates for your add-in behind the scenes; when the window procedure of that window receives that message, the OnSendMessage event is raised on the add-in module. There's a delay between sending the message and receiving it; this delay allows Excel to switch out of the XLL context after your XLL completes. In other words, the event handler of the OnSendMessage event is executed in the COM add-in context. The event is the next step mentioned above. In that event handler, you are free to use any object model calls.

This mechanism allows you e.g. to fill arbitrary cells when (actually, after) an XLL function is called.


Andrei Smolin
Add-in Express Team Leader
Posted 12 Jan, 2018 06:52:09 Top
Michael Kaden


Guest


Dear Andrei,

I am not so font of delays as we work very much with iterations and execution time is already an issue.

is there another possibility without

Dim range As Excel.Range = _Module.ExcelApp.Range(AddressNow)

to pass the range of the caller from the XLL Module to the ComAddIn Module?

alera = AddinModule.CurrentInstance.FLCVOCHC(range)

If not, I would pass

alera = AddinModule.CurrentInstance.FLCVOCHC(AddressNow) = String of the Caller

and convert it to The Appropriate Excel Range in the ComAddIn Module.

But your post made me think. I was under the impression that any procedure from the XLL Module will be processed in an XLL context and any procedure from the ComAddIn will be processed in a Com context. is that not so??

However I asked myself for some time, in what context will a procedure be processed which is outside of the XLL Module and outside of the ComAddIn Module, for example a class which resides directly under the project folder?

Thank you & regards

Michael
Posted 12 Jan, 2018 09:45:35 Top
Andrei Smolin


Add-in Express team


Posts: 18825
 Joined: 2006-05-11
Michael Kaden writes:
But your post made me think. I was under the impression that any procedure from the XLL Module will be processed in an XLL context and any procedure from the ComAddIn will be processed in a Com context. is that not so??


It isn't so! Where is located the method doesn't matter. What matters is when the method is called. A method is executed in the context of the XLL call if it was invoked as a result of calls initiated by the XLL function which is invoked by Excel!

In other words, if the user enters a formula and this invokes a UDF, then a method invoked by that IDF is executed in the context of the XLL call; this also applies to any method invoked by that method; all of these methods are executed in the context of the XLL call.

The delay I mentioned above lets Excel switch to the COM add-in context where Excel is ready to accept COM calls.


Andrei Smolin
Add-in Express Team Leader
Posted 12 Jan, 2018 09:58:25 Top
Michael Kaden


Guest


Dear Andrei,

thank you for your prompt response.

This also answers my question about which context is used for procedures outside of the XLL or ComAddIn Module. If fro example a Ribbon button opens a Form which is located outside of the XLL or ComAddIn, then as the Ribbon is in the ComAddIn, the form will be run in the Com context. Did I get this right?

Previously I had UDF's in VBA (Macros) and these called Functions DLL's which had Excel Objects (getting values from Excel Ranges etc.). Never had a problem with it and I was under the impression I could replace the VBA UDF with XLL UDF.

Do I understand your post correct that if the XLL UDF makes a call to a ComAddInn Function such as

alera = AddinModule.CurrentInstance.FLCVOCHC(range)

and the ComAddInn function FLCVOCHC includes calls to Excel Objects,(i.e. getting input values for the calculation with range.offset(x,y).value2) FLCVOCHC will be executed in XLL context and the calls to Excel Objects will be a problem?

If Excel stays in XLL context until the UDF receives the return value and closes (plus a delay), then we should have had the same problem with VBA and DLL's?

Can there be a problem with large dependencies between cell's which have complex calculations.

How long will a calculation with this method take if we have, for example, a dependency tree with 1.000 UDF's, Some of the UDF's are quite complex and in the method without the delay built in we take ca.10 sec.

Finally on your suggestion, can I have a simple VB example of a UDF and a ComAddIn Function for example where the Com Function writes a value of range(caller).Offset(1,0).Value to the caller after the caller UDF is invoked?

Thank You & Regards

Michael
Posted 12 Jan, 2018 11:31:12 Top
Andrei Smolin


Add-in Express team


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

Michael Kaden writes:
... then as the Ribbon is in the ComAddIn, the form will be run in the Com context. Did I get this right?


Exactly.
Michael Kaden writes:
FLCVOCHC will be executed in XLL context and the calls to Excel Objects will be a problem?


Exactly. This is the greatest obstacle when switching from VBA-based UDFs to XLL. Because of it we recommend using an Excel Automation add-in to replace VBA-based UDFs (it has downsides of its own - check https://www.add-in-express.com/docs/net-excel-udf-tips.php#udf-choose)

Michael Kaden writes:
If Excel stays in XLL context until the UDF receives the return value and closes (plus a delay), then we should have had the same problem with VBA and DLL's?


First off, this isn't necessarily so. Secondly, I talk about the XLL context; it might be different than the context related to VBA calls.

Michael Kaden writes:
How long will a calculation with this method take if we have, for example, a dependency tree with 1.000 UDF's, Some of the UDF's are quite complex and in the method without the delay built in we take ca.10 sec.


I can't tell. I assume some workbooks may require lots of time to get calculated. A way to speed up the calls is to use async functions; see https://www.add-in-express.com/docs/net-excel-udf-tips.php#asynchronous-xlls.

Michael Kaden writes:
can I have a simple VB example of a UDF and a ComAddIn Function for example where the Com Function writes a value of range(caller).Offset(1,0).Value to the caller after the caller UDF is invoked?


I suggest that you check the VB.NET example at https://www.add-in-express.com/creating-addins-blog/2011/10/11/invoke-excel-addin-xll/. You will need to modify that solution so that 1) the XLL add-in stores the address of the cell to modify and the value to set to that cell (before calling ADXAddinModule.SendMessage) and 2) the COM add-in retrieves the stored value and applies it to the cell having the stored address (in the OnSendMessage event of the add-in module).


Andrei Smolin
Add-in Express Team Leader
Posted 15 Jan, 2018 08:28:21 Top
Michael Kaden


Guest


Dear Andrei,

thank you for the detailed response.

1.)Asynchronous XLLs

I read your article and also some other Google Sources. As we do extensively iterations in our Excel Sheets, I understand that this might inhibit using asynchronous UDF's?

2.) Using Excel Objects in XLL

Before I go the way to use the ADXAddinModule.SendMessage() method I have done some more research. I found the following article:

https://msdn.microsoft.com/en-us/library/office/bb687829.aspx

From there I read and hopefully understand it right that a limited scope of EXCEL Objects are supported by XLL and C API.

I only use the

Workbook Object, WorkSheet Object and Range Object

As these are probably the most basic Excel Objects, I assume that these are supported by XLL C API? The fact that I get no error or other problem in using these Objects in my XLL AddIN, supports my guess. With extensive search in Google I however did not succeed in finding out which Object are supported by XLL.

Do you know which Excel Objects are supported by XLL?

Or, if I am wrong and XLL does not support any Excel Objects, why do I not get any error and the Workbook/Worksheet/Range Objects work without problems in my XLL?

thank you & regards

Michael
Posted 19 Jan, 2018 07:05:28 Top

Posts 1 - 10 of 15
First | Prev. | 1 2 | Next | Last