# Using Array Formula

That's what is more important than anything else

 Forums    Topics    Search    Help    Login    Register
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

Posts: 15495
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. Regards from Belarus (GMT+3), 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