Range accepts sometimes only semicolons instead of commas

Question

I have reduced my problem to the following code example. I am using a German Excel version in which separators in normal Excel formulas are semicolons ";" instead of "," (e.g. =SUMME(A1;A3) instead of =SUM(A1,A3)).
Now the code which works different from time to time:

Sub CommasDoNotWorkAnymore()

Dim a()
Dim i%

a = Array("A1,A3,A5", "B1", "B2")
i = 0

Debug.Print Sheets(1).Range(a(i)).Address
End Sub

Normally, when starting Excel, this code works. But sometimes Excel seem to switch the accepted separators used in the Range() to semicolons untill I restart Excel. This occurs most times when rerunning the code after a runtime error.

Is this a general Excel bug? Does anybody know what is behind this behaviour? Is there some Excel-wide "local option" for the Range class?

EDIT: I just tried to convert the a(i) with CStr(a(i) but this does also not work. So no ByRef kind of problem...

Answer 1

If you want to control it, check first what separator is currently in use. What I guess is that you want to know the list separator:

Application.International(xlListSeparator)

Check other separators here: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-international-property-excel

The other time I had a problem with identifying decimal separator in VBA. Finnally I was able to get it in this way:

Function GetVBAdecimalSep()
    Dim a(0) As Variant
    a(0) = 1 / 2
    GetVBAdecimalSep = Mid(a(0), 2, 1)
End Function

Changing separator not always works. Please see this: Changing decimal separator in VBA (not only in Excel)

The best solution is to check/change locale, even temporary.

Application.LanguageSettings.LanguageID(msoLanguageIDUI)

gives the LCID which would be 1033 for English (US)