In yesterday's article, I wrote about a Unicode-friendly function to retrieve existing file names. I included a screenshot of VBA's built-in MsgBox. The problem is that the built-in MsgBox does not display Unicode characters. Here's yesterday's non-Unicode example:
Luckily, we can fix the above issue by calling the "Wide" character version of the MessageBox directly from the Windows API. Here's what that looks like:
But wait, it gets better! We can create a drop-in replacement for the VBA MsgBox function that uses the MessageBoxW API call. Most likely, you can add this function to your application and all of your code will work the same as before, but with Unicode support now!
I know what you're thinking. "Whoa, whoa, whoa, you're making me nervous with all this 'Most likely,' talk. What am I going to break by using this code?"
The answer is that the drop-in replacement function does not support the final two optional parameters of the built-in MsgBox, namely helpfile and context. If you depend on those features of the message box, you've got some more work to do. On the bright side, any calling code that includes either of these arguments will raise a compile-time error, so they will be easy to find.
What's a "drop-in replacement" anyway?
A drop-in replacement is a custom function or procedure that you can drop in to your existing codebase and have it replace the functionality of a built-in function or procedure. The key to crafting a drop-in replacement is to match the signature of the function or procedure as closely as possible.
A function or routine's signature is basically the line that appears in the Object Browser (press [F2] in the VBA IDE) for that procedure. It includes the procedure's scope (Public, Private, etc.); the procedure's type (Sub, Function, Property Get/Let/Set, etc.); the procedure's name; and the type, order, and optionality of each of its arguments. For functions, the signature also includes its return type.
The names of the arguments are not generally considered to be a part of the routine's signature. However, when crafting a drop-in routine replacement in VBA, it is important to match them to the original routine to support named argument assignment, such as:
Let's compare the function signatures between the two MsgBox functions.
Built-in MsgBox function:
Drop-in MsgBox function (Unicode-safe):
Signature formatting in the Object Browser
Routine signatures in the object browser generally reflect the way those same routines appear when declared in code. There are some differences, though. These differences allow for a more concise representation. This maximizes the signal to noise ratio of the information displayed, but only if you understand the meaning of what you don't see.
Here are some important notes about reading routine signatures in the VBA object browser:
- Optional parameters appear inside square brackets
- Since Variant is the default type, it is not shown in the signature even if it's explicitly declared in code
- Since ByRef is the default method for passing arguments, it is not shown in the signature even if it's explicitly declared in code
The Buttons argument
The Buttons argument is worth some extra discussion. It's implemented as a bit flag. Bit flags deserve their own article, but for today's purposes you just need to know that a bit flag allows you to pass several pieces of information in a single numeric argument.
This means that we need to be careful before declaring our new function a drop-in replacement for the built-in MsgBox. More specifically, we need to make sure that our version supports all the possible attributes that could be passed to the built-in version. To confirm this, I compared the available bit flag arguments between the built-in VBA MsgBox and the MessageBoxW API call.
The bit flag arguments for the VBA MsgBox is a subset of the arguments available for MessageBoxW. In other words, all the VBA MsgBox bit flag arguments are supported, plus a few additional arguments, such as TaskModal and ServiceNotification. We're not worried about the extra ones; all that matters is every VBA MsgBox bit flag argument is supported in MessageBoxW.
The trickiest part of this whole endeavor is getting the API call right. Luckily, Renaud Bompuis handled this part already in his fantastic article, VBA: Unicode Strings and the Windows API. Read that for all the requisite background, especially the part about using StrPtr to avoid some problematic Unicode-ANSI-Unicode conversion issues.
Renaud also links to his enhanced message box at the end of his article. I've never used it myself, but I expect it's exceptional. In any case, his enhanced message box is not a drop-in replacement for the VBA MsgBox function. So, switching to that solution will require changes to the rest of your codebase.
Using the Original MsgBox
Though you should only need it if you want to use one of the two unsupported final arguments, I suppose I should tell you how to call the original MsgBox if you wish. Simply calling
MsgBox() will call our drop-in replacement due to the way VBA resolves scope. If you want to call the original, you can do so using its fully qualified name,
'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 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 MsgBox = MessageBoxW(0, StrPtr(Prompt), StrPtr(Caption), Buttons) End Function