Tutorial 1Tutorial 2Tutorial 3Tutorial 4

“I’m not afraid of storms, for I’m learning how to sail my ship.” – Louisa May Alcott

Welcome to Ultimate VBA Tutorial Part 4 – How to Debug VBA Code. If you haven’t seen part 3 then you can find it here.

In this tutorial, we are going to look at and use the built-in debugging tools.

Learning Outcomes

When you finish this tutorial you will be able to:

1. Find Compile errors in your code before you run it.
2. Step through your code one line at time.
3. Step in and out of procedures.
4. Pause your code on a specific line.
5. View the Call Stack and see which procedures are currently active.
6. See what values your variables contain using the Locals window.
7. Use the Watch window to check out the result of functions and calculations.
8. Get information about any range or range variable using the Watch Window.
9. Use the Debug.Print statement in your code to write out values.
10. Use the Debug.Assert statement to help make your code BulletProof.

Useful References

How to Make Your Code BulletProof (Article)

Introduction

The purpose of this tutorial is to teach you how to use the debugging tools that come with VBA. These are mostly found on the Debug menu in the VBA Editor.

We use the debugging tools to examine our code as it is running. We can pause the code, step through it line by line and examine the values of our variables.

As well as the debugging tools, VBA has two debugging statements. These are Debug.Print and Debug.Assert. We can use these statements in our code. They allow us to print out values(Debug.Print) and check values in our variables(Debug.Assert).

The first tool we will look at is Debug Compile. Unlike the other debugging tools – this is used before we run the code. It checks for errors that may currently exist in our project.

The Compile Tool

There are three types of errors in VBA

1. Syntax – relating to one line.
2. Compile – relating to more than one line.
3. Runtime – errors that occur when the code is running.

Syntax errors occur when we write incorrect syntax in a line of code. Some examples are:

For i  1 To 10  ' Expected: =
If x = 6        ' Expected Then or GoTo
Do While        ' Expected Expression
Dim i As        ' Expected New or Type name

' Correct syntax
For i = 1 To 10
If x = 6 Then
Do While x > 6
Dim i As Long

Compile errors involve more than one line. The syntax may be fine but there may be a corresponding statement missing. Examples are

• an If Statement without the End If
• a For statement without Next
• a Do While statement without Loop
• a call to a procedure that doesn’t exist
• incorrect parameters for a Procedure

Note: The term “Procedure” refers to a Sub or a Function.

The following are some code examples of Compile errors

Sub CompileErrors()
    
    Dim i As Long, x As Long
    
    For i = 1 To 10 ' For Without Next
    
    If x > 5        ' Block if without End if
    
    PrintValue      ' Argument not optional

End Sub

Sub PrintValue(ByVal val As Long)

End Sub

The correct version of the code might look like this

Sub CompileErrors()
    
    Dim i As Long, x As Long
    
    For i = 1 To 10 
    Next i
    
    If x > 5 Then 
    End If
    
    PrintValue 55 

End Sub

Sub PrintValue(ByVal val As Long)

End Sub

To find compile errors in our code we use Debug->Compile VBAProject from the Debug menu. We do this prior to running the code. The Compile tool will also find any existing syntax errors in our code.

Debug.Compile searches through the code and displays the first error it finds.

Tip: VBAProject is the name of the current project. This can be changed by going to Tools->VBAProject Properties and changing the name in the “Project Name” text box.

When you fix the current error you can Debug.Compile again to search for the next error in your code.

If Debug.Compile does not find any errors then it becomes disabled(greyed out) in the menu.

There is a progress bar the shows Debug.Compile happening. However, unless you have a slow computer with a large amount of code, it will probably happen too fast for you to notice.

1. Open the Visual Basic editor and create a new module.
2. Copy the code below to the module. The code contains four errors.
3. Using the Compile tool to find and fix all the errors.

Option Explicit

Sub PrintOutValues()

    Sheet1.Cells.ClearContents
    
    Dim arr As Variant
    arr = Array("Apple", "Orange", "Peach", "Plum")

    For i = LBound(arr) To UBound(arr)
        If arr(i) <> "Apple" Then
            Sheet1.Range("A" & i).Value = arr(i)
    Next i
    
    PrintValue i

