VBA Excel: message box and non english words

Question

Has anyone a workaround to show Arabic in a message box. I even changed the font to Arial (Arabic) in VBA setting and I cant write Arabic in the code but when it runs its shows garbage words in the message box. Below is a sample code even though it's showing as CaCaCa below but in my VBA it's a proper Arabic word.

Private Sub cmdDelete_Click()
    
Dim iRow As Long
    
If Selected_List = 0 Then
  Font.Name = "Arial Unicode MS"
  MsgBox "ÇáÇãÇä No row is selected. ", vbOKOnly + vbInformation, "Delete"
  Exit Sub
End If

score 2 · Accepted Answer · answered Dec 05 '20 at 18:07

An approach based on @Tarik 's link to MsgBox with Unicode characters:

VBA7 Declaration of API function MessageBoxW()

Option Explicit              ' Declaration head of code module

Private Declare PtrSafe Function MessageBoxW Lib "User32" ( _
    ByVal hWnd As LongPtr, _
    ByVal lpText As LongPtr, _
    ByVal lpCaption As LongPtr, _
    ByVal uType As Long) _
As Long

Help function MsgBoxW()

'Site: https://stackoverflow.com/questions/55210315/how-do-i-display-a-messagebox-with-unicode-characters-in-vba

Function MsgBoxW( _
        Prompt As String, _
        Optional Buttons As VbMsgBoxStyle = vbOKOnly + vbInformation, _
        Optional Title As String = " Delete") _
    As VbMsgBoxResult
    Title = WorksheetFunction.Unichar(&H1F4BC) & Title
    MsgBoxW = MessageBoxW(Application.hWnd, StrPtr(Prompt), StrPtr(Title), Buttons)
End Function

Note that Access would need a Application.hWndAccessApp argument to get the corresponding window handle.

Example call

Sub ExampleCall()
    Dim s As String
    s = WorksheetFunction.Unichar(&H2776) & " " & getArabic() & vbNewLine & vbNewLine & _
        WorksheetFunction.Unichar(&H2777) & " No Rows selected. "
    
    MsgBoxW s
End Sub

Hardcoding test function getArabic()

As I don't know the Arabic language, the following function only tries to simulate a correct phrase I got via a translation site by joining single unicode values of a hardcoded array to a string like e.g. ; so I beg your pardon for any mistranslation :-)

There are numerous sites where you can get the hexadecimal or decimal code values immediately.

It would be possible as well to insert your original string into an Excel sheet cell and analyze the corresponding character values one by one (e.g. via formula =UNICODE(MID($A2,1,1)) etc.)

Function getArabic()
'Note: uses decimal values here (e.g. decimal 1604 equals hexadecimal &H644)
    Dim arr: arr = Array(1604, 1605, 32, 1610, 1578, 1605, 32, 1578, 1581, 1583, 1610, 1583, 32, 1589, 1601, 46)
    Dim a, s As String
    For Each a In arr
        s = s & WorksheetFunction.Unichar(a)
    Next a
    getArabic = s
End Function

Why would you use `WorksheetFunction.Unichar` instead of `ChrW$`? — GSerg, Dec 06 '20 at 14:38
Sorry for not commenting: I just didn't want to distinguish between char codes lower or higher than the allowed ChrW maximum of &HFFFF; of course there would be no need here. - c.f. the [linked site](https://stackoverflow.com/questions/56008191/get-unicode-characters-with-charcode-values-greater-hex-ffff) @GSerg — T.M., Dec 06 '20 at 17:07
Values higher than &hffff cannot be represented with a single UTF-16 char, so naturally `ChrW` [cannot](https://stackoverflow.com/questions/57158679/alternative-of-chrw-function/57161304#comment100831969_57158679) return them, because they map to *sequences of UTF-16 chars*. If you define your numeric array in terms of UTF-16 chars, as you should, you should never face this problem. — GSerg, Dec 06 '20 at 17:24
Of course quite true, appreciate the hint how to provide for alternatively and certainly worth posting some further reflections. - Afaik I wouldn't face a problem by applying the `WorksheetFunction.Unichar` upon the numeric array as I did above @GSerg — T.M., Dec 06 '20 at 17:46

score 1 · Answer 2 · answered Dec 05 '20 at 10:03

1

The computer you are using should have the default code page to Windows 1256. That way, it interprets any extended ASCII character (above 127) as Arabic. The alternative (preferred option) is to ensure you use UTF-8.

See How do I display a messagebox with unicode characters in VBA?

answered Dec 05 '20 at 10:03

Tarik

10,810
2
26
40

sorry this didnt work you have any other solution? – kamran mirza Dec 05 '20 at 11:36
Probably you didn't pass the `Application.hWnd` argument using Excel as shown in the link above. Another way is by creating a Userform :-) @kamranmirza – T.M. Dec 05 '20 at 12:00
FYI might be interested in [Get Unicode characters with CharCode values greater hex FFFF](https://stackoverflow.com/questions/56008191/get-unicode-characters-with-charcode-values-greater-hex-ffff) – T.M. Dec 05 '20 at 12:01
I fail mentioned that I have userform from which I am validating in the form and the user is Arabic and wanted to show Arabic message with the English as well. I can see the Arabic in the msgbox code but when its running it's just garbage. – kamran mirza Dec 05 '20 at 12:25

VBA Excel: message box and non english words

2 Answers2

Linked