Debug Excel VBA Code Line by Line for Errors

Home More Excel (VBA) Training Videos

You can check or debug Excel VBA code line by line for errors by stepping through the macro code one line at a time. Now you see what each line of macro code does  and this can be quite helpful if you are getting eror messages.
You normally get error messages if you haven't defined a variable especially in Excel version 2010. Earlier Excel versions were more forgiving. Also you may be accessing a worksheet that doesn't exist. Sometimes you get no errors but the code doesn't work as expected. this can happen when instead of writing:

You wrote:

In such a scenario things can get tough and the programmer will ned to study the code manually line by line.

Now let's say we wrote the following macro to transfer or paste all rows containing the data with 'car' in the first column of sheet1 into sheet2:

Sub mycar()
'Let's start at row 2. Row 1 has headers
x = 2
'Start the loop
Do While Cells(x, 1) <> ""
'Look for data with 'Car'
If Cells(x, 1) = "Car" Then
'copy the row if it contains 'Car'
'Go to sheet2. Activate it. We want the data here
'Find the first empty row in sheet2
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
'Paste the data here
ActiveSheet.Paste Destination:=Worksheets("Sheet2").Rows(erow)
End If
'go to sheet1 again and actvate it
'Loop through the other rows with data
x = x + 1
End Sub

  • We click on the developer tab
  • Then select Visual Basic from the code group
  • In the Microsoft Visual Basic for Applications window we click on Debug
  • From the drop down menu we select 'Step Into'. You have the option of pressing F8 from the keyboard also
  • Now we make the code window smaller than our worksheet window so that every time we press F8 or clcik 'Step Into' we can see our highlighted code
  • At the same time the position of our cursor in the worksheet can changeand you can observe what's happening in the worksheet
  • If the Excel  Editor encounters an error an error dialog box is displayed
  • You can now take corrective action on the relevant line of code
  • Rerun the debug feature to discover any further errors

View the training video below to learn about the debugging feature in MS Excel:

Watch this video on YouTube

Home More Excel (VBA) Training Videos