1

I need to be able to use strings of multiple languages (english, chinese and japanese) in VBA. Changing the region/locale setting of the computer only works if there is one language. Could someone help?

Example code

dim x as string
dim y as string
dim z as string
x = "English text"
y = "尊敬的"
z = "こんにちは"
Nikhil Joseph
  • 41
  • 3
  • 8
  • 1
    This might be of interest: http://vbagettext.sourceforge.net/ *"vbagettext is a collection of Visual Basic for Applications modules and classes that implement basic I18N and L10N capabilities for VBA projects. You can import these components into your own VBA project."* – Pᴇʜ Aug 21 '18 at 12:20
  • 4
    Excel is fine with any of those characters; it's just that you won't be able to hard-code values like your examples - or any Unicode characters (ie., above ASCII 255) in the VBA editor. **You could store the values in a worksheet** (hidden if necessary) and then load them into strings from VBA, or else use `CHRW` etc to generate the strings the "long way". – ashleedawg Aug 21 '18 at 12:31

1 Answers1

3

There's a simple alternative to ashleedawg's suggestion:

Use bytearrays instead of strings to declare the strings. That way, the VBA IDE can store the data independent of locale settings.

I use the following function to declare bytearrays in VBA (Note: errors if you pass anything else than a byte):

Public Function ByteArray(ParamArray bytes() As Variant) As Byte()
    Dim output() As Byte
    ReDim output(LBound(bytes) To UBound(bytes))
    Dim l As Long
    For l = LBound(bytes) To UBound(bytes)
        output(l) = bytes(l)
    Next
    ByteArray = output
End Function

If you have this, you can do the following:

dim x as string
dim y as string
dim z as string
x = "English text" 
'Or: x = ByteArray(&H45,&H0,&H6E,&H0,&H67,&H0,&H6C,&H0,&H69,&H0,&H73,&H0,&H68,&H0,&H20,&H0,&H74,&H0,&H65,&H0,&H78,&H0,&H74,&H0)
y = ByteArray(&HA,&H5C,&H6C,&H65,&H84,&H76)
z = ByteArray(&H53,&H30,&H93,&H30,&H6B,&H30,&H61,&H30,&H6F,&H30)

To get these bytearrays, I use the following worksheet function:

Public Function UnicodeToByteArray(str As String) As String
    If Len(str) = 0 Then Exit Function
    Dim bytes() As Byte
    bytes = str
    Dim l As Long
    For l = 0 To UBound(bytes) - 1
        UnicodeToByteArray = UnicodeToByteArray & "&H" & Hex(bytes(l)) & ","
    Next
    UnicodeToByteArray = UnicodeToByteArray & "&H" & Hex(bytes(UBound(bytes)))
End Function

You can use this in a worksheet (e.g. =UnicodeToByteArray(A1) where A1 contains the string), and then copy-paste the result to VBA.

You can directly assign strings to bytearrays and reversed.

Note that unicode support varies throughout VBA. E.g. MsgBox z will result in questionmarks, while Cells(1,1).Value = z will set A1 to the desired string.

Erik A
  • 31,639
  • 12
  • 42
  • 67
  • This works perfectly !! Thank you !! Much obliged !! – Nikhil Joseph Aug 21 '18 at 18:33
  • Perfect! Thanks a lot for your share. I am newbie in VBA. Your function helps me much to translate from JPese into bytearray. I use it to find and cut JPese in a string. Can you help me how to translate back after cutting? – Tommy Hoang Jun 12 '20 at 03:54