Search code examples
vbaexcelunicodeshift-jis

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?

Solution

  • 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