Tip 4: Automatically log critical MsgBox errors.

One way to log critical MsgBox errors is by not using the standard message box provided by VBA’s Interaction.MsgBox routine. When you refer to an object or a property in code, Visual Basic searches each object library you reference in order to resolve it. Object library references are set up in Visual Basic’s References dialog box. (Open the References dialog box by selecting References from the Project menu.) The up arrow and down arrow buttons in the dialog box move references up and down in a list so that they can be arranged in priority order. If two items in the list use the same name for an object, Visual Basic uses the definition provided by the item listed higher in the Available References list box. The three topmost references (Visual Basic For Applications, Visual Basic Runtime Objects And Procedures, and Visual Basic Objects And Procedures) cannot be demoted (or shuffled about). The caveat to all this prioritizing works in our favor—internal modules are always searched first.

Visual Basic 5 allows you to subclass its internal routines such as MsgBox and replace them with your own (through aggregation). Recall that in the code shown earlier (in Listing 1-1) some of the calls to MsgBox were prefixed with VBA. This explicitly scopes the call to VBA’s MsgBox method via the Visual Basic For Applications type library reference. However, calls to plain old MsgBox go straight to our own internal message box.

A typical call to our new message box might look like this:

MsgBox "Error text in here", _
       vbYesNo + vbHelpButton + vbCritical, sMsgBoxTitle

vbHelpButton is not a standard Visual Basic constant but rather an internal constant. It’s used to indicate to MsgBox that it should add a Help button. Also, by adding vbCritical, we’re saying that this message (error) is extremely serious. MsgBox will now log this error to a log file.

To replace MsgBox, all you have to do is write a function (an application method really) named MsgBox and give it the following signature. (The real MsgBox method has more arguments that you might also want to add to your replacement: use the object browser to explore the real method further.)

Public Function MsgBox _
( _
    ByVal isText As String _
    , Optional ByVal inButtons As Integer _
    , Optional ByVal isTitle   As String  _
)

Here’s an example of a trivial implementation:

Public Function MsgBox _
( _
    ByVal isText As String _
    , Optional ByVal inButtons As Integer _
    , Optional ByVal isTitle   As String  _
)

    Dim nResult As Integer

    nResult = VBA.Interaction.MsgBox(isText, inButtons, isTitle)

    If Not IsMissing(inButtons) Then
        If (inButtons And vbCritical) = vbCritical Then
            Call LogError(isText, inButtons, isTitle, nResult)
        End If
    End If

    MsgBox = nResult

End Function

Here we’re logging (implied by the call to LogError) the main message text of a message box that contains the vbCritical button style. Notice that we’re using the VBA implementation of MsgBox to produce the real message box on screen. (You could use just VBA.MsgBox here, but we prefer VBA.Interaction.MsgBox for clarity.) Within your code, you use MsgBox just as you always have. Notice also that in our call to LogError we’re logging away the user’s response (nResult) too—“I’m sure I said ‘Cancel’!”

Another good idea with any message box is always to display the application’s version number in its title; that is, modify the code above to look like this:

sTitle = App.EXEName & "(" & App.Major & "." & _
                             App.Minor & "." & _
                             App.Revision & ")-"

nResult = VBA.Interaction.MsgBox(isText, inButtons, _
                                 sTitle & isTitle)

Figure 1-2 shows the message box that results from this code.

Of course, you don’t have to use VBA’s MsgBox method to produce the message box. You could create your own message box, using, say, a form. We create our own custom message boxes because we often want more control over the appearance and functionality of the box. For example, we often use extra buttons (such as a Help button, which is what the vbHelpButton constant was all about) in our message boxes.

Figure 1-2 Using your version number in message boxes