End Sub

Public Sub PrintValues(ByVal val As Long)
    
    Debug.Print i
    
End Sub

Stepping through the code

It is very useful to examine our code as it is running. If we couldn’t do this it would make finding errors very difficult.

The two main reasons for examining our code as it is running are

1. To find and fix runtime errors in our code.
2. To help understand our code and what to see what it is actually doing.

Debugging tools are well known for helping to find and fix errors. That is the main reason for using them.

However, the second reason is not as well known. Using the debugging tools can help you see exactly how VBA code works. This is of enormous benefit when you are learning VBA.

For example, if you wanted to understand how Collections work, you could write some simple code and then use the debugging tools to see what exactly the code is doing.

To examine our code we must be able to pause the code and step through it line by line. Let’s look at the tools that allow us to do this.

Step Into

Using Step Into allows us to walk through our code one line at a time. While doing this we can examine the variables at each stage.

We step in by using Debug->Step Into from the menu. However, it is much more practical to use the shortcut key F8 or the Debug toolbar.

When we press F8 in a procedure it will highlight the first line of the procedure in yellow.

Each time we press F8 the code on the current line runs and the highlight moves to the next line. Notice that it skipped the line starting with the Dim keyword.

When we step through the code we skip blank lines and declaration lines. Declaration lines are lines starting with Dim, Private and Const.

If there is a line that contains a call to a procedure then using Step into will step into this procedure.

The code steps into Print Value

When it reaches the end of Print Value it will step out and continue from the line after the call to Print Value.

1. Copy the code below into a new module.
2. Open the Immediate Window(Ctrl + G) and delete any contents.
3. Step through this sub one line at a time until you reach the end of the sub.
4. Note that the code prints after it leaves the Debug.Print line.

Sub CompileErrorsAct()

    Dim x As Long
    
    x = 7
    
    PrintValue x * 10
    
    x = 9
    
    PrintValue x * 20

End Sub

Sub PrintValue(ByVal amount As Long)
    
    Debug.Print amount
    
End Sub

Using a Breakpoint

Stepping through code from the very first line is not practical for most debugging tasks.

Luckily for us, VBA has Breakpoints. We can place a breakpoint on any line of code and VBA will pause here when the code runs. We cannot put a breakpoint on declaration lines or blank lines.

If you want to set a breakpoint on a certain line, you do so by clicking on the left margin of the line. You can also click on the line and press F9.

You use the same action to turn off a breakpoint. To remove all breakpoints use the shortcut keys Ctrl + Shift + F9.

When you run the code it will pause on the breakpoint. In the following image you can see a mixture of brown(the breakpoint) and yellow(the current line)

1. Use the code from the previous activity.
2. Place a breakpoint on the lines “x = 7” and “x = 9”.
3. Run the code using F5 and get it to stop at the first breakpoint.
4. Press F5 again and the code will stop on the second breakpoint.
5. Press F5 again and the code will run to the end of the sub.
6. Clear all the breakpoints(Ctrl + Shift + F9).
7. Put a breakpoint on the line Debug.Print in the PrintValue sub.
8. Run the code again and it will stop on this line. Step through the rest of the code using F8.

Step Over

The only difference between Step Over(Shift + F8) and Step into is that Step Over will not step into a procedure. It will step over as the name implies.

When using Step Over, the highlight will move to the next line even if there is a call to a procedure on the line.

It is important to understand that Step Over does not skip the code. VBA will run the code in the procedure and then pause the code on the next line after the procedure.

It is important to keep in mind that we want to run the code exactly as it would run normally. If skipped lines this would mean that the application would behave differently.

We can use Debug->Set Statement to set the next statement to be executed. This can be useful in some specific situations but mostly we want to use the Step statements.

1. 1. Copy the code below into a new module.
   2. Place a breakpoint on the line

amount = 8

