VBA Non Latin Characters
Non-Latin Text in VBA Code
In spreadsheet cell A1, we have the following Arabic pangram:
صِف خَلقَ خَودِ كَمِثلِ الشَمسِ إِذ بَزَغَت — يَحظى الضَجيعُ بِها نَجلاءَ مِعطارِ
VBA provides the AscW and ChrW functions to work with multi-byte character codes. We can also use Byte arrays to manipulate the string variable directly:
Sub NonLatinStrings()
Dim rng As Range
Set rng = Range("A1")
Do Until rng = ""
Dim MyString As String
MyString = rng.Value
' AscW functions
Dim char As String
char = AscW(Left(MyString, 1))
Debug.Print "First char (ChrW): " & char
Debug.Print "First char (binary): " & BinaryFormat(char, 12)
' ChrW functions
Dim uString As String
uString = ChrW(char)
Debug.Print "String value (text): " & uString ' Fails! Appears as '?'
Debug.Print "String value (AscW): " & AscW(uString)
' Using a Byte string
Dim StringAsByt() As Byte
StringAsByt = MyString
Dim i As Long
For i = 0 To 1 Step 2
Debug.Print "Byte values (in decimal): " & _
StringAsByt(i) & "|" & StringAsByt(i + 1)
Debug.Print "Byte values (binary): " & _
BinaryFormat(StringAsByt(i)) & "|" & BinaryFormat(StringAsByt(i + 1))
Next i
Debug.Print ""
' Printing the entire string to the immediate window fails (all '?'s)
Debug.Print "Whole String" & vbNewLine & rng.Value
Set rng = rng.Offset(1)
Loop
End SubThis produces the following output for the Arabic Letter Sad:
First char (ChrW): 1589
First char (binary): 00011000110101
String value (text): ?
String value (AscW): 1589
Byte values (in decimal): 53|6
Byte values (binary): 00110101|00000110
Whole String
??? ????? ????? ??????? ??????? ??? ??????? — ????? ???????? ???? ??????? ???????
Note that VBA is unable to print non-Latin text to the immediate window even though the string functions work correctly. This is a limitation of the IDE and not the language.