Debug Excel VBA Code Line by Line for Errors

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 error 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:
Worksheets(“Sheet1”).Activate

You wrote:
Worksheets(“Sheet1”).Active

In such a scenario things can get tough and the programmer will need 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’
Worksheets(“Sheet1”).Rows(x).Copy
‘Go to sheet2. Activate it. We want the data here
Worksheets(“Sheet2”).Activate
‘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
Worksheets(“Sheet1”).Activate
‘Loop through the other rows with data
x = x + 1
Loop
End Sub

Description of the process:

  • 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:


2 thoughts on “Debug Excel VBA Code Line by Line for Errors

  1. Dean Visitor

    Sir,
    I found the video very useful and informative. I followed along as you walked through the entire process with the Debug.

    I could not detect any error or misplaced variable. What I am noticing is that Ln27 IS NOT the end product file of Ln 25. It looks like it is copying/renaming the first PDF file into the second PDF; which is incorrect.

    Sub NextInvoice()
    ‘H10 is the invoice number
    ‘H12 in the customer ID
    Application.DisplayAlerts = False
    Range(“H10”).Value = Range(“H10”).Value + 1
    Range(“A16:I35”).ClearContents
    Range(“D37”).NumberFormat = (“dd mmm yyyy”)
    End Sub
    Sub SaveInvWithNewName()
    Application.DisplayAlerts = False
    Dim DC_date As Date
    Dim myFile As String, lastRow As Long
    Dim myFile1 As String, lastRow1 As Long
    ‘Copy Invoice to a new workbook
    ‘Create [Proforma Invoice]PFI*.xlsx
    ‘Create [Proforma Invoice]PFI*.PDF
    ‘Create [Receipt]RE00*.xlsx
    ‘Create [Receipt]RE00*.PDF

    ActiveSheet.Copy
    NewFN = “T:\Invoices\Customer Invoices\TB 918273\” & Range(“H12”) & “_PFI00” & Range(“H10”).Value & “.xlsx”
    ActiveSheet.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
    myFile = “T:\Invoices\Customer Invoices\TB 918273\” & Range(“H12”) & “_PFI00” & Range(“H10”).Value & “.pdf”
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=myFile
    NewFeNe = “T:\Invoices\Customer Invoices\TB 918273\” & Range(“H12”) & “_RE00” & Range(“H10”).Value & “.xlsx”
    ActiveSheet.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
    myFile = “T:\Invoices\Customer Invoices\TB 918273\” & Range(“H12”) & “_RE00” & Range(“H10”).Value & “.pdf”
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=myFile
    value_1 = Sheets(“OrderSheet”).Range(“B41”)
    value_2 = Sheets(“OrderSheet”).Range(“D41”)
    value_3 = Sheets(“OrderSheet”).Range(“F41”)

    ActiveSheet.Range(“A7”) = value_1
    ActiveSheet.Range(“A37”) = value_2
    ActiveSheet.Range(“D37”) = value_3
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    NextInvoice
    End Sub

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *