How to return the Array in Excel addin

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

How to return the Array in Excel addin
 
Subscribe
Zewus Choe




Posts: 10
 Joined: 2024-05-16
How to return the result array like

={"A",1;"B",2}
Posted 16 May, 2024 09:29:28 Top
Andrei Smolin


Add-in Express team


Posts: 19011
 Joined: 2006-05-11
Hello Zewus,

Check our blog at https://www.add-in-express.com/creating-addins-blog/excel-read-update-cells/.

Regards from Poland (GMT+2),

Andrei Smolin
Add-in Express Team Leader
Posted 16 May, 2024 16:54:45 Top
Zewus Choe




Posts: 10
 Joined: 2024-05-16
Hello

I'm not asking you to enter the values of multiple cells at once.

I am asking that the COM add-in function returns values to one cell and inputs values of several cells at once.

As if you entered ={"A,"1;"B,"2} in one cell.
Posted 16 May, 2024 23:46:04 Top
Andrei Smolin


Add-in Express team


Posts: 19011
 Joined: 2006-05-11
Hello Zewus,

I suppose you are looking for Range.ArrayFormula. Is this so?

Regards from Poland (GMT+2),

Andrei Smolin
Add-in Express Team Leader
Posted 20 May, 2024 09:31:50 Top
Zewus Choe




Posts: 10
 Joined: 2024-05-16
Thanks for the information Andrei.

Best Regards, Zewus
Posted 20 May, 2024 10:32:08 Top
Zewus Choe




Posts: 10
 Joined: 2024-05-16
Hello

It was checked in the VBA that formula2 properties were used to enter the array formula into the a cell.

Formula properties exist. Is there an available formula2 ?

--> ExcelApp.ActiveCell.Formula2 := '=ArrayFunc(..)';

Best Regards
Posted 21 May, 2024 04:54:58 Top
Andrei Smolin


Add-in Express team


Posts: 19011
 Joined: 2006-05-11
Hello Zewus,

An array formula should be entered to the Range.FormulaArray property; see https://learn.microsoft.com/en-us/office/vba/api/excel.range.formulaarray.

The difference between Range.Formula (https://learn.microsoft.com/en-us/office/vba/api/excel.range.formula) and Range.Formula2 properties is described at https://learn.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/range-formula-vs-formula2.

By default, you use the type library of Excel 2000 where Range.Formula2 is missing. Still, you can use late binding to use it.

Regards from Poland (GMT+2),

Andrei Smolin
Add-in Express Team Leader
Posted 24 May, 2024 08:03:47 Top
Zewus Choe




Posts: 10
 Joined: 2024-05-16
Hello

I don't understand the 'Still, you can use late binding to use it.' you said.

Can you write about this in detail?


---

When the ribbon button is clicked, =@myFunc("test") is entered into the active cell in Excel.

Therefore, only the "1,1" value is returned in activecell.

I'm looking for Formula2 to solve this.





function TWksFunc.MyFunc(var Range: OleVariant): Integer;
var
i, j: Integer;
Arr: Variant;
begin
try
Arr := VarArrayCreate([1, 5, 1, 5], varVariant);
for i := 1 to 5 do
begin
for j := 1 to 5 do
VarArrayPut(Arr, IntToStr(i) + ',' + IntToStr(j), [i,j]);
end;
Result := Arr;
except
on E:Exception do
begin
Result := VarArrayCreate([1, 1], varVariant);
Result[1] := E.Message;
end;
end;
end;

procedure TAddInModule.adxRibbonTab1Controls0Controls0Click(Sender: TObject; const RibbonControl: IRibbonControl);
begin
ExcelApp.ActiveCell.Formula := '=MyFunc("Test")';
end;
Posted 24 May, 2024 08:37:55 Top
Andrei Smolin


Add-in Express team


Posts: 19011
 Joined: 2006-05-11
Hello Zewus,

Late binding - this relates to the general programming area, not to Add-in Express. I suggest that you google for using late binding in Delhi. Say, I've googled out this page: https://stackoverflow.com/questions/17383304/which-one-is-better-in-terms-of-performance-the-early-binding-or-late-binding-i.

Regards from Poland (GMT+2),

Andrei Smolin
Add-in Express Team Leader
Posted 27 May, 2024 09:50:03 Top