在VBA中使用多种语言(英语,中文,日语) [英] Using multiple language (english, chinese, japanese) in VBA
问题描述
我需要能够在VBA中使用多种语言(英语,中文和日语)的字符串.仅当使用一种语言时,才能更改计算机的区域/地区设置.有人可以帮忙吗?
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?
示例代码
dim x as string
dim y as string
dim z as string
x = "English text"
y = "尊敬的"
z = "こんにちは"
推荐答案
ashleedawg的建议有一个简单的替代方法:
There's a simple alternative to ashleedawg's suggestion:
使用字节数组而不是字符串来声明字符串.这样,VBA IDE可以存储与语言环境设置无关的数据.
Use bytearrays instead of strings to declare the strings. That way, the VBA IDE can store the data independent of locale settings.
我使用以下函数在VBA中声明字节数组(注意:如果传递的不是字节,则报错):
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
您可以在工作表中使用它(例如=UnicodeToByteArray(A1)
其中A1包含字符串),然后将结果复制粘贴到VBA.
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.
请注意,Unicode支持在整个VBA中有所不同.例如. MsgBox z
将产生问号,而Cells(1,1).Value = z
会将A1设置为所需的字符串.
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.
这篇关于在VBA中使用多种语言(英语,中文,日语)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!