Sender cell and UDF.

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

Sender cell and UDF.
 
Thomas Horstmann




Posts: 12
 Joined: 2020-08-24
I have got an Excel UDF. Is there a way to locate the cell which sends/calls the function, when I calculate it?
Posted 08 Sep, 2020 06:41:24 Top
Dmitry Kostochko


Add-in Express team


Posts: 2875
 Joined: 2004-04-05
Hi Thomas,

The Excel Object Model has the Caller property:
https://docs.microsoft.com/en-us/office/vba/api/excel.application.caller

Please see below a small code sample below: 

function TADX_XLFunction.MyFunc(const param0, param1, param2: OleVariant): OleVariant;
var
  caller: OleVariant;
  rng: ExcelRange;
  addr: WideString;
begin
  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;
      addr := '+++ ' + rng.Address[true, true, xlA1, false, false];
      OutputDebugString(PWideChar(addr));
    end;
  end;

...
Posted 08 Sep, 2020 10:53:06 Top