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
2 Answers
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
6 Comments
Dietrich Baumgarten
Adding the
vbNullChar is not necessary. I use Application.ActiveWindow.hWnd in Word and Excel instead of Application.hWndAccessApp.Dietrich Baumgarten
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.
Erik A
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.
user202729
@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.
Erik A
@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 stackoverflow.com/questions/2620409
|
appname-vbatags; Excel was to be the first.access-vbabut missing thevbatag, the one combination I'd have to clean up for the next step (mergingaccess-vbaintoms-access) to even work.-vbatags 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).