locked
VBA character encoding problem between PC/Mac RRS feed

  • Question

  • Hi all,

    I have created some macros and a UserForm in Excel 2010. Since I work in Brazil, I used some latin characters inside my VBA code to change the text in some interface elements.

    Problem is, my company works with both Macs and PCs. We are running Office 2011 also, which should support VBA.

    But when I open the file on Excel 2011 for Mac, the UserForm title and the strings encoded inside my VBA Macros have garbled characters.

    It is a typical case of wrong code page interpretation. Excel 2010 (Windows) is writing the VBA using one character encoding, but Excel 2011 (Mac) is reading it assuming a different one.

    Is it possible to coerce the VBA code inside the excel file to be saved as (for example) UTF-8 ? And read as well?
    Wednesday, December 21, 2011 1:27 PM

Answers

  • Hi Joe,

    I asked for some help from Microsoft and got below reply:

    ________________________________________________________

    The problem falls between Office, VBA and Mac.

     

    The key problem is in this statement here:

    Since I work in Brazil, I used some latin characters inside my VBA code to change the text in some interface elements

     

    Some background:
     

    1.      The Windows VBA IDE only supports typing and displaying characters in the current system locale. Code written in VBA however can store and process stings in Unicode just fine. Characters not in the current code page just cannot be typed or displayed properly in the editor, and the user will see what’s described in the forum.

    2.      Support for Mac-specific codepages on Windows is spotty, and it would depend on which machine last saved as to what code page was saved with the VBA project.

    3.      There is no way to get VBA to input and encode data from its UI as UTF-8 on Windows.

     

    A couple possible workarounds: (although there may be others)

    ·        Move the text out of the code into a hidden Excel sheet (which supports Unicode) and then set the values into the form by looking up the value in Excel

    ·        Store the text in a Unicode text file and load that file using VBA to set the form UI.

     

    In both cases, the text will appear garbled in the VBA UI (watch windows), but it should render in the user form correctly.

     

    Questions for the customer:

    ·        What is their current default system locale in Windows when she saves the project with the strings appearing correctly? (See Language for non-Unicode programs  in the Windows “Region and Language” settings)

    ·        Does setting the text in the form work anyway even though the code does not display corrected in the VBA IDE?

     

    Questions for MacBU:

    ·        What code page would be saved to the VBA project when on a Mac?

    ·        What code pages  does Mac or Mac Office respect and have translation tables for? (This would be in the support data for WideCharFromMultiByte()). 


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Saturday, January 28, 2012 2:46 AM

