Use Hebrew characters in VBE

Does any one know, is there a way i can make that a Hebrew letter should display in a MsgBox?

Not in VBA.MsgBox, but windows provides an Unicode version which is able to show Unicode chars. See the code below.

And no, it is not possible that you type Hebrew chars into the VBA editor. A workaround is to store the Hebrew text in cells in a hidden sheet.

Andreas.

'Version 1.01

'Andreas Killer

'24.01.2014

Option Explicit

Option Private Module

#If Win64 Then

Private Declare PtrSafe Function MessageBoxL Lib "user32" Alias "MessageBoxW" ( _

  ByVal hWnd As LongPtr, ByVal lpText As LongPtr, ByVal lpCaption As LongPtr, _

  ByVal wType As Long) As Long

Private Declare PtrSafe Function GetForegroundWindow Lib "user32" () As LongPtr

#Else

Private Declare Function MessageBoxL Lib "user32" Alias "MessageBoxW" ( _

  ByVal hWnd As Long, ByVal lpText As Long, ByVal lpCaption As Long, _

  ByVal wType As Long) As Long

Private Declare Function GetForegroundWindow Lib "user32" () As Long

#End If

Private Sub Example_MsgBoxW()

  Dim Msg As String

  Dim Answer As VbMsgBoxResult

  'This is the usual way to build unicode chars in VBA

  Msg = "Is O" & ChrW$(&H2082) & " " & ChrW(&H2248) & " O²?"

  'But a little function makes it more easier

  Msg = ReplaceUnicode("Is OU+2082 U+2248 O²?")

  'Note: It depends on the OS if an unicode char is not shown as expected!

  Select Case MsgBoxW(Msg, vbYesNoCancel Or vbQuestion Or vbDefaultButton2)

    Case vbYes:    Debug.Print "vbYes"

    Case vbNo:     Debug.Print "vbNo"

    Case vbCancel: Debug.Print "vbCancel"

  End Select

End Sub

Function MsgBoxW(ByVal Prompt As String, _

    Optional ByVal Buttons As VbMsgBoxStyle, Optional ByVal Title, _

    Optional ByVal HelpFile, Optional ByVal Context) As VbMsgBoxResult

  'Same as VBA.MsgBox, but support unicode

  If IsMissing(Title) Then Title = Application.Name

  MsgBoxW = MessageBoxL(GetForegroundWindow, StrPtr(Prompt), StrPtr(Title), Buttons)

End Function

Function ReplaceUnicode(ByVal S As String) As String

  'Replace unicode terms like "U+1234" in S to unicode chars

  Dim i As Long, j As Long, k As Long

  Dim A As Integer

  Dim Digit As String

  'Find the start of an unicode term

  i = InStr(1, S, "U+", vbTextCompare)

  On Error GoTo Errorhandler

  Do While i > 0

    'Get the next up to 4 hex digits

    For j = i + 2 To i + 5

      A = AscW(Mid$(S, j, 1))

      If A < 48 Then

        Exit For '<0

      ElseIf (A > 57) And (A < 65) Then

        Exit For '>9 <A

      ElseIf (A > 70) And (A < 97) Then

        Exit For '>F <a

      ElseIf A > 102 Then

        Exit For '>f

      End If

    Next

    'Store the unicode char

    Mid$(S, i, 1) = ChrW$(CLng("&H" & Mid$(S, i + 2, j - i - 2)))

    'Overwrite the rest of the term with zeros

    For j = i + 1 To j - 1

      Mid$(S, j, 1) = vbNullChar

    Next

Skip:

    'Find the next term

    i = i + 1

    i = InStr(i, S, "U+", vbTextCompare)

  Loop

  'Strip zeros

  ReplaceUnicode = Replace(S, vbNullChar, "")

  Exit Function

Errorhandler:

  'Skip this part

  Resume Skip

End Function

You can use a UserForm to achieve a similar effect.

1. Add a UserForm to your VBE Project (named UserForm1 by default).
2. Add a Label (Label1) and TextBox (TextBox1) to UserForm1.
3. In Cell A1 of the ActiveSheet, add some Hebrew characters.
4. Add the following code to a standard code module and run it.  You should see the Hebrew characters.

Option Explicit

Sub TestUnicodeInLabel()

    Dim oForm As UserForm1

    Dim sButtonText As String

    Set oForm = New UserForm1

    Dim sVariableText As String

    sVariableText = ChrW(1488) & " " & ChrW(1520) & " " & ChrW(1509) & " (should see Hebrew characters)"

    With oForm

        sButtonText = ActiveSheet.Range("A1").Value

        .TextBox1.Text = sButtonText

        .Label1.Caption = sVariableText

        .Show

    End With

End Sub