|
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: https://www.add-in-express.com/forum/read.php?FID=1&TID=15985
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 |
|
|
Andrei Smolin
Add-in Express team
Posts: 19122
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 |
|
|
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. |
|
|
Andrei Smolin
Add-in Express team
Posts: 19122
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 |
|
Forums
Topics
Search
Help
Login
Register