Using Message Boxes in Debugging

There are methods besides those discussed already that can isolate bugs in code. They may be considered somewhat crude, but they do work. For example, message boxes are always useful for letting you know what is going on. They can display the value of a variable or even several variables by concatenating them together. They are extremely useful when you have a large procedure and you do not know the region the bug is in. For example, a large piece of code appears to run but hangs and will not respond to Ctrl-Break . Perhaps it ran perfectly up to now but has hit certain circumstances that cause it to hang.

The question is, how far into the code do you get before the issue occurs? You could try stepping through it, but this is very time consuming if it is a large procedure. Placing message boxes at strategic points throughout the code will give you an idea of where it stops. Make sure all the message boxes display something meaningful, such as ‚“Ok1, ‚½ ‚“Ok2, ‚½ and ‚“Ok3. ‚½ You can then see what stages the program has covered before it hangs. Be sure you clear all the extra message boxes out once you have found the bug! You can also concatenate variables together.

 Sub test_for() 
 For n = 1 To 4 
 For m = 2 To 8 
 
 MsgBox m & " xxxxx " & n 
 
 Next m 
 
 Next n 
 
 End Sub 

This message box will display the values of m and n separated by a line of x's. The purpose of the x's is to prevent null values or spaces being hidden. Although it would not happen in this example, variables can sometimes end up with Null values or be spaces if they are string variables, in which case you would only see one number appearing, and how would you know whether it is the value of m or n ? The row of x's distinguishes this.

If you want a continuous readout of variables while the program is running, there are ways to get one. Debug does not offer this option, but with a little ingenuity it can be accomplished. You can change the caption properties on both the Application object (spreadsheet) and a UserForm object using code. This assumes that you have already defined UserForm1 :

 Sub test_for() 
 
 For n = 1 To 4 
 
 For m = 2 To 8 
 
 Application.Caption = n & " xxxx " & m 
 
 UserForm1.Caption = n & " xxxx " & m 
 
 Next m 
 
 Next n 
 End sub 

This will display the variables as the program is running in the caption of the window. Depending on how fast the variables change and how long the procedure is, you will be able to see patterns occurring and see what is happening live at each stage. In terms of patterns, you will be able to see sequences of numbers , how they ascend and descend, what the maximum values they get to are, and so on. If a major error occurs, you can see at what value it is occurring.

You can reset the application title bar afterward by setting it to an empty string in code:

 Application.Caption = ""