Excel 2003 VBA program kills itself in Japan
 

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

Excel 2003 VBA program kills itself in Japan
 

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" wrote in message
...
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

Excel 2003 VBA program kills itself in Japan
 

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

Excel 2003 VBA program kills itself in Japan
 

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
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

Excel 2003 VBA program kills itself in Japan
 

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/libr...93(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" wrote in message
...
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
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

Excel 2003 VBA program kills itself in Japan
 

Peter T schrieb:

As a workaround try this -

txt = Replace(txt, Chr$(228), "ae") ' ä

That's easy enough. Thanks to you and all the others for your inputs.

CU,
Christian

Excel 2003 VBA program kills itself in Japan
 

I confirm.
Code changes itself between English and Japanese computers.
You will not find that bug if you simply change language to Japanese.
You have to have "true" Japanese Windows.


It happens when "text code" is internally loaded and interpreted by VBE
and it happens for non-ascii characters.

just new feature.

workaround for this is to build that string dynamically not to use
non-ascii characters in code

instead of
ill = "´`@‚¬Â²Â³Â°^<\*./[]:;|=?,"""

it should be
ill = chrw(1234) + chrw(2345)+ chrw(3456) .... and so on.







On 2/23/2010 9:30 AM, 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.

TIA,
Christian