A VBA MsgBox Replacement

In VBA Tips, Tricks and Best Practices, Part Three of Four I mentioned encapsulating the MsgBox function to handle user input. Here is the function I wrote to handle that.

It uses an enumerated section to duplicate the button arguments used in the original MsgBox function. This should be placed at the top of a standard module. (Hint: You can get this list by typing 'MsgBox' into the VB Editor or the Immediate Window and pressing F1.)

Enum MessageBoxButtons
  vbOKOnly = 0
  vbOKCancel = 1
  vbAbortRetryIgnore = 2
  vbYesNoCancel = 3
  vbYesNo = 4
  vbRetryCancel = 5
  vbCritical = 16
  vbQuestion = 32
  vbExclamation = 48
  vbInformation = 64
  vbDefaultButton1 = 0
  vbDefaultButton2 = 256
  vbDefaultButton3 = 512
  vbDefaultButton4 = 768
  vbApplicationModal = 0
  vbSystemModal = 4096
  vbMsgBoxHelpButton = 16384
  vbMsgBoxSetForeground = 65536
  vbMsgBoxRight = 524288
  vbMsgBoxRtlReading = 1048576
End Enum

Here is the new function, called MessageBox (a la VB.NET). There are two optional arguments:

• The buttons argument duplicates the function of the same argument in the original MsgBox function.
• title is declared As Variant so we can use IsMissing to check if it was passed.

Note that I left out the last two optional arguments of MsgBox; I never use them, so to avoid complication I didn't include them.

Function MessageBox(prompt As String, Optional buttons As MessageBoxButtons = 0, _
 Optional title As Variant) As VbMsgBoxResult  ' As Integer
' rewrite of VBA's MsgBox Function
' will return one of the following seven values:
' vbOK      - 1
' vbCancel  - 2
' vbAbort   - 3
' vbRetry   - 4
' vbIgnore  - 5
' vbYes     - 6
' vbNo      - 7

Dim titleString As String

  If IsMissing(title) Then
    titleString = Application.Name
  Else
    titleString = CStr(title)
  End If

  MessageBox = MsgBox(prompt, buttons, title)
End Function

Sample usage:

Sub testme()

Dim result As Integer

  result = MessageBox("hello", vbInformation + vbYesNo, "My title")

  Select Case result
    Case 1, 4, 5, 6
      MessageBox ("you want to continue!")
    Case 2, 3, 7
      MessageBox ("you want to stop")
  End Select

End Sub

You'll notice that the new function works just as well as the original, maybe better (since it has less options to worry about), and you can use it with or without returning a value.

In the example above, we capture the result of MessageBox in the variable result, then use a Select Case statement to determine which option was selected. The MessageBox function is then used without capturing the return value, to simply display a message box to the end user.

Related Articles:

• April 6, 2009 -- Update to Data Filter Tool
• September 6, 2008 -- Reading worksheet values into arrays
• April 21, 2009 -- Converting month name to number