How to debug Excel VBA code

The error message given by Excel VBA error is minimal and requires the full use of various tools for debugging.

1. Compilation errors
Common compilation errors include the following:
The wrong source code format, such as missing then after if: In the editor, the line turns red.
Incorrect syntax structures, such as if and end if do not correspond: The code will be given a compilation error before it is run.
Type mismatch: This type of error occurs when the parameter entered by the function differs from the type of the parameter when it is defined.
Variable undefined: A variable type that is not declared (when Option Explicit is used)

Suggestions:
Format normalization, strict indentation. The VBA plug-in smart indent is a good helper tool. This makes the source code more readable, allowing for faster detection of source code formatting and syntax result errors.
Tick the "Tools-options-compiler-require variable declaration", or manually add option Explicit to the front of each code module. In code editing, many errors are simply the result of a hand error, which causes the compiler to force a check on the variable declaration, which will cause errors to be found at compile time.

2. Error in operation or operation result
When the program compiles without problems, but the running error or the result of the operation is not as expected, it is necessary to use the debugging function of VBA. Here are a few important debugging features provided by VBA that allow programs to stop at certain locations for inspection:
F9: Set the program breakpoint and click on the left column of the code to have the same effect. Press F9 again to cancel the breakpoint.
F8: Single-step tracking when the calling child function is traced to the inside of the child function
Shift+f8: One-step tracking, but does not enter the inner part of the child function
Ctrl+shift+f8: Jumps out of the function being traced and returns directly to the previous layer function.
F5: Run the program until an error occurs, the program ends, or the program breakpoint
CTRL+F8: Run the program until an error occurs, the program ends, the program breakpoint, or the current cursor is in action
Debug.Print Var: Displays the value of Var in the Immediate window
Debug.Assert var: program stops automatically when Var==false

The above commands can also be obtained from menus and command bars.
During debugging, the values of each variable can be viewed in the following ways, and when the variable is not the same as expected, the cause of the program error is found, and the modification can be compared:

Immediate Window (shortcut key ctrl+g): The result value of Debut.print is displayed in this window, and the code is calculated and run at any time. Add "?" in front of the code to display the results of the run in the Immediate window.
Watch window: You can add variables and expressions to the Watch window to see the values of variables and expressions in real time. Allows you to drag variables and expressions from the Code window into the Insight window
Local window: The local window allows you to view the current local variable and the variable value of the global variable.
edit window: The variable's value can be displayed by hovering over the variable in the edit window.
add important code comments.
Note the parameter passing method of the function, which is passed by default, and the child function modifies the value of the variable.
Avoid using variable variables such as ActiveSheet, ActiveWorkbook, Sheet1, ThisWorkbook this absolute variable. Absolute variables are unaffected by external operations.

Source: http://www.elsyy.com/s/article/view-2473-11703

========================================================================

VBA Testing and error handling

1. Testing

Test the code and check that it is correct. To ensure that your code works properly, you must try to simulate the different environments in which your code runs and check its correctness.

Errors in the program are generally divided into the following categories:

(1) syntax errors (such as typing a keyword or punctuation spelling errors).

