Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: Debugging code

  1. #1
    Star Lounger
    Join Date
    May 2013
    Posts
    59
    Thanks
    34
    Thanked 6 Times in 6 Posts

    Debugging code

    For me, debugging code is a nightmare. Before I post in forums for help, I attempt to troubleshoot first to avoid wasting someone else's time. But it seems that I am doing it in an disorganized fashion. Does someone have a technique to simplify the process?

    TIA,
    Alexandra

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Hard to know how you do it and what to suggest, but you could start by reading this page: http://www.cpearson.com/Excel/DebuggingVBA.aspx
    Regards,
    Rory

    Microsoft MVP - Excel

  3. The Following User Says Thank You to rory For This Useful Post:

    Alouso (2014-10-07)

  4. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I presume you have tried single-stepping through the code ?

    This can be a pain when there are long loops, but its one of many techniques to work out what is happening as the code executes.

  5. The Following User Says Thank You to MartinM For This Useful Post:

    Alouso (2014-10-07)

  6. #4
    Star Lounger
    Join Date
    May 2013
    Posts
    59
    Thanks
    34
    Thanked 6 Times in 6 Posts
    @Rory: Thanks for the link. It describes methods for debugging that I did not know existed.

    @ Martin: Thanks for verifying that I should be going through line by line. I use breakpoints then hover over variables to see what their values are but I often get lost in the flow. Should line-by-line be the first step?

    Are there any other debugging tips or programs you gurus can suggest to create a step-by-step systematic approach?

    Alexandra

  7. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    It is difficult to answer your specific question with any precision because there are so many possible circumstances !

    Code which is written in clearly logical sections, each performing a defined function and labelled as such, does make subsequent debugging much easier, and losing the flow is less likely.

    "Getting lost in the flow" might also be an indicator that your code isn't sufficiently commented - it is all so obvious when you are writing it and all so obscure when you try and debug it ! I find that forcing myself to comment each and every line when I write it, saying what it is doing (or is meant to do !) is well worth the effort in the long run.

    Actually though, when my code stumbles on an error, or doesn't give the results I expected, my first step is usually to write some critical information (such as variables) out to a message box. I find that by working from near the beginning and on in small stages (logical chunks) I can usually spot the problem pretty quickly this way.

    Hope this helps.
    Last edited by MartinM; 2014-10-07 at 07:31.

  8. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    I always have the Locals window and Immediate Window at the bottom of the VBE window so I can keep an eye on variable values and run quick checks in the IW. I also use the Watch window occasionally. Setting watches, and Debug.Print and Debug.Assert statements are also very useful, as are commands like Step Over and Run to Cursor.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Alexandra,

    Debugging your code actually begins at design time not when it fails. When writing complex code, I force myself to sketch a quick flow chart that provides an overview of the flow. The flow chart then becomes my commented sections as I write the code. When completing each section, I then test up to that point to make sure the code is doing what I expect. It is better than writing the whole code and then when it fails you don't know where to look. When testing the new section, I launch the code from the beginning but I set a breakpoint at the start of the section I am testing. Pressing F8 while in break mode will move through one line at a time. If a line fails, the code will halt then reset prior to that line so you can make adjustments to it then press F8 again to see if you can progress past it. Like rory does, I also add debug. print statements and use the watch window. I don't use debug.asset at all since I always get confused as to the condition of True or False that pauses the code. And like you, I also hover over the variable to glimpse at their values. When naming variable, many like to use various conventions. To follow the flow, I find it most helpful to use varable names that are relevant and tend to speak in English. For example:

    "For Each cell in Rng" make so much more sense than "For Each zCtlry in zRngCtlrx" (I know I will catch flack for that!)

    Generally, these are also the steps I also take to troubleshoot code when a problem arises.

    Most of all I agree with Martin in that if you write code with a clear, logical, and direct approach to achieve a task, it will be much easier to understand when you need to revisit it.

    Here is an excellent source of VB editor Tips that may be useful: http://www.telecomhall.com/best-vbe-...tips-ever.aspx

    HTH,
    Maud

  10. The Following User Says Thank You to Maudibe For This Useful Post:

    Alouso (2014-10-08)

  11. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Alexandra,

    I agree with Maud.

    I would also add that you should set your VBE options to check the box to require variable definition. This step alone will catch a lot of errors (mistyping of variable names).
    vbe.JPG
    Of course, doing this will REQUIRE you to DIM (declare) all of your variables, the editor will yell at you if you use an undeclared variable. This is a great way to catch errors before they occur, e.g. using IntPct in one place and InterestPct in another can play all kinds of havoc with your code.

    Now for the FLACK Maud so rightly said he would catch! ROTFLOL.gif

    Using a leading character(s) on variable names is a time honored technique to prevent errors. This is because when getting into a longer set of code where your declarations aren't visible when reading code you can tell at a glance the type of data held by a variable.

    iDays = Days is an Integer value
    lRows = Rows is a Long value
    zCity = City is a string (zero terminated string in programmereeze)
    etc.

    There are whole documents written about naming conventions for variables. Here's a link to the Wiki on Hungarian Notation (one of the more famous). You don't have to follow one of the published ones for your own needs you only need to be consistent in whatever notation you use.

    Another good practice is to indent your code so the logical structure stands out at a glance.
    Code:
    Sub Test()
    
       Dim ...
       Dim ...
     
       Do 
         If ActiveCell.Value <> "" Then
           code line 1
           code line 2
         Else
           else code line 1
         End If
       Loop Until ActiveCell.Value = ""
    
    End Sub     'Test()
    All of the above advice is good and very useful. You'll find that debugging is more art than science and the more you do the better you'll get.

    HTH

    PS: I went back and read the whole article on HN and I found it interesting that in the Opinions section they posted more against than for. So I guess I should say IMHO it's the best way...but then again I'm an OLD RETIRED GEEK...OLD HABITS Bruce Willis!
    Last edited by RetiredGeek; 2014-10-07 at 23:12.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Alouso (2014-10-08)

  13. #9
    Star Lounger
    Join Date
    May 2013
    Posts
    59
    Thanks
    34
    Thanked 6 Times in 6 Posts
    There is a lot of good information here to digest. Thank you all a hundred times.

    Just one thing I don't understand. I would think that you guys, being such expert coders, wouldn't have had the need to have developed such fine debugging skills.

    Alex

  14. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    RG,

    You are the true Super-Ego of the VBA psyche!

  15. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maud,

    From Wikipedia:
    Super-ego

    The superego (German: Über-Ich)[26] reflects the internalization of cultural rules, mainly taught by parents applying their guidance and influence.[8] Freud developed his concept of the super-ego from an earlier combination of the ego ideal and the "special psychical agency which performs the task of seeing that narcissistic satisfaction from the ego ideal is ensured ... what we call our 'conscience'."[27] For him "the installation of the super-ego can be described as a successful instance of identification with the parental agency," while as development proceeds "the super-ego also takes on the influence of those who have stepped into the place of parents — educators, teachers, people chosen as ideal models."[28]
    You NAILED IT! ROTFLOL.gif
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  16. #12
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Ho ho Alexandra !

    I hope you don't mind a serious answer to your lovely humour ?

    There's a balance in everything.

    You could spend months writing perfect code - logical examination of every step, assumption check, peer review, publish and amend . . . . then, as you say, you wouldn't need any debugging skills at all.

    Or you could write crappy code and spend your months debugging it.

    Somewhere in between is an optimum place, and I think that what we are all saying - in our different ways - is where that optimum lies.

    It is not at the point of no debugging, neither is it at the point of no care in writing code because fine debugging skills will take care of everything.

    But it is much nearer the former than the latter.

    M

  17. The Following User Says Thank You to MartinM For This Useful Post:

    Alouso (2014-10-10)

  18. #13
    Star Lounger
    Join Date
    May 2013
    Posts
    59
    Thanks
    34
    Thanked 6 Times in 6 Posts
    A balance I have yet to find, Martin

  19. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi

    Following on from RG's post#8, in the vba Tools>Options click on the [General] tab and set the Error Trapping option to
    'Break in Class Module'.
    This should be the default setting in my opinion.
    It means that if you are using any Forms in Excel, and an error occurs with the Form, you will debug to the line that causes the problem rather than
    just getting a general unhelpful message that an error has occurred.

    Even if you don't use Forms, setting this option as your default would do no harm.

    zeddy

  20. The Following User Says Thank You to zeddy For This Useful Post:

    RetiredGeek (2014-10-13)

  21. #15
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Zeddy,

    All the years I've been cussing VBA for breaking at the Show command. Who knew...yeah you did.

    Thanks, this will save me loads of time going forward!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  22. The Following User Says Thank You to RetiredGeek For This Useful Post:

    zeddy (2014-10-24)

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •