Is there a way to change the language of an Excel application/workbook during the execution of a function? The problem I face is the following:
In my organization there’s of course a lot of work that has to be shown in excel tables. We have functions (written in APL) that do most of the calculations and the result is then exported to an Excel sheet. We now face the problem that our code‘s functionality depends on the language settings of the user as we get errors in the following example.
Let's say we want to put the value 1 in the ranges A1:B2 and A5:B10, and we do that in one command
ActiveSheet.Range[⊂'A1:B2;A5:B10'].Value2←1
This works fine if Excel is in German but after I switched my Excel to English I got an error. The problem is the semicolon ";" that there is in the code which was written with German settings in mind. When I change that to a comma
ActiveSheet.Range[⊂'A1:B2,A5:B10'].Value2←1
I manage to get it work in English but not in German.
So my question is the following: Is it possible to get and set Excel's language in the function? If it is not German then I switch it to English, do my work, and in the end switch it back to whatever it was.
I wrote the example in APL as it is the one we use but a solution in other language (e.g. VB) would be fine as I guess it is more of an Excel question.
Getting the language is easy, as Michal mentioned. But you can skip a few steps, as you don't really want to know the language but only the list separator (and maybe decimal separator and a few others):
Dim delimiter As String
delimiter = Application.International(xlListSeparator)
This will work for any language, so you don't need to check for German, Dutch etc. Similarly, you can get other regional settings like xlDecimalSeparator. See https://learn.microsoft.com/en-us/office/vba/api/excel.application.international for the full list.
Setting the language or the list separator is more difficult. However, there is a trick to get consistent behaviour: setting the decimal separator to "," will force the list separator to be ";", if it was a "," in the current language.
Application.UseSystemSeparators = False
Application.DecimalSeparator = ","
This may not be the setting you want, but at least it is consistent so you can modify your source files accordingly.