Referencing a cell or a range through VCL add-in function

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

Referencing a cell or a range through VCL add-in function
 
gui




Posts: 14
 Joined: 2016-05-11
Hi,

I followed the steps from https://www.add-in-express.com/docs/vcl-excel-automation-addins.php
Especially this Delphi code: 

function TcoMyExcelAutomationAddin1.MyFunc(var Range: OleVariant): OleVariant; 
begin 
  Result := 0; 
  case VarType(Range) of 
    varSmallint, varInteger, varSingle, 
    varDouble, varCurrency, varShortInt, varByte, 
    varWord, varLongWord, varInt64: Result := Range * 1000; 
  else 
    try 
      Result := Range.Cells[1, 1].Value * 1000; 
    except 
      Result := CVErr(xlErrValue); 
    end; 
  end; 
end;


I'm interested in accessing cells' properties directly from Delphi code. So, in the code above, the interesting line is: 
Result := Range.Cells[1, 1].Value * 1000;


However, it seems that it is never called.

So my question is: how do I access cells from such a TcoMyExcelAutomationAddin1.Function.

For instance, in Excel, I would type: 

=MyFunc(A2)
or 
=MyFunc(A3:B4)


and Excel would pass the correct cell or range to the MyFunc Delphi function:

And 
function TcoMyExcelAutomationAddin1.MyFunc(var Range: OleVariant): OleVariant; 
begin 
 // What code should I use for Range to be able to read cells properties??

end;


Any idea whether it's possible?

Thank you!
Posted 26 Mar, 2018 19:21:39 Top
Andrei Smolin


Add-in Express team


Posts: 18830
 Joined: 2006-05-11
Hello gui,

Type library: 
[
id(0x000000C9)
]
HRESULT _stdcall MyFunc([out, retval] VARIANT * Result, [in] VARIANT * Range );


Code: 

 function TcoMyUDF.MyFunc(var Range: OleVariant): OleVariant;
var
  callerRange: Excel2000.ExcelRange;
  addr: WideString;
  passedRange: Excel2000.ExcelRange;
begin
  Result := 0;
  callerRange := IDispatch(OleVariant(self.COMAddInModule.ExcelApp.DefaultInterface).Caller) as Excel2000.ExcelRange;
  addr := callerRange.Address[True, True, Excel2000.xlA1, EmptyParam, EmptyParam];
  OutputDebugStringW(PWideChar('!!! Caller address:' + addr));
  //OutputDebugStringW(PWideChar('!!! Formula:' + WideString(callerRange.Formula)));
  //OutputDebugStringW(PWideChar('!!! Value:' + WideString(callerRange.Value)));

  case VarType(Range) of
    varSmallint, varInteger, varSingle,
    varDouble, varCurrency, varShortInt, varByte,
    varWord, varLongWord, varInt64: Result := Range * 1000;
  else begin
    passedRange := IDispatch(TVarData(Range).VDispatch) as Excel2000.ExcelRange;
    addr := passedRange.Address[True, True, Excel2000.xlA1, EmptyParam, EmptyParam];
    OutputDebugStringW(PWideChar('!!! Passed address:' + addr));
    try
      Result := passedRange.Cells.Item[1, 1].Value * 1000;
    except
      Result := CVErr(xlErrValue);
    end;
    end;
  end;
end;


This produces the following debug output:

[4452] !!! Caller address:$A$2
[4452] !!! Passed address:$A$1

Note that accessing callerRange.Value produces an exception.


Andrei Smolin
Add-in Express Team Leader
Posted 27 Mar, 2018 03:53:53 Top
gui




Posts: 14
 Joined: 2016-05-11
Hi,

Thank you very much for your code. I was able to do what I wanted thanks to it!
Posted 29 Mar, 2018 15:36:43 Top
Andrei Smolin


Add-in Express team


Posts: 18830
 Joined: 2006-05-11
You are welcome!


Andrei Smolin
Add-in Express Team Leader
Posted 30 Mar, 2018 03:30:22 Top