The built-in VBA MsgBox function does not display Unicode characters.
You can call a Windows API function in place of the built-in MsgBox function to overcome this shortcoming. The challenge is that most code bases contain a lot of MsgBox calls. Replacing them all one at a time with a Windows API function call would be tedious and error-prone.
The solution is to create a drop-in replacement for the built-in MsgBox function.
A "drop-in replacement" is code that you can "drop in" to your codebase and have it take effect without having to modify any of your existing code.
This is a powerful concept.
Due to VBA's identifier resolution rules, if we define a public procedure with the same name as a standard library method then our custom procedure will take precedence. So, if we want to use the MessageBoxW API call everywhere we currently call the built-in VBA MsgBox procedure, we simply need to create a custom procedure named MsgBox that has the same function signature as the built-in version.
I demonstrated how to do that in my article, Unicode-Friendly MsgBox.
One Small Difference
While using my Unicode-friendly MsgBox replacement, I noticed that it behaved slightly differently than the built-in version, at least in one specific situation.
Steps to Reproduce
- Open Microsoft Access
- Switch to the VBA IDE and maximize it
- Copy and paste the MsgBox function from my prior article
MsgBox "test"from the Immediate Window
The main Microsoft Access window would get the focus and the message box would appear above it.
The VBA IDE remains the active window and the message box is not tied to any application whatsoever.
Obsessing over this small detail may seem like overkill.
But a good "drop-in replacement" function should match the original function's behavior except for the thing that necessitates the custom procedure in the first place. For the MsgBox replacement, the key difference is support for Unicode characters.
Thus, anything not related to Unicode characters should match the original as closely as possible.
Getting a Handle on Windows
My original replacement version of the MsgBox function was not tied to any existing window.
To change that behavior, I am going to take advantage of MessageBoxW's
A handle to the owner window of the message box to be created. If this parameter is NULL, the message box has no owner window.
In my original code, I passed
0 as the value for
hWnd. Since this parameter represents a memory pointer, the value
0 is considered a NULL pointer.
To more closely match the built-in VBA MsgBox, we need to pass the value of the pointer to the main Access window instead of
0. Luckily, this value is easy to get in Access as it is a property of the Access.Application object: hWndAccessApp.
Use the hWndAccessApp method to determine the handle assigned by Windows to the main Microsoft Access window.
Here is the refactored code:
'The Declare statements must appear at the top of the module #If VBA7 Then Private Declare PtrSafe Function MessageBoxW Lib "user32" _ (ByVal hWnd As LongPtr, _ ByVal lpText As LongPtr, _ ByVal lpCaption As LongPtr, _ ByVal wType As Long) As Long #Else Private Declare Function MessageBoxW Lib "user32" _ (ByVal hWnd As Long, _ ByVal lpText As Long, _ ByVal lpCaption As Long, _ ByVal wType As Long) As Long #End If ' ---------------------------------------------------------------- ' Procedure : MsgBox ' Author : Mike Wolfe ' Date : 2020-12-24 ' Source : https://nolongerset.com/unicode-msgbox-v2/ ' Purpose : Unicode-safe drop-in replacement for the VBA MsgBox function. ' Changelog - 2022-09-15: Make the Office host application the parent window. ' ---------------------------------------------------------------- Function MsgBox(Prompt As Variant, _ Optional Buttons As VbMsgBoxStyle = vbOKOnly, _ Optional Title As Variant) As VbMsgBoxResult 'Set the default MsgBox title to Microsoft Access/Excel/Word/etc. Dim Caption As String If IsMissing(Title) Then Caption = Application.Name Else Caption = Title End If #If VBA7 Then Dim hWnd As LongPtr #Else Dim hWnd As Long #End If 'Include support for multiple Office applications while ' avoiding compile errors by making the Application ' object late-bound Dim App As Object Set App = Application Select Case Application.Name Case "Microsoft Word": hWnd = App.ActiveWindow.hWnd Case "Microsoft Excel": hWnd = App.hWnd Case "Microsoft Access": hWnd = App.hWndAccessApp Case Else: hWnd = 0 'No owner window (Outlook, Powerpoint, etc.) End Select MsgBox = MessageBoxW(hWnd, StrPtr(Prompt), StrPtr(Caption), Buttons) End Function
A Quick Note About the "W" Suffix
Many API functions have two versions distinguished by a single-character suffix as follows:
- "W" suffix: supports Wide (i.e., 2-byte) characters
- "A" suffix: supports ANSI (i.e., 1-byte) characters
As you probably realized, the
hWndAccessApp property is unique to the Microsoft Access object model. The function checks to see the name of the VBA host application and then retrieves the window handle from the appropriate property:
However, I could not simply call all three of these properties directly (i.e., early-bound) in the code because two of them would always generate compile errors. For example, the Excel and Word properties would generate compile errors when running in Access, while the Access and Word properties would generate errors when running in Excel.
To avoid those compile errors, I defined a generic
Object variable and assigned the host's
Application object to it. Every Office host application includes an
Application object, so there was no problem with that part of it.
I then can include calls to the object model from other host applications without generating compile errors. Of course, I would get a runtime error if I tried to access the
hWndAccessApp property when running from Excel. So, that is something to keep in mind if you want to use this trick.