(2) Compile errors (such as when using the object's methods, the object does not support this method).

(3) Run-time errors (such as except 0, open or close documents that do not exist, close open documents, and so on).

(4) Logic errors (logical germanium errors are the most difficult type of error to find, they are caused by the logic used in the program.)

2. Debug

To ensure that the variables and expressions in your code run as expected, you can use the various debugging tools in VBA to track them. The VBE has the following debugging tools.

(1) interrupt mode

① Enter the interrupt mode method: Press the F8 key ("Step Over") at the beginning of the procedure. Use the Cut breakpoint (F9 key or click the current blank indicator bar). Use the Stop statement. When the process is running, press the Ctrl+break key. Use the Run-time Error MsgBox dialog box.

② Exit Break Mode method: Select Run sub/UserForm from the Run menu, or press the F5 key or click the Run Sub/UserForm button on the Debug toolbar. Choose the reset option from the Run menu, or click the Reset button on the Debug toolbar.

(2) Single Step execution process

① Step into the process: from the "Debug" menu, select the "process" option, or press the SHIFT+F8 key.

② jump out of the process (skip the rest of the process): Choose the Step out option from the Debug menu, or press CTRL 10 Shift 10 F8.

③ runs to the cursor (skips a set of statements): From the Debug menu, choose Run To Cursor or press the CTRL+F8 key.

④ set the next statement to the cursor statement (be wary of ignoring statements that do not execute intermediate): Choose Set Next Statement from the Debug menu or press CTRL 10 F9.

⑤ the "Show Next Statement" option indicates what the next statement will be executed. You can use this debug feature when you need to monitor many processes in the program window and lose traces.

(3) monitoring process

① the local window, which displays all variables and their values in the current procedure, also displays the properties of the currently loaded form and control. ): Select the Local window option from the View menu.

② The Call Stack dialog box (see All active procedure Calls): Select Call Stack from the View menu or press Ctrl+l.

③ "Immediate Window" (can query and set variable values, create or revoke objects, execute single-line commands). Choose Immediate Window from the View menu or press the Ctrl+g key.

④ "Watch Window" (is a debugging tool that you can use in break mode to change the values of variables and expressions to see how different values affect your code). From the View menu, select.

3. Prevention of Errors

(1) Comments.

(2) Indent Code: Tab key or Enable Too1s (Tools) menu select options (option) In the Editor tab of Auto Indent (auto indent).

(3) Modularity of the code.

(4) Explicitly declare variables: Use the Option Explicit statement, or enable the Require variable Declaration check box in the Options Editor tab of the Tools menu.

(5) Avoid using variant variants (you can only specify variant types when a variable may contain a null value).

(6) Turn on grammar check: Enable the Tools menu, select the Options Editor tab, automatic grammar detection.

(7) Beware of Dim Traps: Dim Str1, str2 as String ' does not declare a STR1 variable.

4. Error handling

Debugging can only find predictable errors, and when dealing with unpredictable and unavoidable errors, you must use error handling. You can make your program more robust by enabling error handling, capturing and prompting for error handling. You can make your application more stable and robust. If you have several procedures in your application, consider using a centralized error handler.

(1) catch error: On Error Goto lines (On Error Goto 0 disables the error handler in the current procedure) in this syntax, line specifies the row of code to which control jumps when a run-time error occurs.

(2) write error handler: Display error message to user, prompt remedy; Allow to continue or cancel operation. Use the Err object:

Property	Description
Number	Stores the numeric ID of the last error. This is the default property
Description	Store information about the error
Source	Contains the object name or application name where the error occurred
HelpFile	Contains the name of the Help file
HelpContextID	Contains the Help context ID corresponding to the error number
LastDLLError	The system error code that contains the last call to the DLL

The Raise method allows the user to customize error handling information and return error handling information back to the calling process: Err.Raise number[, source, description, HelpFile, HelpContext]

the Clear method clears all the property values of the Err object: Err.Clear (The Err.Clear method is called automatically when a statement such as on Error, Exit Sub, Exit Function, Exit property, Resume, and so on is called). )

(3) exit the error handler:

①0n Error Resume: You can use it to return control to the statement that caused the error when the fault handler has fixed the error. But note that if the error is still there, it will cause an infinite loop.

②0N Error Resume Next: Ignore the wrong statement and proceed to the next sentence.

③resume [Linelabel]: The statement that passes control to the point where the line label (which must be in the same procedure as the Resume statement).

④ can also use the Exit Sub or exit function or even the end statement to exit a procedure that has generated an error, but these statements should be placed before the error handler so that an error handler is not executed when no error is generated.

Source: http://blog.163.com/xing_aixin/blog/static/372355052008377252250/

How to debug Excel VBA code