none
Using VBA System in English and Polish RRS feed

  • Question

  • I have created a system, using vba 7.0 (in English) via Excel 2010 for a company based in Poland. For the system to run correctly the company have been resetting their language to English (rather than Polish). Is there a more elegant way of getting around this problem.

    Also, they have recently upgraded to office 2013 (from 2010). Can this affect the above problem?

    Tuesday, April 11, 2017 11:12 AM

Answers

All replies

  •  the company have been resetting their language to English (rather than Polish).

    Why? VBA speaks English all around the world, and your code must consider the country-specific settings using e.g. Application.International etc.

    Andreas.


    Tuesday, April 11, 2017 12:47 PM
  • Hi Andy,
     
    A very good explanation of working with international users in VBA is given by
    Stephen Bullen:
     
     
     
    Tuesday, April 11, 2017 1:42 PM
  • Hi Andy Walker 60,

    lots of things can be affected and produce an error.

    so you need to take care about all the things before you develop the code for international users.

    best way you try to develop a separate code block for particular country and then try to run it according to user.

    you can try to use Country code to identify the user and then run the code.

    Sub Code()
       Country_Code = Application.International(xlCountryCode)
       If Country_Code = 1 Then
          MsgBox ("Hello")
       ElseIf Country_Code = 34 Then
          MsgBox ("Hola")
       End If
    End Sub
     

    Reference:

    Creating Macros for Different Language Versions

    you can also try to use "Application.LanguageSettings.LanguageID(msoLanguageIDUI)" to identify the language.

    example:

    Sub Test2()
        Select Case Application.LanguageSettings.LanguageID(msoLanguageIDUI)
        Case 1043: MsgBox "Run code for Dutch"
        Case 1049: MsgBox "Run code for Russian"
        Case Else: MsgBox "Run code for English (default)"
        End Select
    End Sub

    Reference:

    LanguageSettings.LanguageID Property (Office)

    you can use Application.International Property which Returns information about the current country/region and international settings.

    MsgBox "The decimal separator is " & _ 
     Application.International(xlDecimalSeparator)

    Reference:

    Application.International Property (Excel)

    you can refer link below for more details.

    Excel version and Office language settings

    also try to refer the link suggested by Jan Karel Pieterse [MVP] will be useful for you to get deep understanding regarding this issue.

    then you had mentioned that,"they have recently upgraded to office 2013 (from 2010). Can this affect the above problem?"

    if they keep all the settings are same as Excel 2010 and use English language then I don't think version upgrade will cause any issue.

    if your code is dependent on specific version then it may cause some issues when they try to run it in different version.

    if you have this kind of issue then let us know about that , we will try to suggest you further to solve the issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 12, 2017 2:14 AM
    Moderator
  • Hi Jan

    Thanks for response. It helped somewhat. I now have updated the code to reset the decimal separator to "." before proceeding (was "," on PC's in Poland office) and this has resolved some problems. However, I have noticed that when passing decimal numbers (as opposed to whole integers) such as "21.04" to CDbl("21.04") it creates an error (rather than recognise as a Double), even though I have reset the decimal point with:

       Application.DecimalSeparator = "."
       Application.UseSystemSeparators = False

    General sequence:

    On my PC using Office 2013 with decimal separator = ".":

    CDbl("21.04") = 21.04

    CDbl("21,04") = 21.04

    On my PC using Office 2013 with decimal separator = ".":

    CDbl("21,04") = 21.04

    CDbl("21.04") = ERROR

    I do not understand why it functions differently. Any guidance appreciated.

    Andy

    Tuesday, May 23, 2017 11:06 AM
  • Hi Andy,
     
    When converting from a string to a number (and vice versa), VBA uses the
    Windows locale settings for the decimal separator.
     
    So if Windows is set to use the comma, CDbl("21,04") yields a number and
    CDbl("21.04") gives an error.
     
    Basically, you should not need to convert any decimal separators as long as you
    start out with a valid number and convert it to text and the other way around.
     
    I tried this:
     
    Sub foobar()
        Dim sDec As String
        Dim bUseSys As Boolean
        bUseSys = Application.UseSystemSeparators
        sDec = Application.DecimalSeparator
       
        Application.DecimalSeparator = "."
        Application.UseSystemSeparators = False
        Debug.Print CDbl("21.04")
        Debug.Print CDbl("21,04")
        Application.UseSystemSeparators = bUseSys
        Application.DecimalSeparator = sDec
    End Sub
     
    it gives this output for me in both Excel 2010 and 2016:
    21.04
    2104
     
    Windows is set to use the period as decimal separator.
    If I flip windows to use the comma and leave the code unchanged, I get:
     
     2104
     21,04
     
    if I modify the code and set the APplication.DecimalSeparator to use the comma:
     
     2104
     21,04
     
    So the Application.DecimalSeparator setting has no effect at all here, exactly
    what Stephen describes on the page I referred you to. Windows regional settings
    control what VBA does here. Nothing else.
     
     
    Tuesday, May 23, 2017 2:15 PM