---

Introduction

If there's one part of the Excel environment that has remained fairly stable over the years it's the Visual Basic Editor (VBE) and associated development environment. While Excel 2007 has seen a monumental change, look under the covers and you'll find the same old VBE that has been there since Excel 97. For those who've never delved too deeply, this article gives a quick run down of the main features of the VBE and shows how it can be used to debug macros and VBA code.

The examples in this article have been tested with Excel 2000 and Excel 2007. For those who want a more general introduction to programming Excel with Visual Basic for Applications (VBA), then our Introduction to Excel VBA is the place to start.

To get to the VB enviroment the simplest option is to press Alt-F11 from within Excel. This opens the VB environment, with a Project tree on the left-hand side showing open workbooks and modules, and the VB Editor on the right hand side. Alternatively in Excel 2000 you can use the Tools > Macro > Visual Basic Editor menu option. In Excel 2007 it's still Alt-F11, but you can also get to it via the Developer Ribbon tab (if you don't see this then click on the Office button, then Excel Options > Popular and tick the check box for Show Developer tab in Ribbon).

Debugging

Sooner or later you will find that you have a macro that doesn't quite work. Either it will be a macro you have written yourself or else it may be a macro that a user has knocked up and which no longer functions the way it used to. The Visual Basic editing environment supports a good degree of debugging, which should help to track down the error.

Faced with a macro that isn't working there are various places to start in tracking down the root of the problem. If it is clear where the macro is failing then it makes sense to home in on that part of the code. Alternatively for smaller, less complex macros, it may be that single stepping through each line of code might be a better start.

Single Stepping Through Code

Let's take the latter approach first. As an example let's turn to our first macro:

Sub Example()
  Dim number As Integer
  Dim finished As String
  
  Sheets("Sheet1").Activate
  ActiveSheet.Cells(1, 1) = "Monday"
  Selection.AutoFill Destination:=Range("A1:A7"), Type:=xlFillDefault
  
  For number = 1 To 7
    ActiveSheet.Cells(number, 2) = number
  Next number
  
  finished = "Finished processing sheet!"
  MsgBox finished

End Sub

To single step through this macro we can either go into the VBE, switch to the module containing the macro and then click on the 'Single Step' button from the Debug toolbar or the Debug menu, or else select Tool/Macro to open the macro dialog and then select the 'Step Into' command button. Either of these will open up the Debug window, which lists the macro code with the first line of the macro high-lighted, the Debug toolbar will also become active. To execute each line of the macro in turn we can either use the 'Step Into' button from the toolbar, or press F8. As each line is executed the cursor moves to the next command, and we can also examine the underlying sheets to see what is happening by switching from the VBE to Excel itself.

Single stepping through our sample macro allows us to see Excel selecting the sheet called 'Sheet1', entering the value 'Monday' in the first cell and then using auto-fill to enter the rest of the days of the week into the first column. It then cycles through the numbers 1 to 7 and adds them to the second column. When we are done we can switch to the sheet and see that the macro has done as we expect.

In addition to the 'Step Into' command button there's also a 'Step Over' button, which on the face does exactly the same thing - it executes the next line of the macro. However if your macro calls other macro procedures and functions then 'Step Into' will follow the execution path down into the procedure or function line by line, whereas 'Step Over' will execute the procedure call as a single line. In a complex macro using 'Step Over' will be more useful early on, because it can be used to pinpoint where the code goes wrong, and if it highlights a procedure or function going wrong you can then use 'Step Into' to follow execution down until you high the error.

The 'Step Out' command is also used when debugging code contained in multiple procedures and functions. If you have stepped into a procedure and decide that you want to carry on debugging in the higher level procedure or function, then 'Step Out' will finish executing all the code in the current procedure and then stop once more on the next line of the calling procedure.

Immediate Execution Window

What happens if we hit an error? We can simulate this by changing the worksheet to 'Sheet100' and then attempting to single step through the macro once more. When we single step through the 'Sheets("Sheet100").Activate' line, Excel hits an error and opens up a 'Microsoft Visual Basic' dialog box. Pressing the Help button will give the help file associated with the given error (in this case 'Run-Time Error 9 - Subscript out of range' - not the most helpful of messages!). The Debug button will make the Debug window active again, and put you into Break mode, where you can attempt to dynamically fix the problem. In Break mode further macro execution is pending, and all variables retain their values. Pressing the 'End' button will stop further macro execution and you'd have to start the macro again from scratch once you've fixed the problem.

If we return to the Debug window we can look at some of the other facilities of the VBE debugging environment. The first of these is the 'Immediate' window, accessed from the View menu or via the short-cut key of Ctrl+G. The Immediate window allows us to interactively enter Visual Basic expressions and commands and have them executed. In this way we can often identify or investigate the cause of a problem, and often we can even work out the correct code, try it out and then copy it to the clip-board for incorporation into our macro. The code we enter into the immediate window doesn't necessarily have to be anything to do with our macro. For example if we enter a line such as the one below (not forgetting to press carriage return at the end of the line):

	Print 2 + 2 

The Visual Basic interpreter will execute this and print the result (4) on the next line. We could also enter the same line as follows to get the same result:

	? 2 + 2

More usefully we can enter lines such as:

	? worksheets.count

This will return the count property of the worksheets collection, in other words it will tells us how many worksheets we have in our current workbook. In this way we can find out about the properties of the active objects that our macro is dealing with - and of course we can execute the methods for those objects too. It is often the case that using the Immediate window when a macro has hit an error may allow you to discover a fix, which you can then copy to the clip-board and then paste into the macro to run again. You can also use the code completion function with the Immediate window to see what variables and methods are available. Entering Workbooks. and then pausing for a second will cause a pop-up window to display with the list of methods and variables supported by the Workbooks object.

One final point worth making is that there is also a Debug.Print command that you can enter into your macros. This command prints out statements to the Immediate Window, make it a useful tool for embedded debugging statements into your macros. For example this simple macro will tell us how many worksheets there are in the active workbook:

Sub test()
    Debug.Print "Number of sheets in workbook: " & ActiveWorkbook.Worksheets.Count
End Sub