How to speed up UDF with large arrays

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

How to speed up UDF with large arrays
Excel UDF arrays 
Larry X


Serious Developer


Posts: 34
 Joined: 2011-04-22
function TcoMyAutomationTest1.MySum(var Range: OleVariant): OleVariant;
var
i,j: integer;
ASum: double;
begin
ASum := 0;
for i:=1 to Range.Rows.Count do
for j:=1 to Range.columns.count do
ASum := ASum + Range.Cells[i,j].value;
Result := ASum;
end;

This function is considerably slower than the Excel SUM function. What can I do (if anything) to improve its performance? Thanks.
Posted 17 May, 2011 20:25:01 Top
Dmitry Kostochko


Add-in Express team


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

Sorry but I don't know how exactly the SUM function works so I cannot advise you anything of value.

BTW, you can use native Excel functions in your code. Please see the sample below: 

function TADX_UDFSample.MySum(Param1: OleVariant): OleVariant;
begin
  Result := Self.COMAddInModule.ExcelApp.WorksheetFunction.Sum(Param1,
    EmptyParam, EmptyParam, EmptyParam, EmptyParam,
    EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam,
    EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam,
    EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam,
    EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam,
    EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam);
end;
Posted 18 May, 2011 02:03:28 Top