CODEWITHSUNDEEP
vbaexcelunicodeshift-jis
Aditya
Aditya

Reputation: 115

SHIFT-JIS to Unicode?

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?

Upvotes: 2

Views: 1902

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60334

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

Entering original string

String with Unicode character codes

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

Upvotes: 0

omegastripes
omegastripes

Reputation: 12612

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:

input

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

result

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:

output

Upvotes: 2

Related Questions