1. Click in the sub StepOver and then Run(F5) the code. The code will pause on the breakpoint.
2. Step through the rest of the code using Step Over(Shift + F8).
3. Note that the code does not enter any subs.
4. When you step over PrintValue, note that the value has been printed to the Immediate Window.
5. When you step over WriteToSheet, note that the value has been written to A1 in the worksheet Sheet1.

Public Sub StepOver()
    
    Dim amount As Long
    
    amount = 8    
 
    PrintValue amount

    WriteToSheet amount

End Sub

Public Function PrintValue(ByVal amount As Long)
    ' Print to Immediate window
    Debug.Print amount
End Function

Public Sub WriteToSheet(ByVal amount As Long)
    ' Write the worksheet
    Sheet1.Range("A1").Value = amount
End Sub

Step Out

Step Out runs the code to the next line outside the current procedure. It will then pause and highlight this line

If you are in the top most procedure then the application will complete without stopping anywhere.

The following image shows an example of stepping out

At any point where the code is paused we can select Run->Continue(F5). This will continue running the code until it meets a breakpoint or until it finishes.

1. We are going to use the code from the previous activity.
2. Step through the code using Step Into(F8).
3. When you reach the call to PrintValue, the code will enter the sub.
4. Step out of this sub(Ctrl + Shift + F8).
5. Note that the value will have been written to the Immediate Window.
6. When you reach the call to PrintValue, the code will enter the sub.
7. Step out of this sub(Ctrl + Shift + F8).
8. Note that the value will have been written to the worksheet cell A1.

The Call Stack

The Call Stack window(Ctrl + L) is a simple display that shows you the current procedures that are in use.

For example, if you have the following three subs

Public Sub Level_1()
    Level_2
End Sub

Public Sub Level_2()
    Level_3
End Sub

Public Sub Level_3()
    MsgBox "Level_3"
End Sub

We are going to run Level_1. This calls Level_2 which in turn calls level_3. When the code is paused in Level_3 you can use the call stack to see the procedures in use.

The first procedure is at the bottom of the stack with the current procedure at the top.

The stack can be very useful because you can have procedures that are called from different parts of the code. Using the stack can show you where exactly which procedure called the current one
.

In the following code, SendEmail can be called from either of the CreateReport subs.

Public Sub CreateReport_1()
    
    ' Report Code here
    
    SendEmail
    
End Sub

Public Sub CreateReport_2()
    
    ' Report Code here
    
    SendEmail
    
End Sub

Public Sub SendEmail()
    
    ' Code to send email
    
End Sub

1. Copy the code below into a new module.
2. Place a breakpoint on the Debug.Print line.
3. Run the code from the CreateReport module.
4. When the code stops on the breakpoint, open the Call Stack window.
5. Close the Call Stack window.
6. Step out of the Level 3 sub.
7. Open the Call Stack to see what it looks like now.
8. Close the Call Stack window.
9. Step out of the Level 2 sub.
10. Open the Call Stack to see what it looks like now.
11. Close the Call Stack window.

Public Sub CreateReport()

    Level2

End Sub

Public Sub Level2()

    Level3

End Sub

Public Sub Level3()

    Debug.Print "Level3"
    
End Sub

Locals Window

We use the Locals Window(View->Locals Windows from the menu or Alt + V, S) to see the values of our variables.

The Locals window is automatically populated when our code is running. We cannot add or edit variables in this window.

The window contains all the variables in our current procedure.

The basic variable types are like worksheet cells – they only contain one value.

If a more complex type of variable is in the Locals window(e.g. Array, Collection, Workbook etc.) you will see a plus sign to the left of it. If you click on the plus sign, it will expand and you can see all of the members.

Take the following code

Public Sub ExampleVariables()

    Dim x As Long, y As Long
    x = 5
    y = 10
    
    Dim coll As New Collection
    coll.Add "Apple"
    coll.Add "Orange"
    coll.Add "Pear"

End Sub

If we pause the code at the End Sub line, the Locals window will look this

1. Copy the code below into a new module.
2. Bring up the Locals Windows.
3. Step into the code one line at a time.
4. Make a note of the values in the Locals Windows for each line.

