Analyzing Macros and Identifying Errors


« Back to videos

Additional Information:

This module shows you how to step through code by pressing F8 in order to analyze the effect that each line has. It is not possible to use F8 to start a sub-routine that is triggered by an external event e.g. if you are using the Worksheet SelectionChange event, you cannot run the code from within the Visual Basic Editor. This is because these events are defined by the action a user takes within Excel. A SelectionChange macro has to know which range has been selected at outset so this variable can be referenced throughout the routine.

If you wish to step through a macro triggered by an external event, you should introduce a break point early in the code. When you trigger the code from Excel, it will run automatically until it reaches your break point, at which point you can step through it as demonstrated in the video.

You may encounter issues determining variable values by hovering your mouse above their names. If they are not being displayed, it is possible to evaluate all active variables by selecting View->Locals from the Editor's menu bar.

The reasons for running through code are twofold. Firstly, it is worth ensuring your code has the desired effect before it cycles through all your data. Remember, there is no option to undo the actions of an Excel macro. However, you can also find technical flaws in your coding. If Excel cannot process a line of your code, it will stop running your macro. It will throw up an error message which normally gives you the option to debug, as in the video.

Some error messages do not give you an explicit option to debug. Instead they terminate the macro. This is unhelpful as it does not tell you where the error is. If Excel is not giving you the option to debug errors, then go to Tools->Options->General->Error Trapping in Visual Basic Editor and select "Break on All Errors". If you are also using error handling, you will need to revert to "Break on Unhandled Errors" once you have debugged the code.

Error handling is an interesting topic because the "On Error Resume Next" and "On Error Goto" commands can be used in two ways. Their most common use is to let the macro run past, or terminate after unexpected errors. This is the technique demonstrated in the video.

There are also circumstances in which error handling allows you to deal with anticipated errors. Visual Basic can be used to open and process batches of files. It may be that some of these files do not need to be processed. Introducing a line like "On Error Goto CloseFile" could let you skip to the point at which that file is closed, if certain reference points could not be identified.

If your macro gets stuck in an infinite loop, it is suggested that you break the code using Escape. In the event this does not work, you should try the alternative method of Ctrl+Break. Occasionally your code will cause the entire of Excel to crash. So before attempting to run new code, you should always save your file.