Excel VBA Macro Programming

When working on an application in VBA, there are three modes that you can be in:

• Design time ‚   ‚   When you are working on the code for the application or designing a form.

• Runtime ‚   ‚  When you run your code or your form. The title bar of the VBA screen will contain the word ‚“running, ‚½ and at this point you can view code but you cannot change it.

• Break ‚   ‚  If you press Ctrl-Break during runtime, it will stop execution of your code. A dialog will be displayed with the error message, ‚“Code execution has been interrupted ‚½ and several buttons . Clicking the Debug button will take you into the code window.

When you click Debug, you go into instant watch mode, also known as debug mode. You'll be able to see your code and the line it has stopped at will be highlighted in yellow. This is the line that is causing the problem. You can place your cursor on any variable that is in scope and it will give you the value of it instantly. You can also move the point of execution by dragging the yellow arrow to the line of code to be executed.

Try this simple program:

Sub Test_Debug() x = 2 Do Until x = 1 x = x + 1 Loop End Sub

When you run this, the program never finishes because x will never equal 1. Press Ctrl-Break, and the error window will appear. Click Debug, and you will be in instant watch mode in the code window. Move your cursor across any instance of x and its value will appear. See Figure 7-1.

Figure 7-1: Example of instant watch

You can restart your code by clicking the Run symbol on the toolbar (the triangle symbol pointing to the right) or pressing F5; it will start from the point where it was stopped. You will notice a yellow arrow on the left of the code that indicates the current execution point. Try dragging the yellow arrow to a new start point, such as x = 2 , and then rerun the code. This is useful if you want to restart a loop or an If condition after you have changed code due to a bug.