Public Sub CheckLocals()

    Dim i As Long, x As Long
    Dim s As String, c As Currency
    
    i = 6
    x = 6 * 675
    
    s = "John" & " " & "Smith"
    
    c = 3.29 * 12.34

End Sub

The Watch Window

The Locals window can be useful when you are starting off and writing very simple code.

However, you will outgrow it pretty quickly and this is where the Watch Window(View->Watch Window from the menu or Alt + V, H) comes in.

We can examine variables, calculations and even functions in the Watch Window.

There are three ways to add an item to the watch window

1. Right-click on the variable in question, select Add Watch and click Ok when the dialog box appears appears.
2. Highlight the item in question and drag it into the watch window(it must be visible).
3. Click in the watch window and type the variable name(the Watch window must be visible).

You can also edit the Watch Window which is very useful. Just click on the Watch and type or delete text as required.

The reason the Watch window is useful is that we can create our own custom Watches.

Take a look at the following code

Public Sub ExampleVariablesWatch()

    Dim x As Long, y As Long
    x = 5
    y = 10
    
    Dim coll As New Collection
    coll.Add "Apple"
    coll.Add "Orange"
    coll.Add "Pear"

End Sub

The following are some examples of watches we could add to the watch window.

1. Copy the code below into a new module.
2. Place a breakpoint at the end of the sub and run the code until it stops here.
3. Bring up the Watch Window if it is not visible.
4. Right-click on the variable amount, select Add Watch and click Ok. The amount variable appears in the Watch window.
5. Double-click on the variable amount to select and then drag it into the Watch Window.
6. Right-click on the Watch Window and select Add Watch.
7. Type Amount + 11 and click Ok. You will see the value for Amount + 11 as 800 in the Watch Window.
8. Right-click on Amount + 11 in the Watch Window and select Edit Watch.
9. Change the watch to Amount – 89 and Click Ok. You should see the value 700 for this watch.
10. Click on any watch in the Watch window. The press Delete on your keyboard. This will delete the watch.

Public Sub UseWatch()

    Dim amount As Long
    
    amount = 789

End Sub

Watch Window and Functions

The Watch Window can even give you the result of functions. This includes the built-in VBA functions or ones you create yourself.

It is good practice only to use simple functions in the Watch window or Excel may have unexpected results(i.e. it may crash!).

Let’s have a look at an example of using a function in the watch window. Take a look at the following code

Public Sub CreateReport()
    
    Dim x As Long, sText As String
    
    ' VBA Function
    sText = Left("John Smith", 4)
    
    ' User Function
    x = GetResult(5)
   
End Sub

Public Function GetResult(ByVal val As Long) As Long
    GetResult = val * 10
End Function

We can place the functions in the watch window and check the results.

Notice that we can try different parameters in the Watch window.

We can also use any functions in the watch window – you can see that we used the Right function here.

1. Copy the code below into a new module.
2. Place a breakpoint at the end of the sub GetNames.
3. Run the code until it stops here.
4. Bring up the Watch Window if is it not visible.
5. Drag GetFirstName(“Jack:Smith”) to the Watch window.
6. You will see the result is Jack.
7. Edit the watch so that the string reads “Frederick:Smith”.
8. You will see that the value of the watch changes to Frederick.

Public Sub GetNames()

    Dim sFirstName As String
    
    sFirstName = GetFirstName("Jack:Smith")
    Debug.Print sFirstName
    
    sFirstName = GetFirstName("Jill:Lee")
    Debug.Print sFirstName

End Sub

Public Function GetFirstName(ByVal sName As String) As String

    ' Get the position of the first separator
    Dim lPosition As Long
    lPosition = InStr(sName, ":")

    ' Take all the characters before the first separator
    Dim sFirstName As String
    sFirstName = Left(sName, lPosition - 1)

    GetFirstName = sFirstName
    
End Function

Watch Window Ranges

The Watch window can be incredibly useful when dealing with ranges. The Range object has a property called Address and this can be invaluable when debugging our code.

You may often have to deal with messy code and using Address can help us see what the Range is.

Here are some useful properties for a range when you are debugging

