SHIFT-JIS to Unicode?

Question

While running a small vba application I want to check if a certain name, e.g. エンジン回転数 occurs in my table by simply matching cell value's to a pre defined string.

How can I store this string エンジン回転数 in VBA? My first guess was to store it in its byte array. However the StrConv function just stores this as an array of 63's corresponding to ?'s. Any tips?

score 2 · Accepted Answer · answered Jun 19 '16 at 19:28

2

I suggest to convert copied text with the following VB Script, since Excel's InputBox() seems process such input incorrectly:

s = InputBox("Enter text")
With CreateObject("Scripting.Dictionary")
    For i = 1 To Len(s)
        .Item(.Count) = "ChrW(" & AscW(Mid(s, i, 1)) & ")"
    Next
    q = Join(.Items(), " & ")
End With
MsgBox q & vbCrLf & Eval(q)

Just paste it in notepad, save and replace the file extension ".txt" with ".vbs". Run it by double click. Paste your text in a dialog:

Then resulting dialog shows that string represented with ChrW function arguments:

You can press Ctrl+C to copy result to clipboard.

Then you can check it in VBA code:

    Cells(1, 1).Value = ChrW(12456) & ChrW(12531) & ChrW(12472) & ChrW(12531) & ChrW(22238) & ChrW(-28958) & ChrW(25968)

The output should be:

answered Jun 19 '16 at 19:28

omegastripes

12,351
4
45
96

It is helpful for me. This case is Japanese, mine is Chinese in GB. Your code give me a tip to handle Unicode in VBE. – PaichengWu Jun 20 '16 at 00:36
Eval() seems not a VBA function. How do I evaluate the string in VBA? – PaichengWu Jun 20 '16 at 02:00
@pcw the code I suggested is VBS, just like an utility allowing to represent source text as ASCII line of code. `Eval()` used there just for check that prepared `ChrW()`s are correct. In my answer I pointed how to paste that line into VBA, while execution it is converted to the source text, no evaluation is needed. Could you elaborate why do you need to evaluate the string? – omegastripes Jun 20 '16 at 06:18
as what you done, I want to verify the transformation. – PaichengWu Jun 20 '16 at 06:26
@pcw that check intented to be for a human after a manual input, it's not quite clear what the necessity to make it runtime for? You may just do another loop which converts codes to chars and concatenates them, then check against src. – omegastripes Jun 20 '16 at 10:08
@pcw BTW one of the ways to implement smth. like `Eval()` in VBA is to create `htmlfile` instance, and use it's scripting engine to call VBS function. Another one is to use `ScriptContol`, it's more powerful, but won't work in 64-bit Office. – omegastripes Jun 20 '16 at 10:14
Yes, you are right. I will try to convert the codes to chars. – PaichengWu Jun 20 '16 at 10:15
I saw you create `htmlfile` instance to call javascript funcation. I will try to call VBS funcation. – PaichengWu Jun 20 '16 at 10:19

Ron Rosenfeld · Answer 2 · 2016-06-19T20:08:36.663

You can store the strings in a regular string variable. The problem may be getting them in there.

Probably the easiest way to get them into the string variable or array, would be to list them in a column on a worksheet (probably either in a hidden column or a hidden worksheet) and then copy them from those cells to your string variable.

The VBA Editor does not process Unicode characters when you paste it in, if you are using Western settings, nor does the regular Input box.

To demonstrate that the string variable does, in fact, store Unicode strings, you can try the following:

Application.InputBox will allow you to paste in Unicode strings, and you can store them in a regular string variable. Unfortunately, if you examine that string variable in the VBE, you will see only a string of ?'s even though the correct values are stored.

Here is an example showing that the proper values are stored -- I output the results in a User Form. The original string and the Unicode character codes.

I Inserted a UserForm (UserForm1), added a Textbox, and allowed it to AutoSize.

Option Explicit
Sub foo()
    Dim I As Long
    Dim sMyUnicode As String
    Dim S As String
    Dim TB As TextBox

sMyUnicode = Application.InputBox(prompt:="Input Unicode String", Type:=2) 'Type 2 = text

S = sMyUnicode & vbLf
For I = 1 To Len(sMyUnicode)
    S = S & vbLf & Mid(sMyUnicode, I, 1) & vbTab & Hex(AscW(Mid(sMyUnicode, I, 1)))
Next I

With UserForm1
    .TextBox1.Value = S
    .Show
End With

End Sub

Another possible solution, which I cannot check against your sample on my system, was once posted on an MSDN forum:

1.In Control Panel > Regional and language options> advanced tab choose the native language you want to use in language for non-unicode programs

2.In VBA editor choose tools>options>editor format> font which support your language

SHIFT-JIS to Unicode?

2 Answers2