<<Convert Excel Spreadsheets to Web Pages \| Trading Software That Operates Within Excel \| Convert Excel, Access & Other Databases \| Merge Excel Files>>

Excel Training VBA Lesson 17

Debugging Excel VBA Code

Information Helpful? Why Not Donate >> Excel Training-Video Series

<< PREVIOUS LESSON |NEXT LESSON>> | BACK TO EXCEL VBA LEVEL1 TRAINING INDEX

Workbook Download
This is a zipped Excel Workbook to go with this lesson.

De-bugging

This lesson we will concentrate on De-bugging code and Error Handling. You will no doubt find that debugging is something you will be doing a lot in the early stages of learning. A good programmer will never think that their finished project doesn't contain any errors, if you do, you won't bother placing in any code to handle errors and that is a BIG mistake. There are many features in Excel that can make De-bugging code a reasonably easy task. To avoid confusion and overload we will discuss in detail what I believe to be the best method and by far the first one is the best:

Prevention is better than cure

Now we have all heard the saying that "prevention is better than cure" and this really holds true with VBA. I believe that a lot of code errors can be prevented just by forming good habits early. One very simple habit is, when typing code in the VBE always use all lower case. This is so any Keywords that Excel recognizes will automatically have their first letter capitalised. This also holds true for typing Variable names or Control names, in fact it holds true for a lot of cases, so develop this habit early. Not only are bad habits hard to break, but so are good ones!

Another saying that holds true in VBA for Excel is "Never assume anything". So whenever writing your code try to think what could cause this to fail. Some of the most common causes are Worksheet names changing, Worksheets position changing, Worksheet protection turned on, encountering Text when you expect a Number or vice versa and NOT naming ranges. Accounting for these potential problems can mean more typing of code, but believe me the short term pain will far out-weighed by the long term gain. Let's look at each of these in turn and see how we can eliminate them.

Worksheet Names

