Programming: Excel VBA Debugging for Beginners

Learning to debug your code is an important skill that will help you write better code and get your code into production faster. But for those Excel folks who have no other programming experience...where do you start? This post will walk you through getting your debugger started, setting breakpoints, and looking at variables. When you finish you’ll be able to walk through your code one line at a time and see what’s going on. [The code sample was taken from this post.]

Starting

Open Microsoft Excel and from the Tools menu select Tools->Macro->Visual Basic Editor. This will open the Microsoft Visual Basic IDE (integrated development environment).


Figure 1

Figure1 shows you what you will see when you start the Visual Basic Editor with a brand new project. You can see in the Project pane a folder called Microsoft Excel Objects. By default there are three sheets and ThisWorkbook. In the pane double click on the sheet you want to associate the macro with. I have double-clicked on Sheet1 and the VB IDE opens a new window that has two combo boxes at the top. Add the code below to the new window.

Sub MyMacro()

    Dim dcell As Range

    Set dcell = ActiveSheet.Range("Bi")
    directory = "c:\test\" & dcell.Offset(-3, 0).Value & "\"
    filetext = Selection.Value & ".xls"
    Workbooks.Open directory & filetext
    
End Sub

Figure 2

You should now have something that looks like Figure 2.

Eyeball Your Code

The beginning of the debugging process requires that you look at your code and step through it in your head. As I look through the code I see ActiveSheet.Range(“Bi”). Now “BI” is a column in the workbook but it’s not a cell. So I change that code to read ActiveSheet.Range(“B1”). To correctly address the cell I’m interested in.

I also notice that I’m really interested in the Value at “B1” not the cell itself. So I adjust the code to read:

    Dim strDir As String

    strDir = ActiveSheet.Range("B1").Value

Okay now I am getting the text out of the correct cell in the form I need it in.

Don’t Nest Statements

Looking at the next line I see dcell.Offset(-3,0).Value. What was I thinking? I have a negative number for the row...which just isn’t going to work. I realize now that I’m using the Offset property wrong and I can replace that text with strDir.

But what if I didn’t know that I was using the Offset property incorrectly? I should write my code like this:

    Dim strValue As String

    strValue = dcell.Offset(-3, 0).Value;
    directory = "c:\test\" & strValue & "\"

With dcell.Offset() nested within the code building the directory string I have no way to directly verify what the resulting value is.

Set a Breakpoint

A breakpoint causes the debugger to stop program execution at the location where the breakpoint is defined. When I start debugging a subroutine I usually start at the beginning. I then verify that each line is working as I expect and I move my breakpoints down.

Setting breakpoints in the Visual Basic Editor is easy. Simply move your cursor to the gray area to the left of the line you want to break on and click the left mouse button. Click the left mouse button again and you have just removed the breakpoint.


Figure 3

Setting a breakpoint at the beginning of the subroutine looks like Figure 3.

Step Through the Code

Now that you have a breakpoint set perform the action that causes the subroutine to be called. Using the Shift-F8 key combination steps you through your code. You can then check the values at each point. There are two ways to do this. The easiest way is to move your mouse cursor over the variable that you are interested in and hover. A small window will open telling you what is in the variable. The second way is to watch a variable. To watch a variable click on the main menu bar View->Watch Window. Move your mouse cursor over the new Watch Window and right-click. The brings up a context menu from which you can select “Add Watch...”, type in the name of the variable and select “OK”


Figure 4

That’s It

That’s the basics. As you become more comfortable with the debugger you can look at other Views that will tell you more advanced information.

Basic Rules:

When you write code always verify that you are getting the values you expect.

Think about what your code will do if the data you expect is malformed. Expect malformed data and write your code accordingly.

Keep things simple. Don’t embed statements into statements, at least not when you’re just getting started.

Technorati tags: Excel, Microsoft, debugging, VBA, beginners, break points