Excel 2003 VBA program kills itself in Japan
in [Programming]
|
From: Christian Treffler on 23 Feb 2010 10:30 Hi, I wrote a VBA program for an Excel workbook. This program works just fine on my PC (US English setup), but my japanese collegues have problems. We observed that the VBA program changes on a japanese PC to the extent that syntax errors occur. This is how it happens: My code looks like this: ------------------------------------ Function ReplIllegal(ByVal txt As String) As String Dim ill As String ill = "´`@€²³°^<>\*./[]:;|=?,""" ' list of illegal characters ReplIllegal = "" txt = Replace(txt, "ä", "ae") txt = Replace(txt, "ö", "oe") txt = Replace(txt, "ü", "ue") txt = Replace(txt, "Ä", "Ae") <snip> ------------------------------------- If the workbook is opened on a japanese computer, the code looks like this ('$' stands for various japanese characters): ------------------------------------ Function ReplIllegal(ByVal txt As String) As String Dim ill As String ill = "$`@$$$$^<>$*./[]:;|=?,""" ' Japanese characters! ReplIllegal = "" txt = Replace(txt, ". , "ae") ' Syntax Error txt = Replace(txt, ". , "oe") ' Syntax Error txt = Replace(txt, ". , "ue") ' Syntax Error txt = Replace(txt, "$", "Ae") <snip> ------------------------------------- Very bad is the replacement of "ä" by ". where the closing quotation mark gets lost. That leads to a syntax error. Is there any chance to avoid this problem? The solution must be applied to the workbook. A client side solution (e.g. installation of an add-on) is not acceptable. TIA, Christian
From: Peter T on 23 Feb 2010 11:35 That doesn't sound good at all ! As a workaround try this - txt = Replace(txt, Chr$(228), "ae") ' � for your other characters, in the Immediate window type ?� and hit enter Or type in cells and return with =CODE(A1) Regards, Peter T "Christian Treffler" <CTreffler.NG.Dev0(a)gmx.de> wrote in message news:1wccff4klrcec.dlg(a)ID-425.user.individual.de... > Hi, > > I wrote a VBA program for an Excel workbook. This program works just > fine on my PC (US English setup), but my japanese collegues have > problems. We observed that the VBA program changes on a japanese PC to > the extent that syntax errors occur. > > This is how it happens: > > My code looks like this: > ------------------------------------ > Function ReplIllegal(ByVal txt As String) As String > Dim ill As String > > ill = "�`@?���^<>\*./[]:;|=?,""" ' list of illegal characters > ReplIllegal = "" > > txt = Replace(txt, "�", "ae") > txt = Replace(txt, "�", "oe") > txt = Replace(txt, "�", "ue") > txt = Replace(txt, "�", "Ae") > <snip> > ------------------------------------- > > If the workbook is opened on a japanese computer, the code looks like > this > ('$' stands for various japanese characters): > ------------------------------------ > Function ReplIllegal(ByVal txt As String) As String > Dim ill As String > > ill = "$`@$$$$^<>$*./[]:;|=?,""" ' Japanese characters! > ReplIllegal = "" > > txt = Replace(txt, ". , "ae") ' Syntax Error > txt = Replace(txt, ". , "oe") ' Syntax Error > txt = Replace(txt, ". , "ue") ' Syntax Error > txt = Replace(txt, "$", "Ae") > <snip> > ------------------------------------- > > Very bad is the replacement of "�" by ". where the closing quotation > mark gets lost. That leads to a syntax error. > > Is there any chance to avoid this problem? The solution must be applied > to the workbook. A client side solution (e.g. installation of an add-on) > is not acceptable. > > TIA, > Christian
From: Martin Brown on 23 Feb 2010 11:54 Christian Treffler wrote: > Hi, > > I wrote a VBA program for an Excel workbook. This program works just > fine on my PC (US English setup), but my japanese collegues have > problems. We observed that the VBA program changes on a japanese PC to > the extent that syntax errors occur. > > This is how it happens: > > My code looks like this: > ------------------------------------ > Function ReplIllegal(ByVal txt As String) As String > Dim ill As String > > ill = "´`@€²³°^<>\*./[]:;|=?,""" ' list of illegal characters > ReplIllegal = "" > > txt = Replace(txt, "ä", "ae") > txt = Replace(txt, "ö", "oe") > txt = Replace(txt, "ü", "ue") > txt = Replace(txt, "Ä", "Ae") > <snip> > ------------------------------------- > > If the workbook is opened on a japanese computer, the code looks like > this > ('$' stands for various japanese characters): > ------------------------------------ > Function ReplIllegal(ByVal txt As String) As String > Dim ill As String > > ill = "$`@$$$$^<>$*./[]:;|=?,""" ' Japanese characters! > ReplIllegal = "" > > txt = Replace(txt, ". , "ae") ' Syntax Error > txt = Replace(txt, ". , "oe") ' Syntax Error > txt = Replace(txt, ". , "ue") ' Syntax Error > txt = Replace(txt, "$", "Ae") > <snip> > ------------------------------------- > > Very bad is the replacement of "ä" by ". where the closing quotation > mark gets lost. That leads to a syntax error. > > Is there any chance to avoid this problem? The solution must be applied > to the workbook. A client side solution (e.g. installation of an add-on) > is not acceptable. I can tell you what the problem is. Japanese DBCS or 16bit Unicode character escapes begin with the top bit set. Having explicit top bit set single character constants in your code like that will break in a Japanese environment and swallow the immediately following character. AscW(), DBCS and Unicode may help. Another solution might be to have two versions of the code and check the machine environment to decide which one to run. Regards, Martin Brown
From: Chip Pearson on 23 Feb 2010 11:56 I would refrain from using string literals for characters not between Chr(0) and Chr(127) when doing cross-language programming. Instead, I would set up at table of numerics indicating the characters to be replaced and the replacement characters. For exampe, you could create a table like Const US_REPLACE = "34,0;14,0;48,49;50,51" Here, there are three replacement pairs, separated by semi-colons: 34,0 14,0 48,49 50,51 Each pair consists of the character code to replace and the character code by which it is to be replaced separated by commas. A replacement character of 0 is to be taken to mean a replacement with a 0 length string (not Chr(0) ). So, with these pairs, Chr(34) and Chr(14) are to be replace by vbNullString and Chr(48) replaced by Chr(49) and Chr(50) replaced by Chr(51). Then, use the GetUserDefaultLangID API function to get the user's current locale ID (1033 = US_EN, 1041 =JP_JP) and choose either the US replacement table or the Japanese replacement table. Run through those replacement pairs to get rid of illegal characters and other replacements. The code that follows does all of this. '''''''''''''''''''''''''''''''''''''' ' START CODE '''''''''''''''''''''''''''''''''''''' Public Declare Function GetUserDefaultLangID Lib "kernel32" () As Long Const US_REPLACE = "34,0;14,0;48,49;50,51" Const JP_REPLACE = "32,0;15,0;48,50;49,51" Const LANG_US_EN = 1033 Const LANG_JP_JP = 1041 Function ReplaceIllegal(Txt As String) As String Dim ReplPairs() As String Dim ReplWhat As String Dim ReplWith As String Dim OnePair() As String Dim Locale As Long Dim N As Long Dim T As String T = Txt Locale = GetUserDefaultLangID() If Locale = LANG_US_EN Then ReplPairs = Split(US_REPLACE, ";") Else ReplPairs = Split(JP_REPLACE, ";") End If For N = LBound(ReplPairs) To UBound(ReplPairs) OnePair = Split(ReplPairs(N), ",") ReplWhat = ChrW(CLng(OnePair(0))) If OnePair(1) = "0" Then ReplWith = vbNullString Else ReplWith = ChrW(CLng(OnePair(1))) End If T = Replace(T, ReplWhat, ReplWith) Next N ReplaceIllegal = T End Function '''''''''''''''''''''''''''''''''''''' ' END CODE '''''''''''''''''''''''''''''''''''''' Then, you can call this code with something like Sub AAA() Dim S As String Dim T As String S = "hello world 123" T = ReplaceIllegal(S) Debug.Print Len(T), T End Sub Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Tue, 23 Feb 2010 16:30:30 +0100, Christian Treffler <CTreffler.NG.Dev0(a)gmx.de> wrote: >Hi, > >I wrote a VBA program for an Excel workbook. This program works just >fine on my PC (US English setup), but my japanese collegues have >problems. We observed that the VBA program changes on a japanese PC to >the extent that syntax errors occur. > >This is how it happens: > >My code looks like this: >------------------------------------ >Function ReplIllegal(ByVal txt As String) As String > Dim ill As String > > ill = "�`@����^<>\*./[]:;|=?,""" ' list of illegal characters > ReplIllegal = "" > > txt = Replace(txt, "�", "ae") > txt = Replace(txt, "�", "oe") > txt = Replace(txt, "�", "ue") > txt = Replace(txt, "�", "Ae") ><snip> >------------------------------------- > >If the workbook is opened on a japanese computer, the code looks like >this >('$' stands for various japanese characters): >------------------------------------ >Function ReplIllegal(ByVal txt As String) As String > Dim ill As String > > ill = "$`@$$$$^<>$*./[]:;|=?,""" ' Japanese characters! > ReplIllegal = "" > > txt = Replace(txt, ". , "ae") ' Syntax Error > txt = Replace(txt, ". , "oe") ' Syntax Error > txt = Replace(txt, ". , "ue") ' Syntax Error > txt = Replace(txt, "$", "Ae") ><snip> >------------------------------------- > >Very bad is the replacement of "�" by ". where the closing quotation >mark gets lost. That leads to a syntax error. > >Is there any chance to avoid this problem? The solution must be applied >to the workbook. A client side solution (e.g. installation of an add-on) >is not acceptable. > >TIA, >Christian
From: Peter T on 23 Feb 2010 12:33
That looks like a much better suggestion than the workaround I proposed! One thing though, if the OP needs to cater for more than only US & Japan might need a bit extend this part > If Locale = LANG_US_EN Then > ReplPairs = Split(US_REPLACE, ";") > Else > ReplPairs = Split(JP_REPLACE, ";") > End If My "English" Locale is 2057, or the in Hex on this page 0x0809, so the If check assumed I have Japanese. http://msdn.microsoft.com/en-us/library/dd318693(VS.85).aspx However even ensuring I got this ReplPairs = Split(US_REPLACE, ";") the function did not replace the required character. Not sure if that means defining more replacement pairs for other languages, incl say GB-Eng Regards, Peter T "Chip Pearson" <chip(a)cpearson.com> wrote in message news:n318o5h2oojn8lpi8c5jcifskq6nvusmed(a)4ax.com... >I would refrain from using string literals for characters not between > Chr(0) and Chr(127) when doing cross-language programming. Instead, I > would set up at table of numerics indicating the characters to be > replaced and the replacement characters. For exampe, you could create > a table like > > Const US_REPLACE = "34,0;14,0;48,49;50,51" > > Here, there are three replacement pairs, separated by semi-colons: > > 34,0 > 14,0 > 48,49 > 50,51 > > Each pair consists of the character code to replace and the character > code by which it is to be replaced separated by commas. A replacement > character of 0 is to be taken to mean a replacement with a 0 length > string (not Chr(0) ). So, with these pairs, Chr(34) and Chr(14) are to > be replace by vbNullString and Chr(48) replaced by Chr(49) and Chr(50) > replaced by Chr(51). > > Then, use the GetUserDefaultLangID API function to get the user's > current locale ID (1033 = US_EN, 1041 =JP_JP) and choose either the US > replacement table or the Japanese replacement table. Run through those > replacement pairs to get rid of illegal characters and other > replacements. > > The code that follows does all of this. > > > '''''''''''''''''''''''''''''''''''''' > ' START CODE > '''''''''''''''''''''''''''''''''''''' > Public Declare Function GetUserDefaultLangID Lib "kernel32" () As Long > Const US_REPLACE = "34,0;14,0;48,49;50,51" > Const JP_REPLACE = "32,0;15,0;48,50;49,51" > Const LANG_US_EN = 1033 > Const LANG_JP_JP = 1041 > > Function ReplaceIllegal(Txt As String) As String > Dim ReplPairs() As String > Dim ReplWhat As String > Dim ReplWith As String > Dim OnePair() As String > Dim Locale As Long > Dim N As Long > Dim T As String > > T = Txt > Locale = GetUserDefaultLangID() > If Locale = LANG_US_EN Then > ReplPairs = Split(US_REPLACE, ";") > Else > ReplPairs = Split(JP_REPLACE, ";") > End If > For N = LBound(ReplPairs) To UBound(ReplPairs) > OnePair = Split(ReplPairs(N), ",") > ReplWhat = ChrW(CLng(OnePair(0))) > If OnePair(1) = "0" Then > ReplWith = vbNullString > Else > ReplWith = ChrW(CLng(OnePair(1))) > End If > > T = Replace(T, ReplWhat, ReplWith) > Next N > ReplaceIllegal = T > End Function > '''''''''''''''''''''''''''''''''''''' > ' END CODE > '''''''''''''''''''''''''''''''''''''' > > > Then, you can call this code with something like > > Sub AAA() > Dim S As String > Dim T As String > S = "hello world 123" > T = ReplaceIllegal(S) > Debug.Print Len(T), T > End Sub > > Cordially, > Chip Pearson > Microsoft Most Valuable Professional, > Excel, 1998 - 2010 > Pearson Software Consulting, LLC > www.cpearson.com > > > > > > > On Tue, 23 Feb 2010 16:30:30 +0100, Christian Treffler > <CTreffler.NG.Dev0(a)gmx.de> wrote: > >>Hi, >> >>I wrote a VBA program for an Excel workbook. This program works just >>fine on my PC (US English setup), but my japanese collegues have >>problems. We observed that the VBA program changes on a japanese PC to >>the extent that syntax errors occur. >> >>This is how it happens: >> >>My code looks like this: >>------------------------------------ >>Function ReplIllegal(ByVal txt As String) As String >> Dim ill As String >> >> ill = "�`@?���^<>\*./[]:;|=?,""" ' list of illegal characters >> ReplIllegal = "" >> >> txt = Replace(txt, "�", "ae") >> txt = Replace(txt, "�", "oe") >> txt = Replace(txt, "�", "ue") >> txt = Replace(txt, "�", "Ae") >><snip> >>------------------------------------- >> >>If the workbook is opened on a japanese computer, the code looks like >>this >>('$' stands for various japanese characters): >>------------------------------------ >>Function ReplIllegal(ByVal txt As String) As String >> Dim ill As String >> >> ill = "$`@$$$$^<>$*./[]:;|=?,""" ' Japanese characters! >> ReplIllegal = "" >> >> txt = Replace(txt, ". , "ae") ' Syntax Error >> txt = Replace(txt, ". , "oe") ' Syntax Error >> txt = Replace(txt, ". , "ue") ' Syntax Error >> txt = Replace(txt, "$", "Ae") >><snip> >>------------------------------------- >> >>Very bad is the replacement of "�" by ". where the closing quotation >>mark gets lost. That leads to a syntax error. >> >>Is there any chance to avoid this problem? The solution must be applied >>to the workbook. A client side solution (e.g. installation of an add-on) >>is not acceptable. >> >>TIA, >>Christian |