15

I have a string containing unicode characters in VBA.

I want to display that string in a message box containing it.

However, instead of the string, the message box only contains a questionmark.

MCVE:

Dim s As String
s = ChrW(5123)
MsgBox s
Erik A
  • 31,639
  • 12
  • 42
  • 67
  • The scheduling applies to *all* `appname-vba` tags; Excel was to be the first. – Martijn Pieters Jul 05 '20 at 12:25
  • @Martijn Could you provide a source on that? As far as I can see, Shog9 said they [_can_](https://meta.stackoverflow.com/questions/370095/merging-the-excel-vba-into-vba-speak-now-or-forever-hold-your-peace#comment603479_370095) extend the plan to these other tags, and explicitly told me Access would be touched _after_ excel-vba [here](https://meta.stackoverflow.com/questions/370095/merging-the-excel-vba-into-vba-speak-now-or-forever-hold-your-peace#comment604299_370095). Messing with the [tag:access-vba] tag is especially problematic because of the [tag:access] tag. – Erik A Jul 05 '20 at 12:54
  • I'm aware of the [tag:access] issue and have been tackling that. At any rate, I don't think I can tackle anything for serious when it comes to [tag:access-vba], as there are [over 5k posts tagged `access-vba` but missing the `vba` tag](https://stackoverflow.com/search?q=%5Baccess-vba%5D+-%5Bvba%5D+is%3Aq), the one combination I'd have to clean up for the next step (merging `access-vba` into `ms-access`) to even work. – Martijn Pieters Jul 05 '20 at 13:15
  • 1
    the whole family of `-vba` tags has been a huge issue for too long however, which is why I'm driving this manually now. While [tag:access-vba] gets a stay of execution, that's only until the CMs regain the ability to do tag burninations properly again (Shog and Jon, both gone, were the only ones who knew how and had the access for it). – Martijn Pieters Jul 05 '20 at 13:18
  • Ah, it's a relief to know access-vba won't suddenly follow outlook-vba. Of course I realize that after excel-vba is gone and it's clear that it didn't cause problems, access-vba will likely follow, hopefully not before the access tag is gone but that's probably idle hope. I think any half-action is a lot of mostly futile work, but if you wish to do it I won't oppose it. – Erik A Jul 05 '20 at 13:56
  • 1
    [tag:access] will go away, and soon. Perhaps today. It's a good start at least. – Martijn Pieters Jul 05 '20 at 13:57
  • (Related blog post) (also explain why you should use `LongPtr` instead of `String`) https://web.archive.org/web/20180823033309/http://blog.nkadesign.com/2013/vba-unicode-strings-and-the-windows-api/ – user202729 Oct 05 '21 at 12:22

2 Answers2

20

MsgBox is not compatible with non-ANSI unicode characters.

We can display message boxes with the WinAPI MessageBoxW function, however, and that is .

Let's declare that function, and then create a wrapper for it that's nearly identical to the VBA MsgBox function:

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

This function is only compatible with Microsoft Access. However, for Excel you can swap Application.hWndAccessApp with Application.hWnd to make it work. For other VBA compatible applications, you'll have to find the appropriate way to get the hWnd.

You can use it like MsgBox, as long as you don't use the context-dependent help functionality:

Dim s As String
s = ChrW(5123)
MsgBoxW s
Erik A
  • 31,639
  • 12
  • 42
  • 67
  • 1
    Adding the `vbNullChar` is not necessary. I use `Application.ActiveWindow.hWnd` in Word and Excel instead of `Application.hWndAccessApp`. – Dietrich Baumgarten Jun 28 '20 at 11:04
  • 3
    VBA string is stored in memory as a COM `BSTR`. It consists of a 4 byte unsigned integer that stores the length of the string in bytes followed by the string data itself as wide characters (2 bytes per character) and terminated with 2 null bytes. So, `BSTR` strings are length prefixed and null-terminated. The internal pointer retrieved by the `StrPtr()` function points to the memory location of the string data, not the length prefix. This means that a VBA String `vbastr` can be passed via `StrPtr(vbastr)` directly to API functions that require a pointer to a null-terminated C-String. – Dietrich Baumgarten Jun 28 '20 at 12:45
  • Ah, you're right, thanks. I didn't know `BSTR` contained a null terminator in addition to specifying the length up front, always thought that strings with specified length didn't contain it. I've edited the answer. – Erik A Jun 28 '20 at 13:18
  • @DietrichBaumgarten Actually ActiveWindow.hWnd is sufficient (Microsoft Word). Although it can be searched in Object Browser) ________________________________________________________________________________ in older versions it may not be accessible, in that case NULL (0) can be used too -- see [MessageBoxW documentation](https://learn.microsoft.com/en-us/windows/win32/api/winuser/nf-winuser-messagebox). – user202729 Sep 29 '21 at 05:31
  • 1
    @user202729 Note that I highly recommend passing a hWnd. If you pass NULL, the window can open behind the current one, while still being synchronous so locking up the application, which can be very confusing for users that don't notice the messagebox. If you're using an application that doesn't provide a hWnd, you can retrieve it using WinAPI, see https://stackoverflow.com/questions/2620409 – Erik A Sep 29 '21 at 06:45
  • Looks like https://stackoverflow.com/questions/9521161/how-do-i-get-the-hwnd-process-id-for-a-word-application-and-set-as-foreground is the method for Microsoft Word 2010. (I didn't test) – user202729 Sep 29 '21 at 06:52
1

An alternative could be my ModernBox:

MsgMox ChrW(5125) & ChrW(5123) & ChrW(5121) & ChrW(5130), vbInformation, "Unicode"

Display:

enter image description here

Gustav
  • 53,498
  • 7
  • 29
  • 55