Unicode MsgBox Revisited

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.

Drop-in Replacement

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

1. Open Microsoft Access
2. Switch to the VBA IDE and maximize it
3. Copy and paste the MsgBox function from my prior article
4. Call MsgBox "test" from the Immediate Window

Expected Reults

The main Microsoft Access window would get the focus and the message box would appear above it.

Actual Results

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 hWnd parameter:

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.

Refactored Code

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

Excel/Word/Etc.

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:

• Access: Application.hWndAccessApp
• Excel: Application.hWnd
• Word: Application.ActiveWindow.hWnd

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.

Image by Antonios Ntoumas from Pixabay