A common problem in many programs involves one or more variables not taking on the values you expect. In such cases, monitoring the variable(s) while your code runs is a helpful debugging technique. Do this by inserting
An error message like this often means that your VBA code contains a bug.
temporary MsgBox functions in your routine. For example, if you have a variable named CellCount, you can insert the following statement:
When you execute the routine, the MsgBox function displays CellCount's value.
It's often helpful to display the values of two or more variables in the message box. The following statement displays the current value of LoopIndex and CellCount, as shown in Figure 13-2:
MsgBox LoopIndex & " " & CellCount
Using a message box to display the value of two variables.
Microsoft Excel X|
Notice that I combine the two variables with the concatenation operator (&) and insert a space character between them. Otherwise, the message box strings the two values together, making them look like a single value. You can also use the built-in constant, vbNewLine, in place of the space character. vbNewLine inserts a line-feed break, which displays the text on a new line. The following statement displays three variables, each on a separate line:
MsgBox LoopIndex & vbNewLine & CellCount & vbNewLine & MyVal
This technique isn't limited to monitoring variables. You can use a message box to display all sorts of useful information while your code is running. For example, if your code loops through a series of sheets, the following statement displays the name and type of the active sheet:
MsgBox ActiveSheet.Name & " " & TypeName(ActiveSheet)
I use MsgBox functions frequently when I debug my code. Just make sure that you remove them after you identify and correct the problem.
Was this article helpful?