• The Range: Range(“A1”).Address
• The Worksheet name: Range(“A1”).Parent.Name
• The Workbook name: Range(“A1”).Parent.Parent.Name

Imagine you had to debug the following badly written code. It doesn’t specify the worksheet before the Range and Cells properties. It contains numbers rather than constants or variables.

Public Sub UsingRanges()

    Dim x As Long, y As Long
    x = 4
    y = 5
    
    Dim rg As Range
    Set rg = Range(Cells(x + 2, y + 5), Cells(x + 5, y + 7))

End Sub

We can use the Watch Window to get the Worksheet name, the Workbook name and the Range address.

1. Create a new workbook and call it Watches.xlsm.
2. Place values in the cells A1,A2,B1 and B2.
3. Create a new module in this workbook and add the code below.
4. Add a breakpoint to the End Sub line and run the code.
5. Use the Watch window to display the Range address, the worksheet name and the workbook name.

Sub WatchRanges()

    Dim rg As Range
    Set rg = Sheet1.Range("A1").CurrentRegion

End Sub

Debug.Print

Debug.Print is a very useful function that we can use in our code.

It simply prints values to the Immediate Window(Ctrl + G to view).

Here are some examples

Sub UseDebugPrint()

    Dim sName As String
    sName = "John Smith"
    
    ' Prints: John Smith
    Debug.Print sName

    Dim x As Long
    x = 10
    ' Prints: x is 10
    Debug.Print "x is " & x
    
    ' Prints: 11
    Debug.Print x + 1
    
    ' Prints A3
    Debug.Print Sheet1.Cells(3, 1).Address
    
End Sub

One useful way to use Debug.Print, is to test our output before writing it to a worksheet. When our application is running we can test our output before we write it to a worksheet or a file.

We can write the code for reading the data first. Then we can print the data to the Immediate Window to check it is ok.

Public Sub PrintRange()

    ' Get the range
    Dim rg As Range
    Set rg = Sheet1.Range("A1").CurrentRegion
   
    ' Print out the contents of the range
    Dim i As Long
    For i = 1 To rg.Rows.Count
        Debug.Print rg.Cells(i, 1).Value
    Next i
    
End Sub

Once we are happy that the data is ok we can move on to the next part of our application.

Another good use of Debug.Print is for testing the structure of our code. We create empty procedures which print their name to the Immediate Window. This allows us to create a simple structure for our application without having to write a lot of code first.

We can run the code and check the result in the Immediate Window(Ctrl + G)

Public Sub CreateReport()

    Debug.Print "Create Report Starting"
    
    ReadData
    
    RunBusinessRules
    
    WriteData

    Debug.Print "Create Report Ending"
    
End Sub

Public Sub ReadData()
    Debug.Print "ReadData"
End Sub

Public Sub RunBusinessRules()
    Debug.Print "RunBusinessRules"
End Sub

Public Sub WriteData()
    Debug.Print "WriteData"
End Sub

The output from this code is

These techniques are very useful when building a VBA application in a professional environment. I use them throughout the applications in The Excel VBA Handbook.

1. 1. Create a new module and add the code below.
   2. Place the values 5, 10, 15, 20 and 25 into the range A1 to A5 in Sheet1.
   3. Add the appropriate Debug.Print statements below the comments in the code.
   4. Open the Immediate Window(Ctrl + G) and delete any existing text.
   5. Run the code. You should have the following results in the Immediate Window.

Address is $A$1:$A$5
Number of rows is 5
Value is 5
Value is 10
Value is 15
Value is 20
Value is 25

Sub WatchRanges()

    Dim rg As Range
    Set rg = Sheet1.Range("A1").CurrentRegion
    
    ' Print: the Range Address

    
    ' Print: the number of rows in the range

    
    Dim i As Long
    For i = 1 To rg.Rows.Count
        ' Print: the value in first cell in each row

    Next
    
End Sub

