VBA-Lesson 2. Debugger

A tool in the VBA environment for debugging program code. Being able to work with a debugger is vital for anyone writing in VBA. It is needed in order to check how your code works and to find and fix errors in it.

Open the Visual Basic Editor (Alt+F11). Create a module in it and write the following code:

 Sub LearningDebug()
     Dim A As Long, B As Long, C As Long, D As Long

     D = 0
     A = 10
     Debug.Print "A = " + Trim(Str(A))
     B = 15
     Debug.Print "B = " + Trim(Str(B))
     C = A + B
     Debug.Print "C = " + Trim(Str(C))
     C = Round(C / 5)
     Debug.Print "C is divided by 5: C = " + Trim(Str(C))
     C = Round(C / D) ' there will be an error here, since it is impossible to divide by zero (and D = 0)
End Sub

Press Ctrl+G and the Immediate window will appear below. In this window, during the operation of the macro, you can change the values of the variables, execute various code in VBA that was not written in the module before.

If you place the cursor inside the code of the module and press F5, it will be executed completely. If you press F8 - only one line of code will be executed. The next press of F8 will execute the next line of code, and so on. you can step through the entire code. Use the F9 button to create a breakpoint. If you then run the code with F5, the code will execute up to the specified line and then pause. It can then be continued using the F5 or F8 buttons described earlier.

If you look closely at the code, you will see that the variables A, B, C, D are assigned numbers. The Debug.Print "text" lines print the text we specified to the previously opened Immediate window to see what is happening with the variable. Str (A) converts a number to a text value. And Trim () removes spaces to the right and left from it. The Round (С) operator rounds the value to an integer according to the rules of arithmetic (therefore, we assign the result of the calculations to an integer variable of the Long type, which means it must be an integer). At the end of the code, we specifically created a false situation to practice using the debugger.

Stand inside the code and press F8 four times. The line that will be executed next will be marked in yellow. Hover over the different variables. At the same time, a pop-up window will appear in which you will see their value. Variables that have not yet been assigned values are equal to zero. Press F8 again. A line will appear in the Immediate window: A = 10 Now we can change the value of the A variable right away in the Immediate window (for example, let's change it to 8) . To do this, in a new line of the Immediate window, write: A = 8 and press Enter. Now hover over the variable A and you will see that its value is 8. to see the value without hovering, you can make it appear in the Immediate window. This is done like this, enter Immediate in an empty line of the window: ?A and press Enter. The ? sign in the Immediate window means the same as Debug.Print in the code. It's just easier and shorter to write that way. You will immediately see the number - the result of your request.

Move the cursor to the last line of code (C = Round (C / D)) and press F9. A breakpoint will appear. You can remove it with the same button, but we will not remove it yet. Press F5, the program will execute all lines and stop at the last one. Messages about assigning values to variables will appear in the Immediate window. We have reached the last line of code. It should throw an error because it contains division by zero. Press F8 and see for yourself. In the window that appears, click Debug. If you press End, the program will stop, and we want to bring it to the end.

The situation can be corrected by changing the value of the D variable. Now it is zero. In the Immediate window, enter the text in an empty line: D = 2 and press Enter. Now, with the last line of code, we divide not by zero, but by 2, thus avoiding the error. Press F5 and the program will complete its work without errors.

Try to modify the code and practice using the debugger. Believe me, it will make your life much easier.

Articles on the topic:

  • VBA-Lesson 1. What is VBA, basic concepts.
  • VBA-Lesson 3. Sheets Collection