How to Override System List Separator for Excel Variant Evaluation

I am working with Excel automation in Delphi and facing an issue with the List Separator used in Excel formula evaluation. My goal is to override the system-defined list separator and set my own separator in code, but Excel continues to use the system's list separator.

Here is my code:

procedure TForm6.Button2Click(Sender: TObject);
var
  lExcel: TExcel;
  lstrResult: String;
  lnResult: Integer;
begin
  ShowMessage(FormatSettings.ListSeparator);

  FormatSettings.ListSeparator := ',';
  FormatSettings.ThousandSeparator := ',';
  FormatSettings.DecimalSeparator := '.';

  ShowMessage(FormatSettings.ListSeparator);

  lExcel := TExcel.GetInstance;
  lExcel.Start;
  lExcel.OpenFile('C:\Testing\test.xls');

  lstrResult := lExcel.V.Evaluate('=if(len(N6)>0,5,6)');
  //lstrResult := lExcel.V.Evaluate('=if(len(N6)=0,N6,"ABC")');

  lExcel.Shutdown;
end;

Even though I explicitly set FormatSettings.ListSeparator, Excel still uses the system-defined list separator (from the regional settings).

This causes issues when evaluating formulas like:

=IF(LEN(N6)>0,5,6)

because if the system separator is different (e.g., ; instead of ,), the formula fails. How can I override the system's list separator for Excel formula evaluation in Delphi, so that my code always uses ',' as the list separator, regardless of the system settings?

In the code, TExcel is a custom wrapper class for Excel automation in Delphi. It uses CreateOleObject('Excel.Application') to interact with Excel.

Start initializes the Excel instance.
OpenFile opens an Excel file.
V is a Variant holding the Excel COM object, allowing interaction with Excel methods, including Evaluate.

Regarding Evaluate, while the standard Application.Evaluate method in Excel always expects , as the separator, my issue is that Excel still uses the system-defined list separator in some cases, particularly with Evaluate. I'm looking for a way to override this behavior in Delphi code so it does not depend on the system's locale settings.

I appreciate any guidance or workarounds for this issue.

Solution

My understanding is that the separators used in Excel cannot be changed programmaticaly, but you can ask Excel which separator is currently used, and then adapt your code to it.

If you use OLE automation (which I guess you do, although it is not visible from your code and not stated), and your variable V is an OleVariant that contains a reference to the Excel object, then you can get Excel's current list separator like this:

const
  xlListSeparator = 5;
var
  ListSep: string;
begin
  ListSep := V.Application.International[xlListSeparator];
end;

The various parameters available are described here: https://learn.microsoft.com/en-us/office/vba/api/excel.application.international

I found out the constant value 5 by running this macro in Excel: Range("A1").Value = Application.International(xlListSeparator)