Making that your program works correctly and with no bugs is always a challenge. Some general advice about testing your code is as follows:

• The best approach to programming is 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 much easier than waiting until mistakes compound and get more confusing.
• Make sure you’ve initialized everything that needs to be initialized, including clearing output from previous runs if necessary.
• Use lots of print statements. You can comment them out once you’re sure that the process they give information on is working correctly.

A handy technique for debugging is Rubber Duck Debugging. In a nutshell, you get an inanimate object (a rubber duck or a teddy bear or something) and explain to it, out loud, in words, as if it were a person, why your code should work. Read every line to it out loud. Usually when you do that you’ll realize that you’re making some incredibly obvious error (typos, not initializing values, not passing variables correctly). If your rubber duck can’t help you find the problem, go ahead and bring in a human for troubleshooting.

Here are some specific VBA tools to make the testing and debugging stage easier.

1. VBE tools

Immediate Window: Executes whatever is entered immediately. To view the content of a variable enter ? VariableName. You can change a variable’s value by entering VariableName = NewValue and pressing ENTER. Use the View menu option in VBE to show these windows.

Watch Window: displays the value of the variables or expressions inserted. To insert a watch select the variable from your code, Right click and select Add Watch… The typical watch type is Break When Value Changes, which pauses at the line after the variable is updated.

Stepping Through Code: You can step through the code one line at a time clicking Step Into on the Debug menu (F8). You can query or change a variable’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.

Breakpoints: 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 Toggle Breakpoint on the Debug menu). Execution will pause immediately before the line of code with the breakpoint. You can query or change a variable’s value from the Immediate window while you are paused.

Stop Command: insert Stop into your VBA code to simply stop code execution and enter break mode on that line of code.

Debug Command: 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.

Debug.print x, y, z
Debug.print Inputbox(“Insert number”)

On this webpage you can find a more detailed explanation of these tools.

2. VBE Options

• Auto Syntax Check: Determines   whether   VB   should   automatically verify correct syntax after you enter a line of code
• Require Variable Declaration: Determines whether explicit variable declarations are required in modules. Selecting this check box adds the statement “Option Explicit” to general declarations in any new module.
• Auto List Member: Displays a list that contains information that would logically complete the statement at the current insertion point location.
• Auto Quick Info: Displays information about functions and their parameters as you type.
• Auto Data Tips: Displays the value of the variable that the pointer is positioned over. Available only in break mode.

3. Error Handling

Sometimes it is easier let errors happen and handle them. We use the On Error statement to catch errors in code.

On Error Resume Next

Ignores the error and resumes execution on the next line of code without fixing the error. The following example won’t crash but neither will fix the error.

Sub Reciprocal()
On Error Resume Next:
N = Inputbox(“Insert number”)
Msgbox (“Reciprocal = ” & 1/N)
End Sub

On Error Goto <label>:

Jumps execution to the labeled line, ignoring the code between. You can use Resume in your error handling block to resume at the line of code that caused the error.

Sub Reciprocal()
On Error Goto ErrorHandler:
N = Inputbox(“Insert number”)
Msgbox (“Reciprocal = ” & 1/N)
Exit Sub
ErrorHandler:
Msgbox (“Invalid value entered”)
End Sub

Resume Command

You can use Resume in your error handling block to resume at the line of code that caused the error.

Sub Reciprocal()
On Error Goto ErrorHandler:
Rec = 1 / Inputbox(“Insert number”)
Msgbox (“Reciprocal = ” & Rec)
Exit Sub
ErrorHandler:
Msgbox (“Invalid value entered”)
Resume
End Sub

 You can find a more detailed explanation about error handling in this webpage.