Sub WatchRanges()

    Dim rg As Range
    Set rg = Sheet1.Range("A1").CurrentRegion
    
    ' Print: the Range Address
    Debug.Print "Address is " & rg.Address
    
    ' Print: the number of rows in the range
    Debug.Print "Number of rows is " & rg.Rows.Count
    
    Dim i As Long
    For i = 1 To rg.Rows.Count
        ' Print: the value in first cell in each row
        Debug.Print "Value is " & rg.Cells(i, 1).Value
    Next
    
End Sub

Debug.Assert and Loops

Debug.Assert [condition] is a very useful function that I have written a full article about.

Debug.Assert evaluates a given condition and if it evaluates to False then the code pauses on the line. It seems very simple but it can be a very powerful technique to use.

The code below will pause if the month number is invalid

Public Sub CreateReport(ByVal lMonth As Long)
    
    ' if the month is not between 1 and 12 then
    ' pause here
    Debug.Assert lMonth >= 1 And lMonth <= 12
    
    Dim sCustomer As String
    sCustomer = GetCustomerName()
    
    ' Check that customer name is not blank
    Debug.Assert sCustomer <> ""
        
End Sub

The purpose of the Assert statement is to check values that come from another part of the code. We normally check values that are passed back from a function or parameters that are passed to a procedure.

We don’t check values that come from outside of our code. For example, we don’t do this

Public Sub ReadFileWrong()

    Dim sFilename As String
    
    sFilename = Sheet1.Range("A1").Value
    
    Debug.Assert sFilename <> ""

End Sub

We should do it like this instead

Public Sub ReadFileCheck()

    Dim sFilename As String
    
    sFilename = Sheet1.Range("A1").Value
    
    If sFilename = "" Then
        ' Handle error
        MsgBox "There is no filename in cell A1."
        Exit Sub
    End If

End Sub

Using Debug.Assert is like a series of checkpoints within your code. It makes it difficult for errors to exist because it will detect them when your code is running.

1. Make sure there are values in cells A1 and A2.
2. Copy the code below into a new module.
3. Run the code. It will run through and display the message without stopping.
4. Delete the contents from all cells in Sheet1.
5. Run the code again. It will pause on the first Debug.Assert line.
6. Press F5 and the code will run to the end.
7. Place values in A1 and A2. Run the code again and it will not pause.
8. Change the line GetMonth = 1 to GetMonth = 13.
9. Run the code again. It will pause on the second Debug.Assert line.

Public Sub AssertionsAct()

    Dim rg As Range
    Set rg = GetRange
    Debug.Assert rg.Cells.Count > 1
    
    Dim lMonth As Long
    lMonth = GetMonth
    ' Check month is between 1 and 12
    Debug.Assert lMonth >= 1 And lMonth <= 12
    
    MsgBox "The code has completed"

End Sub

 
Function GetRange() As Range
    Set GetRange = Sheet1.Range("A1").CurrentRegion
End Function

 
Public Function GetMonth() As Long
    GetMonth = 1
End Function

1. 1. Copy the code below into a new module.
   2. Add a Debug.Assert to the GetPrice function to check the price is greater than zero.
   3. Run the code. It should pause when GetPrice receives zero as a parameter.
   4. View the Call Stack(Ctrl + L) when the code pauses.
   5. Click on the Test sub in the Call Stack window. You will see a green triangle next to the line with the invalid parameter.
   6. Run the code to the end.
   7. Comment out the line that you just added by placing an apostrophe ‘ at the beginning of the line.
   8. Add a second assert in GetPrice to check that the type is A or B.
   9. Run the code. It should pause when D is passed as a parameter.

Public Sub Test()

    Debug.Print GetPrice(19.99, "A")
    Debug.Print GetPrice(49.79, "B")
    Debug.Print GetPrice(0, "A")  ' Invalid currency
    Debug.Print GetPrice(12, "D")  ' Invalid type

End Sub

Public Function GetPrice(ByVal Price As Currency _
            , ByVal sType As String)

    If sType = "A" Then
        GetPrice = Price + (Price * 0.25)
    ElseIf sType = "B" Then
        GetPrice = Price + (Price * 0.1)
    End If

End Function

Tutorial Assignment

