Program by Levels

Tutorial on VBA for Excel (Macros)

bringing you the simplest and most efficient ways
to do things in VBA for Excel

VBA Lesson 6 of 33: Testing  Macros in the Visual Basic Editor for Excel
(Levels: Beginners, Intermediate)

Note: For a better comprehension of the lessons in section 1 on the Visual Basic Editor and the Macro Recorder www.excel-vba.com has created a workbook (vba-tutorial-editor.xls) one of the 25 that are included with the VBA for Excel Desktop Tutorial.

In no other programming environment can you test a procedure while seeing it at work step by step. Here is how it works

Print this page and follow the instructions.

Close every program on your computer. Open Excel and one of the workbooks mentioned above. Open the Visual Basic Editor. On the Window status bar at the bottom of your screen you can see that Excel is open and also the VBE. Right click on the status bar in the empty space where I have added the orange stars:

The following menu appears:

Click on "Tile Windows Vertically" and the screen will look SOMEWHAT like this. Yours will be full screen.

The Excel workbook occupies half of the screen and the VBE occupies the other half. Excel can be in the left half of the screen or the right half. Make  the Code window wider in the VBE so that you can see most of the code (see how in section 3 of this website).

Testing and modifying a VBA procedure

In the Excel half of your screen select the sheet "Test 2"

Testing the entire procedure

Click anywhere within the procedure "proTest" in the VBE and click on the "Run" icon:

In this case you probably didn't see much happening in the VBE window but you can see in the Excel window that the procedure has been executed.

Delete all values in column C for the next exercise.

Testing the entire procedure step by step

1- Click anywhere within the macro "proTest" and press the F8 key at the top of your keyboard. The fist line of code turns to yellow and a small arrow appears in the margin. We are in the execution process.

NOTE: While you are running the macro step by step you can stop the execution at anytime by clicking on the stop button in the toolbar.

The line that is highlighted in yellow is the line that will be executed next time you click on F8.

2- Click again on F8 until the macro has been executed (no more yellow line). Watch the Excel screen at each step and see what happens.

Modifying the procedure while testing the entire procedure step by step

You can modify the code while running the procedure step by step. Follow the instructions below:

1- In the Excel window delete  all the values in column C.

2- Click anywhere within the macro "proTest" and press the F8 key.

3- Click 4 more times until you reach the following line:

4- In the line above change C1 to C3

5- Click on the small yellow arrow in the margin with the left button of your mouse, HOLD the button down and drag the yellow arrow back up on the line that you have just modified.

6- Click F8 again.

7- Finish running the procedure with the F8 key or the "Run" icon . See that the procedure has worked only from cell C3 down. You have modified a procedure while running it.

8- Cancel the change that you have made in the code, change "C3" back to "C1"

Delete all values in column C for the next exercise.

Testing part of the procedure

You can test only part of a procedure. For example:

1- Click F8 to start the execution. Drag the yellow arrow to line 11:

2 - Click F8 one more time  and then click on the stop button of the toolbar .

You have executed just part of a procedure. Delete all values in column C for the next exercise.

Now let's do it another way.

1- Click anywhere within the procedure "proTest" and click on F8 only one time.

2- Right click in the gray margin at the level of line 11 (same as the exercise above) and select "Set Next Statement" in the contextual menu.

3 - Click F8 one more time  and then click on the stop button of the toolbar .

You have executed just part of a procedure. Delete all values in column C for the next exercise. Start your test there.

When you have a long procedure you can test the top part of the procedure the bottom part or any intermediate part using breakpoints and dragging the yellow arrow around.

Let's test the top part:

1- At the level of the line 13 "Loop" left click in the gray margin and the line is highlighted in red/brown. This is a breakpoint.

2- Click anywhere within the procedure "proTest" and click on the "Run" icon:

3- When the line with the breakpoint also carries the yellow arrow

In the Excel window you can see that all the lines above have been executed. One name has been created in cell C1 and cell C2 is selected.

4- Stop the execution with the icon and delete all values in column C for the next exercise.

Note: You remove the breakpoints by clicking on the brown dot or if you have many breakpoints you can go to the menu bar "Debug/Clear All Breakpoints"

Now let's test the bottom part

1- In the Excel window select cell C4

2- Set a breakpoint on line 12:

4- Click anywhere within the procedure "proTest" and click on F8 only one time

5- Click and drag the yellow arrow to line 12 (where the breakpoint is).

6- Finish running the procedure with "Run" icon . See in Excel that cell A1 is selected.

You have executed the bottom part of your procedure

VERY IMPORTANT NOTE: When you start working with variables in VBA you need to declare them before you move on to any other lines of code that you want to execute. You click on F8 once, you click on F8 a second time and the yellow arrow moves down to the first line of code after the Dim's. From there you can start moving around. That is why it is recommended to "DIM" all you variables at the beginning of the procedure.

This page is one of the 33 lessons from the
Tutorial on VBA for Excel (Macros)
VBA for Excel made simple