Excel Made Easy

Vba macro debugging in Excel

Debugging is a most common and most often used word in the world of programming and Excel VBA is no exception.

Debugging is a process of idenfying errors in a program so as to rectify it for proper execution of the program.

"Debugging" came from the word "bug" which usually indicates flaws or defects and the term "Debug" signifies, removal of bugs.

There are a number Debugging techniques such as following:

1.Interactive debugging

2.Control flow

3.Integration testing

4.Log file

5.Unit testing

The VBA code is debugged from Top to Bottom and is sort of, Control flow analysis.

The debugging is used to spot out the error.

In large programs, it�s the Degugging, that saves us from identifying the flaws with less effort.

Consider the following code:

Please note that there is an error in the below mentioned code which when run returns an error, which when not handled properly will result in crashing of this workbook.So this code is not meant to run for an amateur programmer.

  1. Sub range_demo()
  2. Dim sht As Worksheet
  3. Set sht = Sheets.Add
  4. sht.Name = "WS"
  5. sht.Activate
  6. MsgBox "Input value into range A1:A5"
  7. sht.Range("A1") = 4
  8. sht.Range("A2") = 2
  9. sht.Range("A3") = 7
  10. sht.Range("A4") = 1
  11. sht.Range("A5") = 5
  12. MsgBox "Sort range A1;A5 in A to Z"
  13. MsgBox "Ready?"
  14. sht.Range("A1:A5").Sort key1:=sht.Range("A1"), order1:=xlAscending, Header:=xlNo
  15. MsgBox "Now Sorting range A1;A5 in Z to A"
  16. MsgBox "Ready?"
  17. sht.Range("1:A5").Sort key1:=sht.Range("A1"), order1:=xlDescending, Header:=xlNo
  18. MsgBox "Get value from Cell A4"
  19. MsgBox "Value of A4 : " & sht.Range("A4").Value
  20. MsgBox "That's it!"
  21. sht.Activate
  22. sht.Delete
  23. End Sub

The above code when run will result in error:(Wrong Range in the highlighted text above) and displays a dialog box as shown below:

excel vba macro debugging

When Debug is clicked, it will highlight the line containing error as shown below:

excel vba macro debugging

 

You can find similar Excel Questions and Answer hereunder

1) I frequently use a Macro - is there a way to quickly access the Macro in the Excel Ribbon?

2) How can I execute an event at a predetermined time from my Macro?

3) I want to automatically run a procedure whenever I close my WorkBook so that it is in a known state - how can I achieve that?

4) How can I make my macro wait for 5 secs before executing the next command?

5) How can I identify the cells that influence a particular cell to help with debugging of my spreadSheet or understand a spreadSheet that I inherited?

6) I have a macro that takes a lot of time for execution - how can I keep the user informed that the macro is running?

7) How can I clear cell after activating a routine when there is a change in value of a cell?

8) How do I assign a macro to a shape using VBA?

9) I frequently use a Macro - is there a way to quickly access the Macro using a Keyboard Shortcut?

10) How can I prevent users from seeing / accessing my macro code?

 

Here the previous and next chapter

Excel Made Easy was founded in 2002. Excel Made Easy is a service to people looking to learn Microsoft Excel or any other spreadsheet SW on the market.

We endeavor to provide the best service and support and to respond quickly to your questions.

Excel Made Easy is a proud sponsor of Dismantle-It.com the site that shows you the belly of your daily objects, Poupounette.com the site of cars loving cat and PowerPointMadeEasy.com the world top reference for PowerPoint Presentations questions and Pet Cemetery Online a site dedicated to your loving Pets.