Working with different languages (i.e. different alphabets) in VBA

[Kuba_]

I've got texts in different languages inputed in Excel cells. The case is some of the languages don't use latin-alphabet (Chinese, Korean, Thai etc) or contain diacritics (ex. Hungarian, Polish) and when I'm trying to assign such a text to the string variable VBA somehow loses actual text and replaces some letters by question marks. Therefore when I'm trying to find back this string in excel range the procedure fails as the texts differs now.

I suppose that it happens like that because VBA can only work with latin alphabet, is that right?

If so, is there any trick omit this requirement?

 

[pgc01]

Hi

If you want to use different languages better to use Unicode characters in your strings.

Unfortunately, the VBEditor will only accept a few characters (depending on your windows installation), which makes it not possible to insert the strings in the code directly.

Some options:

- compose the strings using the character codes
- write the strings in a worksheet (the cells text accepts Unicode) and then refer to them in the code. You can then, for ex., hide deep this worksheet that you'll use as your text values list.
- store the string in Names and use them in the code

Notice that since you'll use Unicode characters this will work anywhere.

If you need to use many text values option 2 seems the easiest.

 

[pgc01]

I suppose that it happens like that because VBA can only work with latin alphabet, is that right?

P. S. Not sure if it was clear in my post, but to be sure it's clear:

The problem is not vba, the String variables use Unicode and has no problem with the different characters. The problem is the VBEditor, that does not allow you to enter the characters directly.

 

[Kuba_]

- compose the strings using the character codes.

Does it require encoding all letters(signs) separetely or is there any method to encode whole text at once?

- write the strings in a worksheet (the cells text accepts Unicode) and then refer to them in the code. You can then, for ex., hide deep this worksheet that you'll use as your text values list.

This one seems to be brilliantly simple. However as my macro works with few workbooks I would rather avoid to refer to cells as it would make the code pretty clumsy.

- store the string in Names and use them in the code

Could you elaborate on that a little? Do you mean defining name in Excel's Name Manager and refering to them or there is a VBA method or functionality which I haven't heard of so far? (I'm rather newbie where it comes to vba)

 

[wideboydixon]

Here's a couple of functions for converting to/from utf-8:

Private Declare Function MultiByteToWideChar Lib "kernel32" ( _
        ByVal CodePage As Long, _
        ByVal dwFlags As Long, _
        ByVal lpMultiByteStr As Long, _
        ByVal cchMultiByte As Long, _
        ByVal lpWideCharStr As Long, _
        ByVal cchWideChar As Long) As Long
Private Declare Function WideCharToMultiByte Lib "kernel32" ( _
        ByVal CodePage As Long, _
        ByVal dwFlags As Long, _
        ByVal lpWideCharStr As Long, _
        ByVal cchWideChar As Long, _
        ByVal lpMultiByteStr As Long, _
        ByVal cchMultiByte As Long, _
        ByVal lpDefaultChar As Long, _
        ByVal lpUsedDefaultChar As Long) As Long
Public Function UnicodeToUtf8(unicode As String) As String

Dim byteCount As Long
Dim utf8Buffer() As Byte

byteCount = WideCharToMultiByte(65001, 0&, ByVal StrPtr(unicode), -1, vbNull, 0&, 0&, 0&)
ReDim utf8Buffer(byteCount - 2)
byteCount = WideCharToMultiByte(65001, 0&, ByVal StrPtr(unicode), -1, ByVal VarPtr(utf8Buffer(0)), byteCount - 1, 0&, 0&)

UnicodeToUtf8 = StrConv(utf8Buffer, vbUnicode)

End Function
Public Function Utf8ToUnicode(utf8 As String) As String

Dim byteCount As Long
Dim utf8Buffer() As Byte

utf8Buffer = StrConv(utf8, vbFromUnicode)
byteCount = UBound(utf8Buffer) + 1
Utf8ToUnicode = String$(byteCount, ChrW(0))
byteCount = MultiByteToWideChar(65001, 0&, ByVal VarPtr(utf8Buffer(0)), byteCount, ByVal StrPtr(Utf8ToUnicode), byteCount)
Utf8ToUnicode = Left$(Utf8ToUnicode, byteCount)

End Function

I used them in a spreadsheet like this:

Book1
	A	B	C
1		£
2		¤¤
Sheet1

Cell Formulas
Range	Formula
B1	=UnicodeToUtf8(A1)
C1	=Utf8ToUnicode(B1)

So you could type your values in a sheet, use UnicodeToUtf8 to convert them to utf-8 encoding and then, in your code, dynamically convert them to their unicode equivalent like this:

Range("E1").Value = Utf8ToUnicode("áƒ*უსთáƒ�ველი")

Hope that helps in some way.

WBD

 

[pgc01]

Does it require encoding all letters(signs) separetely or is there any method to encode whole text at once?

Hi

If you want to include the strings in your code, you can encode them.

wideboydixon already posted function to encode to utf-8.

If you want to encode words or small phrases, a simpler alternative is just to write the hexadecimal codes of the characters.

This means just a loop.

Ex.

This is an example of a function that returns a result string with the hexadecimal codes of the characters of the input Unicode string (4 digit hex code)

Function CharHex(s As String) As String
Dim j As Long

For j = 1 To Len(s)
    CharHex = CharHex & Right("000" & Hex(AscW(Mid(s, j, 1))), 4) ' adds a character as its 4 digit hex code
Next j
End Function

... and the one to convert back to a Unicode string

Function HexChar(s As String) As String
Dim j As Long

For j = 1 To Len(s) Step 4
    HexChar = HexChar & ChrW("&H" & Mid(s, j, 4)) ' adds a character from its 4 digit hex code
Next j
End Function

As you can see, these are very simple functions, just a loop through the characters.

I did this simple test.
Wrote in A1: house 집 บ้าน дом

This is the word "house" in English, Korean, Thai and Russian, separated by spaces

I then tested the functions. Converted to hex, then back to Unicode string and compared. Tested OK.

Sub Test()
Dim s As String

' hexadecimal representation of the characters of the string
Range("A2").Value = CharHex(Range("A1").Value)

' converts back to string
Range("A3").Value = HexChar(Range("A2").Value)

' checks if the result is equal to the original string
MsgBox "A1 = A3 : " & (Range("A1").Value = Range("A3").Value)

End Sub

 

[pgc01]

... However as my macro works with few workbooks I would rather avoid to refer to cells as it would make the code pretty clumsy.

If you name the cells it won't look that clumsy.

For ex., lets say you defined a named cell "house" that holds the word house in Thai
Now you want to test if the cell J5 holds that word

If Range("J5") = [house] Then
    MsgBox "OK"
Else
    MsgBox "KO"
End If

 

[pgc01]

Could you elaborate on that a little? Do you mean defining name in Excel's Name Manager and refering to them or there is a VBA method or functionality which I haven't heard of so far?

Yes. in this case I meant not as a named cell but as a named literal, like in the name

Refers to: ="집"

Again you could use the code:

If Range("J5") = [house] Then
    MsgBox "OK"
Else
    MsgBox "KO"
End If

to test if J5 has the word house in Korean.

 

[Kuba_]

@wideboydixon @pgc01 Many thanks guys, this is kind of magic I was hoping for!

However, my objective isn't accomplish yet. I described it in new thread since I think it creates new difficulties and solution to them should be easier to find in future. Please, take a look here if you've got some spare time: http://www.mrexcel.com/forum/excel-questions/957487-finding-file-non-latin-alphabet-name-folder.html#post4598679

Cheers!