If you save your macros in the Personal Macro Workbook, that workbook serves as the container workbook, and when the macros are run they affect the active workbook. As you test the macro, you will be changing both these workbooks. The Personal Macro Workbook is changed when you make changes to a macro. The active workbook is changed as you run the macro. This means that during the testing process you will want to save the Personal Macro Workbook. The active workbook, however, should not be saved during the testing process. You will be testing repetitively, and you will always want to start each round of testing with the active workbook in its original state.
Unfortunately, it is all too easy to inadvertently save the active workbook during testing. As a precaution, I strongly suggest that before starting to test, you make a backup copy of the active workbook, and always keep that backup in its original state. That way you can recover if you accidentally save the active workbook in some "interim" state while testing the macro. As stated earlier, mistakes are made.
Syntax errors are errors in VBA statements, such as a misspelled function name or For without a corresponding Next. Those types of errors will generate an error message when a macro is compiled, as described earlier in this tutorial. Syntax errors also include errors in dot notation, such as calling a method on an object which does not have that method. Those errors will generate a VBA error message when a macro is run. Because syntax errors generate error messages they are relatively easy to identify and fix, although some error messages may not identify the problem as clearly as we might wish.
Logic errors are more subtle. There is no problem with the syntax of the macro and it will compile and run without any obvious problem, but we discover that the macro does not do what we wanted and expected it to do. Programming errors that cause such unexpected or undesirable results are called logic errors, popularly known as "bugs." The process of identifying and eliminating logic errors is called "debugging."
First, remember that the macro will run on the currently active worksheet, so make sure the correct one is active. If the macro is designed to be run when the cursor is at a certain point, you need to first position the cursor at that location before debugging the macro (if the macro does not do that as its first step, which it should).
To step through a macro, open the macro in the VBA editor. Position the cursor in the macro code, then click on Debug/Step Into (or press F8). The next statement to be executed is highlighted in yellow, and indicated by an arrow in the margin. Each time you click that menu item (or press F8), the highlighted statement is executed, and the next statement to be executed is highlighted. By this process you can watch the effect of each statement on the worksheet.
Once you have identified a problem, you can stop the macro by clicking Run/Reset. You can then correct the macro code, and save the changes you have made to the macro by saving the Personal Macro Workbook. Since the macro has partially run the active workbook has been changed, so you should close it without saving, and reopen it for the next round of debugging. If you save it accidentally, you can use your backup copy to recreate it in the state it was before the macro is run. To do so, make a copy of the backup, and rename it (keeping the backup copy unchanged).
To set a breakpoint, navigate to the statement where you want the breakpoint and click in the margin on that line (or position the cursor on that line and click on Debug/Toggle Breakpoint, or press F9). A dot will appear in the margin, indicating the breakpoint. Doing that again toggles the breakpoint on or off.
You can have multiple breakpoints in the macro; to run the macro up to the next breakpoint click on Run/Continue. To remove all breakpoints, click on Debug/Clear All Breakpoints.