I recently read an Excel VBA book that recommended using numbers instead of VBA constants for message boxes. Please don’t do that. Here’s why.

Message boxes are a great way to give feedback and to accept simple interactions from users.

You can control the message, the title, the buttons, and the icon that displays.

To define the buttons and the icons VBA has constants that describe the buttons and icons.

These constants all have a numeric value.

Some button examples.

vbOKOnly = 0

vbOKCancel = 1

vbAbortRetryignore = 2

vbYesNoCancel = 3

The icons have their own separate numbering.

vbCritical = 16

vbQuestion = 32

vbExclamation = 48

vbInformation = 64

Here is a comparison of two ways to build a message box showing a Yes and No button with the Question icon.

The two MsgBox lines generate exactly the same message box.

The first code line is easy to read and understand.

The second code line uses 36 to define the buttons and icon. The code may be shorter, but it has added a layer of mystery where none is required. The reader needs to know the numbers to decipher the buttons and icon.   

VBA is supposed to be an easy to read and understand language. In general, it uses plain English. When you use numbers instead of descriptive constants you start to make it hard to read and understand.

Responses

The same issue applies to handling responses from a message box.

The responses have codes too – here are a few.

OK = 1

Cancel = 2

Abort = 3

Retry = 4

Again, compare the two code snippets below in terms of readability and understandability.

These two code lines perform the same task, but one is easy to read and understand and the other is not.

Please use VBA constants when you can and avoid using their respective numbers. It will make your code easy to read for you and others who may need to understand it in the future.