Hebrew text in vba code doesn't decode properly

Question

I've developed a workbook, with some underlying vba code. The workbook is in Hebrew, and the vba code uses Hebrew as well, e.g. comparing strings in Hebrew, or accessing Sheets using their Hebrew names. I've developed this workbook in Excel 2010, and saved it as an xlsm file (macro enabled). It all works very well on various machines and excel versions, except on a certain pc having excel 2007 installed. The problem seems to be that the Hebrew strings I used in the vba code are not properly decoded, and are seen as Gibberish. The Hebrew text within the workbook itself (i.e. the cells or the sheet names) is perfectly fine, only the text I've been using in the VBA code itself doesn't seem to be read properly. What I'd like to do is to automatically set the encoding/decoding properly once the workbook is opened, so that it can be used on any machine, with any excel version.

After reading some more here and here I realize that this happens when the system locale is not Hebrew. Can I dynamically set it only in Excel using vba code (i.e. not changing it in the OS level), so that the Hebrew text I wrote would work? Otherwise I'll need to remove all the Hebrew text from the vba code.

Answer 1

Use the following function from ADODB Stream (You need to reference the latest Microsoft ActiveX Data Objects Library)

Public Function CorrectHebrew(gibberish As String) As String

    Dim inStream As ADODB.stream

    Set inStream = New ADODB.stream
    inStream.Open
    inStream.Charset = "WIndows-1255"
    inStream.WriteText gibberish
    inStream.Position = 0
    inStream.Charset = "UTF-8"
    CorrectHebrew = inStream.ReadText
    inStream.Close
End Function