{"id":54,"date":"2014-01-24T18:27:27","date_gmt":"2014-01-25T01:27:27","guid":{"rendered":"https:\/\/blogs.ubc.ca\/coetoolbox\/?page_id=54"},"modified":"2016-11-28T10:52:13","modified_gmt":"2016-11-28T17:52:13","slug":"debugging","status":"publish","type":"page","link":"https:\/\/blogs.ubc.ca\/coetoolbox\/vba\/debugging\/","title":{"rendered":"Debugging"},"content":{"rendered":"<p><a href=\"https:\/\/blogs.ubc.ca\/coetoolbox\/files\/2014\/01\/tech_support_cheat_sheet.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone  wp-image-1569\" src=\"https:\/\/blogs.ubc.ca\/coetoolbox\/files\/2014\/01\/tech_support_cheat_sheet-267x300.png\" alt=\"tech_support_cheat_sheet\" width=\"521\" height=\"586\" srcset=\"https:\/\/blogs.ubc.ca\/coetoolbox\/files\/2014\/01\/tech_support_cheat_sheet-267x300.png 267w, https:\/\/blogs.ubc.ca\/coetoolbox\/files\/2014\/01\/tech_support_cheat_sheet-624x702.png 624w, https:\/\/blogs.ubc.ca\/coetoolbox\/files\/2014\/01\/tech_support_cheat_sheet.png 732w\" sizes=\"auto, (max-width: 521px) 100vw, 521px\" \/><\/a><\/p>\n<p>Making that your program works correctly and with no <a href=\"https:\/\/en.wikipedia.org\/wiki\/Grace_Hopper#Anecdotes\">bugs <\/a>is always a challenge.\u00a0Some general advice about testing your code is as follows:<\/p>\n<ul>\n<li>The best approach to programming\u00a0is to code your program incrementally: break functions into parts and test one at a time before moving on to the next one. Finding errors one at a time is <strong>much<\/strong> easier than waiting until mistakes compound and get more confusing.<\/li>\n<li>Make sure you&#8217;ve initialized everything that needs to be initialized,\u00a0including clearing output from previous runs if necessary.<\/li>\n<li>Use lots of print statements. You can comment\u00a0them out once you&#8217;re sure that the process they give information on is working correctly.<\/li>\n<\/ul>\n<p>A handy technique for debugging is <a href=\"https:\/\/blog.codinghorror.com\/rubber-duck-problem-solving\/\">Rubber Duck Debugging<\/a>. In a nutshell, you get an inanimate <a href=\"https:\/\/blogs.ubc.ca\/coetoolbox\/files\/2014\/01\/rubber-duck.png\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-1566 alignleft\" src=\"https:\/\/blogs.ubc.ca\/coetoolbox\/files\/2014\/01\/rubber-duck-300x300.png\" alt=\"rubber-duck\" width=\"355\" height=\"355\" srcset=\"https:\/\/blogs.ubc.ca\/coetoolbox\/files\/2014\/01\/rubber-duck-300x300.png 300w, https:\/\/blogs.ubc.ca\/coetoolbox\/files\/2014\/01\/rubber-duck-150x150.png 150w, https:\/\/blogs.ubc.ca\/coetoolbox\/files\/2014\/01\/rubber-duck-624x622.png 624w, https:\/\/blogs.ubc.ca\/coetoolbox\/files\/2014\/01\/rubber-duck.png 700w\" sizes=\"auto, (max-width: 355px) 100vw, 355px\" \/><\/a>object (a rubber duck or a teddy bear or something) and explain to it, <strong>out loud, in words<\/strong>, as if it were a person, why your code should work. Read every line to it out loud. Usually when you do that you&#8217;ll realize that you&#8217;re making some incredibly obvious error (typos, not initializing values, not passing variables correctly). If your rubber duck can&#8217;t help you find the problem, go ahead and bring in a human for troubleshooting.<\/p>\n<p>Here are some specific VBA tools to make the\u00a0testing and debugging stage easier.<\/p>\n<h2>1. VBE tools<\/h2>\n<p><strong>Immediate Window<\/strong>: Executes whatever is entered immediately. To view the content of a variable enter <span style=\"color: #800080;\"><strong>? VariableName<\/strong><\/span>. You can change a variable&#8217;s value by entering <strong><span style=\"color: #800080;\">VariableName = NewValue<\/span><\/strong> and pressing ENTER. Use the View menu option in VBE to show these windows.<\/p>\n<p><strong>Watch Window<\/strong>: displays the value of the variables or expressions inserted. To insert a watch select the variable from your code, Right click and select <strong><span style=\"color: #800080;\">Add Watch\u2026<\/span><\/strong> The typical watch type is <span style=\"color: #800080;\">Break When Value Changes<\/span>, which pauses at the line after the variable is updated.<\/p>\n<p><strong>Stepping Through Code<\/strong>: You can step through the code one line at a time clicking <strong><span style=\"color: #800080;\">Step Into<\/span><\/strong> on the Debug menu (F8). You can query or change a variable&#8217;s value from the Immediate window while you are paused. If your code calls another procedure, you can execute the called procedure without stepping through it clicking Step Over.<\/p>\n<p><strong>Breakpoints<\/strong>: stop the program execution in a selected code line. Add a breakpoint by putting the cursor on the line of code in question and clicking in the left margin next to the line of code (or choosing <strong><span style=\"color: #800080;\">Toggle Breakpoint<\/span><\/strong> on the Debug menu). Execution will pause immediately before the line of code with the breakpoint. You can query or change a variable&#8217;s value from the Immediate window while you are paused.<\/p>\n<p><strong>Stop Command<\/strong>: insert <strong><span style=\"color: #800080;\">Stop<\/span><\/strong> into your VBA code to simply stop code execution and enter break mode on that line of code.<\/p>\n<p><strong>Debug Command: <\/strong>displays the value of whatever is entered immediately in the immediate window. You can use it within your VBA code or in the immediate window.<\/p>\n<p style=\"padding-left: 30px;\"><span style=\"color: #800080;\">Debug.print<\/span> x, y, z<br \/>\n<span style=\"color: #800080;\">Debug.print<\/span> Inputbox(\u201cInsert number\u201d)<\/p>\n<p>On <a title=\"Pearson Software Consulting\" href=\"http:\/\/www.cpearson.com\/excel\/DebuggingVBA.aspx\" target=\"_blank\">this webpage<\/a> you can find a more detailed explanation of these tools.<\/p>\n<h2>2. VBE Options<\/h2>\n<ul>\n<li><strong>Auto Syntax Check<\/strong>: Determines\u00a0\u00a0 whether\u00a0\u00a0 VB\u00a0\u00a0 should\u00a0\u00a0 automatically verify correct syntax after you enter a line of code<\/li>\n<li><strong>Require Variable Declaration<\/strong>: Determines whether explicit variable declarations are required in modules. Selecting this check box adds the statement &#8220;Option Explicit&#8221; to \u000bgeneral declarations in any new module.<\/li>\n<li><strong>Auto List Member<\/strong>: Displays a list that contains information that would logically complete the statement at the \u000bcurrent insertion point location.<\/li>\n<li><strong>Auto Quick Info<\/strong>: Displays information about functions and their parameters as you type.<\/li>\n<li><strong> Auto Data Tips<\/strong>: Displays the value of the variable that the pointer is positioned over. Available only in \u000bbreak mode.<\/li>\n<\/ul>\n<h2>3. Error Handling<\/h2>\n<p>Sometimes it is easier let errors happen and handle them. We use the On Error statement to catch errors in code.<\/p>\n<p><strong>On Error Resume Next<\/strong><\/p>\n<p>Ignores the error and resumes execution on the next line of code without fixing the error. The following example won&#8217;t crash but neither will fix the error.<\/p>\n<p style=\"padding-left: 30px;\">Sub Reciprocal()<br \/>\n<span style=\"color: #0000ff;\">On Error Resume Next:<\/span><br \/>\nN = Inputbox(&#8220;Insert number&#8221;)<br \/>\nMsgbox (\u201cReciprocal = &#8221; &amp; 1\/N)<br \/>\nEnd Sub<\/p>\n<p><strong>On Error Goto &lt;label&gt;:<\/strong><\/p>\n<p>Jumps execution to the labeled line, ignoring the code between. You can use <span style=\"color: #0000ff;\">Resume<\/span> in your error handling block to resume at the line of code that caused the error.<\/p>\n<p style=\"padding-left: 30px;\">Sub Reciprocal()<br \/>\n<span style=\"color: #0000ff;\">On Error Goto ErrorHandler:<\/span><br \/>\nN = Inputbox(\u201cInsert number\u201d)<br \/>\nMsgbox (\u201cReciprocal = &#8221; &amp; 1\/N)<br \/>\n<span style=\"color: #0000ff;\">Exit Sub<\/span><br \/>\nErrorHandler:<br \/>\nMsgbox (&#8220;Invalid value entered&#8221;)<br \/>\nEnd Sub<\/p>\n<p><strong>Resume Command<\/strong><\/p>\n<p>You can use Resume in your error handling block to resume at the line of code that caused the error.<\/p>\n<p style=\"padding-left: 30px;\">Sub Reciprocal()<br \/>\n<span style=\"color: #0000ff;\">On Error Goto ErrorHandler:<\/span><br \/>\nRec = 1 \/ Inputbox(&#8220;Insert number&#8221;)<br \/>\nMsgbox (\u201cReciprocal = &#8221; &amp; Rec)<br \/>\nExit Sub<br \/>\n<span style=\"color: #0000ff;\">ErrorHandler:<\/span><br \/>\nMsgbox (\u201cInvalid value entered\u201d)<br \/>\n<span style=\"color: #0000ff;\">Resume<\/span><br \/>\nEnd Sub<\/p>\n<p>\u00a0You can find a more detailed explanation about error handling in <a title=\"Pearson Software Consulting\" href=\"http:\/\/www.cpearson.com\/excel\/errorhandling.htm\" target=\"_blank\">this webpage<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Making that your program works correctly and with no bugs is always a challenge.\u00a0Some general advice about testing your code is as follows: The best approach to programming\u00a0is to code your program incrementally: break functions into parts and test one at a time before moving on to the next one. Finding errors one at a [&hellip;]<\/p>\n","protected":false},"author":22982,"featured_media":0,"parent":2,"menu_order":9,"comment_status":"open","ping_status":"closed","template":"page-templates\/full-width.php","meta":{"footnotes":""},"class_list":["post-54","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/54","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/users\/22982"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/comments?post=54"}],"version-history":[{"count":18,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/54\/revisions"}],"predecessor-version":[{"id":1573,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/54\/revisions\/1573"}],"up":[{"embeddable":true,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/2"}],"wp:attachment":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/media?parent=54"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}