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.

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.

• excel
• delphi

• What you set in your Delphi environment has nothing to do with Excel. If you want to change something within Excel, do so through its objects. – Blindy Commented Mar 13 at 6:14
• ohk, and what are the available objects to do that can't find the docs – I'mSRJ Commented Mar 13 at 6:28
• What is TExcel here? Start, OpenFile and V are not in the Excel's object model. Also standard Application.Evaluate method only accepts comma as field separator, so the system locale doesn't matter. – BrakNicku Commented Mar 13 at 7:47
• updated the question, TExcel is a custom wrapper class for Excel automation in Delphi. – I'mSRJ Commented Mar 13 at 8:01
• Excel still uses the system-defined list separator in some cases in what cases? The IF formula you have shown isn't the case, can you give one specific example? – BrakNicku Commented Mar 13 at 8:33

2 Answers 2

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/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)

After researching and testing different approaches, I found a way to override the system-defined List Separator, Thousand Separator, and Decimal Separator in Delphi. The issue is that Excel always follows the system-defined locale settings, even if we modify FormatSettings.ListSeparator in our Delphi application.

Override System Separators Using Windows API

Instead of just modifying FormatSettings, we need to update the Windows locale settings dynamically. This ensures that Excel recognizes our custom separators. Below are helper functions to get and set system-wide separators using GetLocaleInfo and SetLocaleInfo.

Functions to Get and Set System Separators

function GetWindowsDecimalSeparator: Char;
var
  Buffer: array[0..3] of Char;
begin
  if GetLocaleInfo(LOCALE_USER_DEFAULT, LOCALE_SDECIMAL, @Buffer, Length(Buffer)) > 0 then
    Result := Buffer[0]
  else
    Result := '.'; // Default fallback
end;

function SetWindowsDecimalSeparator: Boolean;
begin
  Result := SetLocaleInfo(LOCALE_USER_DEFAULT, LOCALE_SDECIMAL, '.');
end;

function GetWindowsThousandSeparator: Char;
var
  Buffer: array[0..3] of Char;
begin
  if GetLocaleInfo(LOCALE_USER_DEFAULT, LOCALE_STHOUSAND, @Buffer, Length(Buffer)) > 0 then
    Result := Buffer[0]
  else
    Result := ','; // Default fallback
end;

function SetWindowsThousandSeparator: Boolean;
begin
  Result := SetLocaleInfo(LOCALE_USER_DEFAULT, LOCALE_STHOUSAND, ',');
end;

function GetWindowsListSeparator: Char;
var
  Buffer: array[0..3] of Char;
begin
  if GetLocaleInfo(LOCALE_USER_DEFAULT, LOCALE_SLIST, @Buffer, Length(Buffer)) > 0 then
    Result := Buffer[0]
  else
    Result := ','; // Default fallback
end;

function SetWindowsListSeparator: Boolean;
begin
  Result := SetLocaleInfo(LOCALE_USER_DEFAULT, LOCALE_SLIST, ',');
end;

This approach solves the issue where Excel ignores FormatSettings.ListSeparator in Delphi and continues to use system-defined separators. Using Windows API functions allows us to ensure consistency in formula evaluation across different system locales.