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