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

Question

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

Doer

The scheduling applies to all appname-vba tags; Excel was to be the first. – Mildred

@Martijn Could you provide a source on that? As far as I can see, Shog9 said they can extend the plan to these other tags, and explicitly told me Access would be touched after excel-vba here. Messing with the access-vba tag is especially problematic because of the access tag. – Doer

I'm aware of the access issue and have been tackling that. At any rate, I don't think I can tackle anything for serious when it comes to access-vba, as there are over 5k posts tagged access-vba but missing the vba tag, the one combination I'd have to clean up for the next step (merging access-vba into ms-access) to even work. – Mildred

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 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). – Mildred

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. – Doer

access will go away, and soon. Perhaps today. It's a good start at least. – Mildred

(Related blog post) (also explain why you should use LongPtr instead of String) web.archive.org/web/20180823033309/http://blog.nkadesign.com/… – Realpolitik

Answer 1

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

Doer

Adding the vbNullChar is not necessary. I use Application.ActiveWindow.hWnd in Word and Excel instead of Application.hWndAccessApp. – Ours

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. – Ours

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. – Doer

@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. – Realpolitik

@Realpolitik 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 stackoverflow.com/questions/2620409 – Doer

Looks like #9521661 is the method for Microsoft Word 2010. (I didn't test) – Realpolitik

Answer 2

An alternative could be my ModernBox:

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

Display:

Aleece