Using Array Formula

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

Using Array Formula
 
Frank Jepsen




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

I tried to use a array formula udf in a com add-in.
Ridl looks like this
    HRESULT _stdcall TestMulti([in] long Rows, [in] long Cols, [out, retval] VARIANT* Result);
and IMPL looks like this
function TcoExcelZDataAddin.TestMulti(Rows, Cols: Integer): OleVariant;
var
  i, j: Integer;
  Arr: Variant;
begin
  try
    Arr := VarArrayCreate([1, Rows, 1, Cols], varVariant);
    for i := 1 to Rows do
    begin
      for j := 1 to Cols do
      begin
        VarArrayPut(Arr, IntToStr(i) + ',' + IntToStr(j), [i,j]);
      end;
    end;
    Result := Arr;
  except
    on E:Exception do
    begin
      Result := VarArrayCreate([1, 1], varVariant);
      Result[1] :=E.Message;
    end;
  end;
end;

In a Excel sheet I entered {=TestMulti(3;4)} with Ctrl-Shift-Enter.
But I only got 1,1 in the first cell.

What am I doing wrong?

Best regards
Frank
Posted 01 Aug, 2016 05:16:45 Top
Andrei Smolin


Add-in Express team


Posts: 18823
 Joined: 2006-05-11
Frank,

Your code works for me. I select a 3rowsx4columns range, enter the formula = TestMulti(3,4) and press Ctrl+Shift+Enter.


Andrei Smolin
Add-in Express Team Leader
Posted 02 Aug, 2016 12:06:45 Top
Frank Jepsen




Posts: 14
 Joined: 2016-07-05
It was my fault. I used array formulas for the first time and didn't know that I had to select multiple cells before hitting Ctrl-Shift-Enter. With that the function above works as expected.

Regards Frank
Posted 02 Aug, 2016 12:07:23 Top
Andrei Smolin


Add-in Express team


Posts: 18823
 Joined: 2006-05-11
No problem!


Andrei Smolin
Add-in Express Team Leader
Posted 03 Aug, 2016 04:54:08 Top