#StackBounty: #excel #vba #excel-formula Override Horizontal vs Vertical array delimiters – International

Bounty: 50

Following up on an earlier question I had about horizontal vs vertical arrays, I have a question about it’s respective delimiters.

Problem definition:

Hereby an example of an incorrect way of comparing two arrays:

{=SUMPRODUCT(--({"Apple","Pear"}={"Apple","Lemon","Pear"}))}

The correct way, in case of an English application countrycode would be:

{=SUMPRODUCT(--({"Apple","Pear"}={"Apple";"Lemon";"Pear"}))}

Within an English version (most likely more than just English) of Excel these delimiters would respectively be a comma , for horizontal arrays and a semicolon ; for vertical ones. Plenty of online information to be found on this.

Working on a machine with a Dutch country code on it’s application however, it’t a complete other story. It does frustrate that my delimiters would both be different, respectively ; and a . Being able to rather simply retrieve the semi-colon it’s proven to be tricky to find any documentation on these delimiters for international version.

Workaround:

Not knowing these delimiters up-front makes it tricky for anyone on a variety of international versions of the application to work with these type of formulas. A rather easy workaround would be to use TRANSPOSE():

{=SUMPRODUCT(--({"Apple";"Pear"}=TRANSPOSE({"Apple";"Lemon";"Pear"})))}

Going through the build-in evaluation we can then retrieve the backslash as the column seperator. Another way would be to use the Application.International property and it’s xlColumnSeparator and xlRowSeparator.

Question

We can both override the xlDecimalSeparator and xlThousandsSeparator through Excel (File > Options > Advanced), or VBA (Application.DecimalSeparator = "-") but where can we find:

  • A place to actually see which xlRowSeparator and xlColumnSeparator are used within your own application, other than the workarounds I described.

Furthermore, is there:

  • A place to override them just like the decimal and thousand seperators
  • If not through Excel interfaces, can we brute-force this somehow through VBA?

I’m very curious if official documentation is present, and/or if the above can be done.


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.