How to set a range of different formulas to an Excel range from Delphi code?

Question

I try to set a range of different formulas, but it does not work via Delphi.

VBA Test Code

To illustrate this, I have some VBA test-code that I can execute directly in Excel:

Public Sub Test()
    Range(Cells(1, 1), Cells(1, 1)).Formula = "=Complex(1, 1.2)"

    Dim vFormula(1 To 2) As Variant
    vFormula(1) = "=Complex(0, 0.1)"
    vFormula(2) = "=Complex(1, 1.2)"

    Range(Cells(2, 1), Cells(2, 2)).Formula = vFormula
End Sub

and it works as expected:

Notes:

• cell A1 contains a single formula
• cells A2, B2 have been set with one call using a variant array of strings
• also note, that the Excel installation is in German and that the English formula Complex is shown in German KOMPLEXE in the screenshot - sweet

Delphi Test Code

Now I try to do the same via Delphi:

procedure TForm1.btnAddRangeFormulasClick(Sender: TObject);
var
  I: Integer;
  VarArr: Variant;
begin
  FWorksheet.Range['A1', 'A1'].Formula := '=Complex(1, 1.2)';

  VarArr := VarArrayCreate([0, 1], varOleStr);
  VarArrayPut(VarArr, '=Complex(0, 0.1)', [0]);
  VarArrayPut(VarArr, '=Complex(1, 1.2)', [1]);

  FWorksheet.Range['A2', 'B2'].Formula := VarArr;
end;

But this does not work as expected:

Notes:

• the single formula in A1 works as expected
• but the variant array that I have set to A2, B2 has not been converted

Any ideas what I am doing wrong and how to fix this?

I am using:

• Windows 10 Pro 64-bit English locale
• Excel 2007 German
• Delphi 10.2
  • Excel TLB: Microsoft Excel 15.0 Object Library

Update

Another try is to use the Locale dependant formula in Delphi:

procedure TForm1.btnAddRangeFormulasGerClick(Sender: TObject);
var
  VarArr: Variant;
begin
  FWorksheet.Range['A1', 'A1'].Formula := '=Komplexe(1, 1.2)';

  VarArr := VarArrayCreate([0, 1], varOleStr);
  VarArrayPut(VarArr, '=Komplexe(0, 0.1)', [0]);
  VarArrayPut(VarArr, '=Komplexe(1, 1.2)', [1]);

  FWorksheet.Range['A2', 'B2'].Formula := VarArr;
end;

this also does not work right away:

Notes:

• The formulas show up as unknown #Name
• But when I then enter each cell and exit again (without any changes) the data show up correctly (recalculation does not help) - which is not a big surprise, it is like directly entering the formulas in Excel
• Note, that it also does NOT work when I use FormulaLocal instead of Formula

Answer 1

I must create a variant array of varVariant (instead of varOleStr) like this:

VarArrayCreate([0, 1], varVariant);

Then all my use-cases including the automatic formula translation work!

Thanks @Vityata

Answer 2

In VBA, .Formula takes the original English formula and it transfers it to Excel. Then, based on the local Excel installation, it is "translated". Thus, =Complex() becomes =Komplexe() in German.

Obviously in Delphi, this works in a similar way. .Formula translates correctly your English formula to German, thus A1 is ok. The VarArrayPut probably takes Text and not Formula, thus it should be at the local language:

You can give it a try:

VarArrayPut(VarArr, '=Komplexe(0, 0.1)', [0]);

As a last resort, if you can find an English installation of Excel, your Delphi code should be working. To get some ideas about the Excel local formula's translation, this is how a standard =IF() formula gets translated:

• .Formula "=IF(B1=C1,""Equal"",""Not equal"")"
• .FormulaR1C1 "=IF(RC[1]=RC[2],""Equal"",""Not equal"")"
• .FormulaLocal "=WENN(B1=C1,""Equal"",""Not equal"")"
• .FormulaR1C1Local "=WENN(ZS(1)=ZS(2),""Equal"",""Not equal"")"

Source (disclaimer - my site)