[Convert] 001 -- On Error for Analysis of Common VBA commands (correct)

001 -- On Error in common VBA commands)
Everyone wants their programs to be smooth, but errors have always been a pain point in our hearts. We always jump out from time to influence our mood. Although the hop-out error prompt will interrupt the running of our program
Just like our painful sensory nerves, it is a protection. It can avoid incorrect results or even catastrophic consequences. We can further program design and improvement to avoid errors and interruptions to our processes.
.
In daily life, we know that one of the most important tasks before the procedure is anesthesia pain relief.
The program also has an anesthetic analgesic, which is On Error.
Resume Next. After this statement is used, no errors will be reported to interrupt the program running. However, it is as dangerous as the loss of pain in a creature, and the consequences of a procedure that remains under anesthesia are unpredictable. Visible On Error
Resume Next is not a panacea. Misuse may cause mistakes and may incur heavy costs. Using this statement can not only avoid errors, but also achieve unexpected results.
There are two common errors: code writing errors and non-sequential errors during program running. The first type of errors can be found and solved during program debugging. The most troublesome problem is the second type of errors.
When dealing with the second type of errors, in addition to carefully designing the program, we should also carefully consider possible errors. This is the fault tolerance of the program, making the program more robust.
Example 1:
In an Excel file
File
When deleting a worksheet, an error occurs if you do not determine whether the worksheet exists in advance. Using Regular judgment will waste a lot of code. The only error that can occur here is that the target is not stored (the subscript is out of bounds), and this is what we want to achieve. It is better to make mistakes.

Code:

Sub main ()
Call deltable ("temporary table ")
End sub
Sub deltable (sname as string)
On Error resume next
Worksheet
S (sname). Delete
On Error GoTo 0
End Sub

What is on error goto 0? It is the antidote to on error resume next. Is to wake up your VBA program. Please pay attention to the use of on
After error resume next, you must develop the habit of using on error goto 0 to solve it. Otherwise, your program will be hard to find errors.
Example 2:
In a user form, a new worksheet is created based on the worksheet entered by the user. An error may occur if a worksheet with the same name exists without prior judgment. You can use cyclic statements to determine whether a worksheet exists or use the on error resume next statement to determine whether an error exists.

Code:

Sub main ()
If OkExcelExist ("Sheet1", ThisWorkbook) Then
MsgBox "worksheet already exists !! "
End If
End Sub
Function OkExcelExist (sSheetName As String, w As Workbook) As Boolean
Dim ws As Worksheet
OkExcelExist = True
On Error Resume Next
Set ws = w. Worksheets (sSheetName)
On Error GoTo 0
If ws Is Nothing Then OkExcelExist = False
Set ws = Nothing
End Function

In this example, an error occurs only when the ssheetname table does not exist. Therefore, it can be used.
Example 3:
If the above two examples can be easily implemented through other methods, it will be difficult to implement this example using conventional code. Check the original post.

Reference:

[Help
] How to Implement input data
Excel Cells
Grid
Check the validity of the reference?

I need to input data in textbox of vba, and then use this data to specify the location of the excel cell.
, How to make the input data legal?
For example, if I enter data in textbox: "a2", I use range (textbox. text). value = "OK" to fill in data for cells a2 in excel.
"OK". How can I ensure that the input "A2" must represent the position of the cell, rather than other characters such as "KKKK" or "1234" that cannot represent the cell?

If this problem is solved in a conventional way, you need to write a lot of code and consider every detail comprehensively. But still cannot be guaranteed. Let's take a look at how to use

Code:


Private sub commandbutton#click ()
Dim STR as string
STR = trim (textbox1.text) 'value
If not okexcel (STR) then' is invalid, a prompt is displayed.
Msgbox STR & "invalid cell address", vbokonly + vbcritical, "smart excel"
Exit sub
'Exit the subprocess
End if
'Add other statements below
End sub
'------------------------------------
Function okexcel (STR as string) as Boolean
Dim R as range
On Error resume next
'If an error occurs, execute the next statement.
Set r = Range (str)
'Attaches a value to r. If an error occurs, the data in str is not a valid cell address.
OkExcel = (err. Number = 0)
'If err. Number is not 0, it indicates an error has occurred.
On Error GoTo 0
'Good habits, restore to normal, although automatic after the end of this process
Recovery
End FunctionJust a few lines solved this problem!
Learn this! ___________________________________________________________________________ The full text, the original URL: http://www.okexcel.com.cn/bbs/viewthread.php? Tid = 168