All replies

  • Hi Brazillian Joe,

    You might check this nice link by Ron de Bruin, you need to test if you dealing with a Windows or Mac machine. See if this helps:

    http://www.rondebruin.nl/mac.htm

     

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    • Proposed as answer by danishani Wednesday, January 18, 2012 7:34 PM
    • Unproposed as answer by danishani Thursday, January 19, 2012 3:07 AM
    Wednesday, January 11, 2012 11:46 PM
  • Thanks but that doesn't help.

    That link is to programatically determine which Excel version is processing the VBA code.

    The problem I am facing is much more basic, in the intrepreter which reads the files.

    It uses different codepages in Mac and Windows, resulting in garbles accented characters. If I type the character ç under mac for instance, it show up as ÅŒ or something under windows. Doing it the other way round also breaks the code.

    In other words, I must use ASCII only in the VBA code for it to be interpreted right under both Excel versions!

    Worse still, Modules are created automatically with the name "Módulo1" in the Brazilian version of Microsoft Office, so you can see that it promptly throws the user into this bug face-first. OK I can rename the Module, but being unable to use accented characters is a very grave bug.

    Kind of unbelievable at this day and age, on a platform supposed to be very stable and mature such as Microsoft Office.

    Wednesday, January 18, 2012 8:57 PM
  • Hi Joe,

    I asked for some help from Microsoft and got below reply:

    ________________________________________________________

    The problem falls between Office, VBA and Mac.

     

    The key problem is in this statement here:

    Since I work in Brazil, I used some latin characters inside my VBA code to change the text in some interface elements

     

    Some background:
     

    1.      The Windows VBA IDE only supports typing and displaying characters in the current system locale. Code written in VBA however can store and process stings in Unicode just fine. Characters not in the current code page just cannot be typed or displayed properly in the editor, and the user will see what’s described in the forum.

    2.      Support for Mac-specific codepages on Windows is spotty, and it would depend on which machine last saved as to what code page was saved with the VBA project.

    3.      There is no way to get VBA to input and encode data from its UI as UTF-8 on Windows.

     

    A couple possible workarounds: (although there may be others)

    ·        Move the text out of the code into a hidden Excel sheet (which supports Unicode) and then set the values into the form by looking up the value in Excel

    ·        Store the text in a Unicode text file and load that file using VBA to set the form UI.

     

    In both cases, the text will appear garbled in the VBA UI (watch windows), but it should render in the user form correctly.

     

    Questions for the customer:

    ·        What is their current default system locale in Windows when she saves the project with the strings appearing correctly? (See Language for non-Unicode programs  in the Windows “Region and Language” settings)

    ·        Does setting the text in the form work anyway even though the code does not display corrected in the VBA IDE?

     

    Questions for MacBU:

    ·        What code page would be saved to the VBA project when on a Mac?

    ·        What code pages  does Mac or Mac Office respect and have translation tables for? (This would be in the support data for WideCharFromMultiByte()). 


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Saturday, January 28, 2012 2:46 AM
  • Windows System Locale is Brazilian Portuguese (I belive it uses Windows CP-1252 amiright) Keyboard is US-International.

    The Mac OS X is in English.

    Writing in an Excel spreadsheet seems to work around the issue, but is kind of a kludgy workaround.

    I can write in the form in either version of Excel VBA and it works on the original platform. Once I open in the other platform the characters get garbled though.

    I haven't tested changing the Mac codepage to see whether the same Mac-generated file breaks if the Mac is using a different codepage. It may well be the case and would corroborate that it is a codepage problem, not a windows-vs-mac problem.

     

    Monday, January 30, 2012 6:43 PM
  • 2016, now Windows 10, office 365, latest version.

    Problem above still exists. No solution.

    I have to live by copying and pasting the macro from a text file when I change platforms so that it works. 

    Anyone found a clever workaround?

    Thanks...

    Thursday, September 15, 2016 6:26 PM
  • When you say you have to copy/paste the macro, do you mean the entire macro or just string constants or hard coded strings and equivalent.

    Did you try the suggested workarounds, if so why why don't they work for your scenario.

    Thursday, September 15, 2016 7:05 PM
  • If you red the entire thread you will understand that none of the suggestion are proper workaround for the issue. 

    The core of the matter is that Windows VBA editor ASSUMES Windows character encodings to interpret the source code and display it. 

    Excel for Mac - even the 2016 version - also ASSUMES that Mac character encodings. 

    And the codepages don't match.

    So if you edit your code on one system and it has non-ASCII characters, those characters will be broken when you edit your VBA code on the other system. 

    There are no real fixes unless the user does a rube goldberg of hoops and bounds to make it work across machines. 

    The right way to do it would be to always save the VBA code internally in a codepage which both systems could understand, such as UTF-8 or UTF-16. 

    But that would depend on software updates from Microsoft. 

    Friday, September 16, 2016 12:11 PM
  • I did read the thread but I don't follow what you're actually doing, so making suggestions is second guessing. 

    Is this about writing code like If a = b Then c = d with also specific language text to display to the user. If the latter not sure why the suggested workaround of storing the text in cells doesn't work for you. Give an example of the type of code you write which involves needing to copy/paste different macros depending on your locale.

    The VBE doesn't support Unicode but uses a legacy method to show 'international' language characters. It's a bit of a kludge but if you change your system local via control panel then the VBE can display the expected international characters as expected. Not sure if or how you can do that in Mac.

    AFAIK the only reliable way to transfer unknown language/text between different systems is to convert to UTF-8 as binary (ie bytes), transfer as bytes, and convert back again the other end. There's a pair of APIs to do that in Windows, not sure if Mac has an equivalent approach.

    Though if you're only converting between English and Brazilian systems/codepages the much simpler StrConv might work for you depending on what you're doing.

    Curiosity why is "Módulo1" a problem? 

    Friday, September 16, 2016 2:27 PM
  • Because it won't show as "Módulo1" in every systems. 

    Excel will use the wrong codepage and display as "Måßdulo1" or something. 

    This prevents the object from being referenced by code, since the name will break across systems. 

    Also, any string using non-ASCII characters like MsgBox("Módulo1") will display the text with mangled/replaced/wrong characters. 

    This is bad for interoperability. 

    Saturday, September 17, 2016 1:37 PM
  • Typically it is not important what is displayed as the module name except in callback type operations where a full hard-coded path to the routine is required. In my production work I rename all modules. FWIW in my English system Módulo1 as a module name displayed and returned as written.

    The MsgBox uses the same internal system for interpreting text as the VBE, indeed it may display text differently when transferred between different language/codepage systems, we all know that. But for the third time I still don't understand why one of the suggested workarounds doesn't work for you (store text in hidden cells or a unicode text file). I still can't imagine why you need to copy/paste entire macros.

    If you have a particular reason why you don't want to store text externally look into one of the approaches I suggested. Though if you're using Mac I guess only StrConv will work albeit not for all languages such as far East character types. If Mac has it's own way of handling these exceptions I'd be interested to know myself.
    Saturday, September 17, 2016 6:18 PM
  • It's a product defect. 

    Microsoft advertises cross-platform compatibility, although there are a bunch opf asterisks on several features which don't work well. 

    This is one more not-advertised incompatibility. 

    If you write VBA code, it should just run equally well, regardless of platform. 

    It shouldn't matter whether the platform is PC or Mac. THere is of course the exception of code which uses platform-specific features such as COM calls to Windows-only DLLs. 

    But this is not the case. Something as basic as a Module name or string content changes because of how the code page of the written code is interpreted differently, and it could cause logic to break. 

    This is a flaw at a very basic level: on which code page the code is parsed? 

    While I agree that it can more or less kludged around, I think it is also easy to agree that the product is flawed.  

    Saturday, September 17, 2016 7:18 PM
  • I'm not sure where Microsoft advertises "cross platform compatibility"or what that's supposed to mean but indeed there are many international issues to take into account. Text and 'codepage' with respect to the VBE is one of the more straightforward ones to deal with, simply store text in cells as a lookup table. Translations can be in columns, it's a well used approach.

    This is from Stephen Bullen a long time ago but much of it still applies though it's not inclusive

    http://www.oaltd.co.uk/excelprogref/ch22/progrefch22.htm


    Monday, September 19, 2016 8:38 AM
  • The issue I run into is even more serious, it's not just a mismatch between Mac and Win, but even between Mac and Mac itself.

    I use a Microsoft Excel macro to generate the code of another macro function (it creates a .bas text file), and I make use of this code in a Microsoft Powerpoint file, where I need to manually import this .bas file. This code includes symbols like: "à è é ì ò ù €" (italian keyboard)

    It runs flawlessy under Windows environment (Win Excel and Win Powerpoint) while I found out it doesn't work under Mac environment (Mac Excel and Mac Powerpoint). To have the script working I need to replace the "import Module" step with this:

    1) create empty Module

    2) open text file (.bas) > select All > Copy

    3) paste into this new Module

    This is my functioning workaround on a Mac. But "Office for Mac" should REALLY be able to read the code generated by "Office for Mac" itself, without a Copy & Paste (which works!)



    • Edited by Kar.ma Tuesday, December 19, 2017 6:12 PM
    Tuesday, December 19, 2017 6:09 PM
  • Yep that's a real product flaw, I just spent a few hours meddling around for a solution and reading this thread it seems there is none. So I will have to export all the strings from the code to some sheet AND avoid using accented characters in sheet names altogether. Real bummer.

    Seems to me it wouldn't be rocket science to store the code files in a more compatible encoding. It's short-sighted and disrespectful to the non-english speaking communities that VBA isn't reliable to use with non-english languages.

    Thursday, April 5, 2018 9:34 PM
  • O problema persiste ainda no ano de 2020.
    Seremos forçados a não usar mais o Excel?
    Wednesday, July 22, 2020 3:06 PM
  • VBA Code in Windows: 
    Application.WorksheetFunction.Min(pnlEscopo.ListObjects("tbEscopo").ListColumns("Início").Range)

    Same file in IOS:
    Application.WorksheetFunction.Min(pnlEscopo.ListObjects("tbEscopo").ListColumns("In´cio").Range)

    Any help?


    • Edited by DaviOda Wednesday, July 22, 2020 3:15 PM
    Wednesday, July 22, 2020 3:14 PM
  • I'm Brazilian too and I'm pissed off seeing how the microsoft treats the problems related to Excel for Mac and their customers.

    I developed a application in excel for Mac and I passed through several imcompabilities, and I understand the diferences between the operation systems, but this specific problem is not that.

    The latin character I write on Mac is not interpreted on Windows pc, I believe it is just a matter of a code page, but we don't have the option to change it!

    And microsoft does't look to their customers at all. This post has 9 yers!!!

    I don't believe it!!

    Tuesday, December 22, 2020 12:21 PM