The correct separator to use in Excel get_Range in VSTO

Question

In a VSTO project for Excel written in C#, I need to get the Range object from a string list of cells.

Here is a simplified version of the problem:

string strRange = "A1:A2,A5";
Excel.Range r = sheet.get_Range(strRange);

However since the list separator can be different from the comma in different culture settings I'm actually using this:

listSep = System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator;
string strRange = "A1:A2" + listSep + "A5";
Excel.Range r = sheet.get_Range(strRange);

My problem is when the user changes "Decimal Separator" in Excel Options > Advanced (the Application.DecimalSeparator) to match the ListSeparator, this won't work.

What is the correct way to call get_Range with a string specifying the Range?

EDIT: Slight modification to add information of my comment below.

Tinderbox

The format should always be Cell1:Cell2,Cell3 afaik - that's how the API works and should not be affected by ListSeparator. There is an alternative suggestion in this answer, however: #6155682 – Jany

When the the ListSeperator as per CultureInfo is ',' and the Application.DecimalSeparator is also ',' the separator to be used is ';'. But why? where has it been documented? – Tinderbox

@dash: No, get_Range(<string>) is affected by Culture settings. – Tinderbox

Answer 1

Not the cleanest approach, but this workaround helped me:

private static string GetRangeSeparator(Excel.Worksheet sheet)
{
     Excel.Application app = sheet.Application;
     string sRng = app.Union(sheet.get_Range("A1"), sheet.get_Range("A3")).AddressLocal;
     sRng = sRng.Replace("$", string.Empty);
     string sSep = sRng.Substring(sRng.IndexOf("A3") - 1, 1);
     return sSep;
}

Hope it'll help someone.

Tinderbox

Answer 2

You might consider using the Application.Union method to build your range. Something like (untested):

Application.Union(sheet.get_Range("A1:A2"), sheet.get_Range("A5"));

Bowfin

Yes, this is an alternative/workaround. But as mentioned in my question the problem is to find the correct separator for a range definition. I can't use the Application.Union due to its bad performance in huge data volumes. – Tinderbox