Accessing AddinModule object and ExcelApp from UDF functions

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

Accessing AddinModule object and ExcelApp from UDF functions
 
Pierluigi




Posts: 2
Joined: 2022-02-19
Dear Sirs,
I have a question. We are developing a multithread Excel plug-in starting from your well made COM integration framework and all is going well.

In our Excel plug-in we have defined some custom UDF (and also RTD) functions. From these functions we need to call your standard objects class to get the active cell/range, returning not only a single value but changing several others cells in the active worksheet directly from the functions itself.

To do this we usually use the AddinModule object and ExcelApp.ActiveSheet, but COM add-in and UDF functions works from different application domains.

To manage this, we use Self.COMAddInModule as TAddInModule and it works fine, calling methods, etc.. We was able to access to the active sheet selected cell as also stated in one forum post: Sender cell and UDF post

Now, trying to change several other cells using, for instance, something like this:

(Self.COMAddInModule as TAddInModule).ExcelApp.Cells.item[1,1] := ....

leads to an OLE Error.

What is wrong? Is it possible to access and set other cells within UDF functions call? We are developing on RAD Studio a native Delphi VCL application.
Any help and suggestion is really appreciated.
Many thanks.

Pierluigi
Posted 19 Feb, 2022 05:55:37 Top
Andrei Smolin


Add-in Express team


Posts: 18338
Joined: 2006-05-11
Hello Pierluigi,

Pierluigi writes:
leads to an OLE Error.


Are there any details?

Regards from Poland (CET),

Andrei Smolin
Add-in Express Team Leader
Posted 21 Feb, 2022 07:10:47 Top
Pierluigi




Posts: 2
Joined: 2022-02-19
Hi Andrei,
I try to give you a picture.

Usually, all works fine calling AddinModule methods or using ExcelApp properties from a TadxExcelTaskPane class.

So, inside a TadxExcelTaskPane class, something like this works:

    somevariable := (AddinModule as TAddInModule).some_method;
    user_selection := ExcelRange(ExcelApp.ActiveWindow.Selection);  
    (ExcelApp.ActiveSheet as Excel2000._Worksheet).Cells.item[1, t+1]  := {any kind of assignment}


Doing the same within an UDF function works only for the AddinModule method call. So:

    (Self.COMAddInModule as TAddInModule).some_method;


works, but this

    var rnd : ExcelRange;
    rng := ExcelRange((Self.COMAddInModule as TAddInModule).ExcelApp.ActiveWindow.Selection);

or

    var ExcelApp : TExcelApplication;
    ExcelApp := (Self.COMAddInModule as TAddInModule).ExcelApp;


doesn’t work. The “rng” variable does not correctly point to the right instatiated object while as soon as you try to address a cell using the ExcelApp variable it will raise an an OLE error exception.

It’s clear that the AddinModule objects are outside the UDF function. So, to address the actually selected range (the cell where the user has enter the UDF function) it’s needed to do address the caller property with the right lcid parameter:

    var caller: OleVariant;
    caller := Self.COMAddInModule.ExcelApp.Caller[EmptyParam, adxLCID];
    if Not VarIsError(caller) then begin
      if (TVarData(caller).VType = varDispatch) then 
        begin
          rng := IDispatch(TVarData(caller).VDispatch) as ExcelRange;
          // do something, etc..  It works.

        end;
      end;


The Excel Range above is correctly initialized and works. Now, what is the right method to to address the Self.COMAddInModule.ExcelApp oject correctly from the Self.COMAddInModule.ExcelApp.Caller property, retriving the right instantiated object and not a range? The goal is to use, as from TadxExcelTaskPane class, the ExcellApp object as usual but within the UDF function or other ones.

Many thanks for your support.
Posted 24 Feb, 2022 04:18:06 Top
Andrei Smolin


Add-in Express team


Posts: 18338
Joined: 2006-05-11
Hello Pierluigi,

We've reproduced the issue. But we have no solution to it. From my experience, such behavior is unexpected.

I would try to handle SelectionChange, get the address of the selected cell(s) and store that string somewhere. When the UDF is called, you can get that address and get the corresponding Range object from the address.

Regards from Poland (CET),

Andrei Smolin
Add-in Express Team Leader
Posted 02 Mar, 2022 08:25:35 Top