We’ve covered a lot of stuff in this tutorial. So let’s put it all together in the following assignment

I have created a simple workbook for this assignment.

You can download the assignment here: Tutorial 4 Assignment Workbook

The Assignment

In the workbook, there are two worksheets

1. Data – this worksheet contains a list of customer transactions.
2. Report – the report will be written to this worksheet.

We have an application which reads the customer transactions. It creates a report that displays the first name and amounts for customers whose name starts with the letters “E” or “G”.

The report should look like this:

Gary: 8029
Gage: 4468
Geraldine: 993
Glenna: 5015
Ezekiel: 4528
Graham: 8577
Eric: 7340
Ezekiel: 607
Gray: 8461
Glenna: 8613
Gage: 9702
Eaton: 4661
Gary: 6996

However, the application is not working correctly and it is our job to fix it!

The Instructions

1. Open the assignment workbook and go to the Visual Basic Editor.
2. There are 3 compile errors. Find these errors and fix them. After you complete this task, the code should compile without any errors.
3. Click on the “Create Report” button on the Data worksheet.
4. Check the Report worksheet. You will notice that this worksheet remains empty. You need to find why no data is been written to this worksheet.
5. Locate the problem by stepping through the code and using the Watch window to examine the values.
6. When you find the problem you should be able to fix it easily.
7. Run the application again and you will notice that it is only printing out names that start with “E”.
8. Find a name starting with “G” on the data worksheet and note the row of this cell.
9. Place a Debug.Assert line in the code that will pause the code when it reaches this name.
10. Examine the variables to see why names starting with “G” are not being recognised.
11. When you find the error you can go ahead and fix it.
12. The code should now run correctly and print out the correct data.

Public Sub CreateReport()

    ' Receive the collection
    Dim coll As Collection
    Set coll = ReadToCollection
    
    ' Print the collection
    PrintCollection coll

    shReport.Activate
    
End Sub

Private Function ReadToCollection() As Collection
    
    ' Create a collection
    Dim coll As New Collection
    
    shReport.Cells.ClearContents
    
    ' Get the Range
    Dim rg As Range
    Set rg = shData.Range("A1").CurrentRegion
    
    Dim lAmount As Long, sName As String
    Dim i As Long
    ' Read through the data
    For i = 2 To rg.Rows.Count
        lAmount = rg.Cells(i, 4).Value
        sName = rg.Cells(i, 1).Value
        ' If the name starts with E or G then add to collection
        If Left(sName, 1) = "E" Or Left(sName, 1) = "G" Then
            coll.Add sName & ": " & lAmount
        End If
    Next i
    
    ' Return the collection to CreateReport sub
    Set ReadToCollection = coll

End Function

Private Sub PrintCollection(coll As Collection)

    shReport.Cells.ClearContents
    Dim i As Long
    For i = 1 To coll.Count
        shReport.Cells(i, 1).Value = coll(i)
    Next i

End Sub

Tutorial Quiz

The following quiz is based on what we have covered this tutorial.

1. 1. What are the three types of errors in VBA?

1. 1. Which compile error does the following code produce?

Public Sub WriteToSheet()

    Dim lAmount As Currency
    
    lAmount = GetAmount()
    
End Sub

Public Function GetAmount(ByVal price As Currency) As Long

End Function

1. 1. Which Step option should you use to pause the code in the next line of the parent procedure?

1. 1. What is the difference between a line highlighted in yellow and a line highlighted in brown?

1. 1. How can we view the current procedure hierarchy when our code is paused?

1. 1. How is the Locals Window different from the Watch Window?

1. 1. If we have a Range variable called rg, how do we view the range address, worksheet name and workbook name in the Watch window?

1. 1. What is the different between Debug.Print and Debug.Assert

1. 1. What does the plus sign mean when it is to the left of an item in the Watch Window?

1. 1. If our code is paused, how do we get it to continue running?

Conclusion of Tutorial

Congratulations on finishing this tutorial. The debugging tools are vital to understand and if used properly can save you considerable time and effort when writing VBA code.

Tutorial 1Tutorial 2Tutorial 3Tutorial 4