By far the best way to overcome this is to use the sheet objects "CodeName". This can be seen in the Properties Window of the sheet or in the Project Explorer (it's the name NOT in the brackets). The CodeName does not change if the sheet Tab name changes or if the sheet is moved to a different position in the Workbook. As an added bonus it is also very helpful when coding. If you type: Sheet1 and then place the period (dot) after it, Excel will list all it's Properties and any Objects on that sheet.

It is not possible to change a Sheets CodeName via VBA, it must be done in the Properties Window for the Sheet Object. To do this ensure the Project Explorer is visible (Ctrl+R) then display the Properties Window (F4). Select the Sheet Object in the Project Explorer then you will see it's CodeName in the Properties Window next to "(Name)". However, there should not really be any need to change this!

Worksheet Protection

It is vital when you develop a project that you Protect as much as possible, in particular the Worksheet Cells. But of course this means that if you try to perform a change to the sheet without UnProtecting, your code will fail. So you can start your procedure with the line:

Sheet1.Unprotect Password:="secret"

and end with..

Sheet1.Protect Password:="secret"

This will work just fine. Or you can use a more generic approach and create 2 Procedures within a normal Module like:

Option Explicit
Dim wWsht As Worksheet
Sub UnProtectAllSheets()
    For Each wWsht In ThisWorkbook.Worksheets
        wWsht.Unprotect Password:="secret"
    Next wWsht
End Sub

Sub ProtectAllSheets()
    For Each wWsht In ThisWorkbook.Worksheets
        wWsht.Protect Password:="secret"
    Next wWsht
End Sub

Then you can start your Procedures with the line:

Application.Run "UnProtectAllSheets"

and end with

Application.Run "ProtectAllSheets"

Just be aware with both methods, that if the code fails for or any reason and does not reach theProtect statement, your Worksheets will be Unprotected. I will show you how we can overcome this later on.

There is another method that can be used to protect your Worksheets from being changed by a user, but still allow any changes via VBA. This is done by setting the Protects last argument (UserInterfaceOnly) to True. The syntax for Sheet protection is:

From Excel Help

<SheetObject>.Protect(Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly)

Remarks
If you apply the Protect method with the UserInterfaceOnly argument set to True to a worksheet and then save the workbook, the entire worksheet (not just the interface) will be fully protected when you reopen the workbook. To unprotect the worksheet but re-enable user interface protection after the workbook is opened, you must again apply the Protect method with UserInterfaceOnly set to True.

End Of Excel Help

However, if you do opt for this method it maybe at your own peril! I have used this method in the past and then used the FillAcrossSheets Method which left the Worksheets fully unprotected! My advise is to play it safe and give it a miss.

Encountering Text when you expect a Number or vice versa

One of the downsides with declaring all Variables is they will be expecting what you have dimensioned them for. So the code:

iResult=Sheet1.Range("A1").Value

will cause a "Type mismatch" error If A1 contains Text and not an Integer. So unless we can be certain range A1 contains a Number we should first check like this:

If IsNumeric(Sheet1.Range("A1").Value) Then
IResult=Sheet1.Range("A1").Value
End If

The other side of the coin would be encountering a Number when we expect Text. Fortunately this won't cause an error, as Excel will simply make the Number a Text number, i.e; SResult=Sheet1.Range("A1").Value when range A1 contains the Number 100 will return "100" as text. But try and do any calculations with it and we will receive an error. So if you will need to use it in calculations use one of the Conversion Functions.

CInt(SResult)

There are many of these in Excel and are well documented. Push F1 and type: Conversion Functions

Not Naming Ranges

This is very important in VBA coding as the code will NOT automatically follow the address of a cell should it be moved. If you place a formula in cell A1 that refers to cell B1 and then Cut and Paste cell B1 to cell D1, the formula will automatically change it's reference to cell D1. Not so in VBA! So for this reason it is very important to name ranges and then our code will references the correct range.

You may be thinking "What has any of the above got to do with De-bugging ?" The answer is simply, it will prevent a lot of unnecessary De-bugging. Now it's almost inevitable that you will have to do some De-bugging while learning, but follow the above rules and it wont be very hard.

Compile Regularly

Perhaps one of the easiest and most overlooked method is the use of "Compile VBA project" option found under "Debug" on the main menu within the VBE. The beauty of this feature is you can pick up a lot of (but not all) errors without even running your code. If you can imagine you have written some code that goes though and makes hundreds of changes to a spreadsheet. You decide to Run your code to see if all is OK and on the second last line you code causes a Run-time error.

Run-time error: An error that occurs when code is running. A run-time error results when a statement attempts an invalid operation.

You now have to MANUALLY undo (undo changes will not work) all your changes so you can test your code again. You may be tempted to just close the Workbook without saving, but be very careful you are not going to loose changes you DO want saved.

Each time you finish (or what you think is finished) writing VBA code within a Procedure or Sub click the "Compile VBA project". Notice it mentions the word "project" not Sub or Procedure. Doing this will immediately pick up any errors in our code and take you straight there and display a message box telling you the problem. Now while this eliminates any errors directly related to your code, it wont pick up any errors directly related to Cells, Worksheets etc. What I mean by this is you could have the line:

Sheets("ThisYear").Cells(1,1).Value=2001

and even if there was no sheet in you Workbook called "ThisYear" Compiling will not pick it up. Try making the same mistake using the CodeName and you will know without even having to Compile.

The Dreaded Run time error

The very last one is the handling and fixing of Run time errors. As I said above, it's almost inevitable that you will have to do some De-bugging, but the job should not be a problem if you have followed all the above rules. One other thing which I have not mentioned above is the use of Comment text within code. Be liberal when commenting code as you will be thankful you did when it comes time to De-bug. One important thing to keep in mind though while writing your comments is, DON'T write too much on what the code does but rather why it does it. You should be able to see the "what" from reading your code.

The Local Window

This is one of my preferred methods of De-bugging and it is very rare that I need to go any further into De-bugging should I even get this far. The Locals Window can be found under "View" on the menu bar. The reason I prefer this is that, it will automatically displays all Declared Variables in the current Procedure along with their values. But you must be in Break mode:

Break mode: Temporary suspension of program execution in the development environment. In break mode, you can examine, debug, reset, step through, or continue program execution. You enter break mode when you:

Encounter a breakpoint during program execution.
Press CTRL+BREAK during program execution.
Encounter a Stop statement or untapped run-time error during program execution.
Add a Break When True watch expression. Execution stops when the value of the watch changes and evaluates to True.
Add a Break When Changed watch expression. Execution stops when the value of the watch changes.

The easiest way to do this is to insert a Breakpoint at the start of your code. You can do this by a single left mouse click on the Margin indicator bar (left of the code window) or by pushing F9 . Excel will place a large dot on it. Now when you run your code it will stop at the Breakpoint and all variables in the procedure will be displayed for you in the Local Window. You can now Step through your code 1 line at the time by pushing F8. You can also toggle between the VBE and the Excel interface while your code is in Breakmode by pushing Alt+F11 or selecting your workbook from the taskbar. Now between these 2 steps you should be able to track down exactly where the problem lies. If you do find a problem you can change the code while in Breakmode, you can change it there and then if you wish and see immediately what effect the change has made. You can also see the value of all your Variables while in Breakmode by waving your mouse pointer over any highlighted code.

I have followed the above methods now for many years and I can honestly say that I rarely need to get as far as using any one of Excels De-bug windows.

Error Handling

Now we have covered the basics of De-bugging code let's move on to using some sort of Error handling within our code. An "Error handler" is pretty much as the name suggests, it handles any errors the may arise. Now Error handling in Excel can become very complicated and is a very large area, but in the interest of keeping things simple we will look at what I believe to be the least you need to know. When you have written your code you will, as described above, try to ensure that no problems will arise. But unfortunately it is virtually impossible to be 100% sure nothing will go wrong. So for this reason we must take a pessimistic point of view and place in some code to allow for errors. The last thing the end user wants to see is a Run time error, it will probably mean nothing to the user and only make him/her lose faith in your ability and the programs. When you have written a Procedure and are satisfied it will do as it is supposed to, it's time to get very negative and look through it with a pessimistic point of view. This means we must expect the worst!

Before we move into how we can account for these potential errors I will go through what I believe to be the most effective preventers of errors.

On Error GoTo <line>
Where "<line>" is any line label in the same procedure as the
"On Error GoTo" Statement.

a line label is described as:

line label:Used to identify a single line of code. A line label can be any combination of characters that starts with a letter and ends with a colon (:). Line labels are not case sensitive and must begin in the first column.

**Do not fall into the trap of using this method a lot as it can make code very hard to follow and De-bug. You end up with what is know as "spaghetti code".**

On Error Resume Next This is one of the most popular methods of handling any potential errors. It simply tells Excel to keep going whenever a Run time error is encountered and go to the next line of code. Be very careful when using this one as it can hide any Run time errors that you should know about. I generally only use this to allow some code to check another line of code. As shown in the next example

On Error GoTo 0 This will turn off the "On Error Resume Next" and allow any Run time errors.

To put to use the "On Error Resume Next" and the "On Error GoTo 0" you could use it in this context:

Sub IsWorkbookOpen()
Dim wBook As Workbook

On Error Resume Next
    Set wBook = Workbooks("Book2")
        If wBook Is Nothing Then
            MsgBox "Book2 is not open!", vbCritical
        Else
            MsgBox "Book2 is open!", vbInformation
        End If
On Error GoTo 0
End Sub

This will allow our code to continue to our If Statement where we can now check to find out whether a Workbook called "Book2" is open on the users PC. Remembering that the default for an Object variable is Nothing. Without the "On Error Resume Next" we would encounter a "Subscript out of range" error and our code would be stopped dead. Try to only use this in this context and you shouldn't have any problems.

Now, I mentioned earlier that we could use some code like:

Application.Run "ProtectAllSheets"

Which would re-protect all our Worksheets within the Workbook once our code had run. But I as I mentioned, if for any reason our code encounters a Run time error that we have not thought of, our entire Workbook would have all it's Worksheets unprotected. This is not something we want! So this is where we could use the "On Error GoTo <line>". So we would have the two procedures below in a normal Module:

Dim wWsht As Worksheet
Sub UnProtectAllSheets()
    For Each wWsht In ThisWorkbook.Worksheets
        wWsht.Unprotect Password:="secret"
    Next wWsht
End Sub

Sub ProtectAllSheets()
    For Each wWsht In ThisWorkbook.Worksheets
        wWsht.Protect Password:="secret"
    Next wWsht
End Sub

So our first line of code in the Procedure that called the two above procedures, would be:

Application.Run "UnProtectAllSheets"

and

Application.Run "ProtectAllSheets" as one of our last line

So we could now use some code like this:

Sub DoToAllSheets()
Dim wWsht As Worksheet

On Error GoTo ReProtect
    Application.Run "UnProtectAllSheets"
        For Each wWsht In ActiveWorkbook.Worksheets
            Range("A1") = 10
        Next wWsht
ReProtect:
Application.Run "ProtectAllSheets"
End Sub

So if for any reason our code encounters a Run time error, all Worksheets would still be protected.

Exit Sub

There is one other means of trapping errors and that is to have your code do what is know as Exit your procedure cleanly and throw up a message box. I use this method only as my last "port of call". The Exit Sub used in combination with the "On Error GoTo <line>" can allow us to place within Procedures we choose a generic type of Error Handler. I have included in this lesson a working example of this along with some other related examples.