1

Starting off in VBA and trying to set up a bilingual worksheet. One part is to include both Arabic and English text in MsgBoxes. I have tried changing my regional settings and can now paste Arabic chars in the VBA Editor however they are displayed as different chars in the message text. (see image)

enter image description here

My current approach is to convert the text to Unicode and join first the letters, then the English text in a separate function (apologies in advance for the poor code):

Sub exampleMsg()

  Dim Atxt As String
  Dim Etxt As String

  Atxt = "\u0627\u062e\u062a\u0628\u0627\u0631\u0627\u062a"
  Etxt = "testing"
  MsgBox (AddArabic(Atxt, Etxt))

End Sub

with the AddArabic() function:

Function AddArabic(ARtxt As String, ENtxt As String)

  arr = Split(ARtxt, "\u")
  Dim uStr, s As String
  Dim uNum As Integer
  
  For Each uStr In arr
   uNum = CInt(uStr)
   s = s & ChrW(uNum)
  Next uStr
  s = s & vbNewLine & vbNewLine & ENtxt
  
  AddArabic = s

End Function

This seems to work for most chars however as a non-Arabic speaker bound to Google translate I keep running into issues with letters that seem to have a non-numeric UTF index (e.g. u062a for ta'a). Understandably these can't be converted to Int.

I've scoured the web for a similar problem but can't seem to find any cases, leading to the assumption that I've misunderstood the ChrW fuction or my options in general. Any help would be much appreciated!

Tried:

  • Change the devices regional setting and insert Arabic text directly
  • Create array of Unicode references and assemble string manually
  • Pass Unicode references as String instead of Int (was worth a try)
  • Read string from a cell and use .Value as MsgBox String

Expected: Message Box to handle foreign chars or Unicode reference to be numeric only

JosefZ
  • 28,460
  • 5
  • 44
  • 83
DerEnglaender
  • 11
  • 1
  • Does this answer your question? [How do I display a messagebox with unicode characters in VBA?](https://stackoverflow.com/questions/55210315/how-do-i-display-a-messagebox-with-unicode-characters-in-vba) – braX Aug 07 '23 at 13:41
  • Hi braX, thanks for the hint. It looks like MsgBoxW could indeed be the most straightforward way however Excel isnt accepting Application.hWndAccessApp.MsgBoxW("test") From what i could tell the switch to Access is neccessary though... – DerEnglaender Aug 07 '23 at 14:01
  • Got the Access window working but unfortunately MsgBoxW displays same mess of chars as MsgBox :( – DerEnglaender Aug 07 '23 at 14:08
  • @DerEnglaender Possibly helpful: [VBA-Excel: Message box and non English words](https://stackoverflow.com/questions/65155683/vba-excel-message-box-and-non-english-words/65160306#65160306) and related [Right to Left Userforms in Excel VBA](https://stackoverflow.com/questions/52781069/right-to-left-userforms-in-excel-vba/52806751#52806751) – T.M. Aug 07 '23 at 17:35

1 Answers1

0

Ok, found a (albeit complex) solution;

  1. changed \u**** references to decimal only using this tool: https://r12a.github.io/app-conversion/ (This gets me the full string of arabic chars)

  2. as braX's suggested answer MsgBox will simply NOT display non ANSI chars. A function using the MsgBoxW option in MS Access is required. Details in his suggested post: How do I display a messagebox with unicode characters in VBA? or the function code directly (Thank you @Eric A !):

    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

    Public Function MsgBoxW(Prompt As String, Optional Buttons As VbMsgBoxStyle = vbOKOnly, Optional Title As String = "Microsoft Access") As VbMsgBoxResult
    MsgBoxW = MessageBoxW(Application.hWndAccessApp, StrPtr(Prompt), StrPtr(Title), Buttons)
End Function

NOTE: using the arabic text in VBA directly in MsgBoxW did NOT work. Additional example found here: VBA Excel: message box and non english words

DerEnglaender
  • 11
  • 1