VBA的程序终止、退出语句块、分支及错误处理，End, exit, on error go to in VBA

代码1： 程序终止及退出方法

 1 Option Explicit
 2  
 3 '一、END语句
 4  
 5    '作用：强制退出所有正在运行的程序。
 6  
 7 '二、Exit语句
 8  
 9    '退出指定的语句
10    
11    '1、Exit Sub
12      Sub e1()
13      Dim x As Integer
14         For x = 1 To 100
15           Cells(1, 1) = x
16           If x = 5 Then
17             Exit Sub
18           End If
19          Next x
20       Range("b1") = 100
21      End Sub
22     '2、Exit function
23      Function ff()
24      Dim x As Integer
25         For x = 1 To 100
26           If x = 5 Then
27             Exit Function
28           End If
29          Next x
30       ff = 100
31      End Function
32    
33    '3、Exit for
34      Sub e2()
35      
36      Dim x As Integer
37         For x = 1 To 100
38           Cells(1, 1) = x
39           If x = 5 Then
40             Exit For
41           End If
42          Next x
43          
44        Range("b1") = 100
45      End Sub
46    '4、Exit do
47      Sub e3()
48      Dim x As Integer
49        Do
50          x = x + 1
51           Cells(1, 1) = x
52           If x = 5 Then
53             Exit Do
54           End If
55        Loop Until x = 100
56        Range("b1") = 100
57      End Sub

代码2： 分支及跳转方法

 1 Option Explicit
 2  
 3 'Goto语句,跳转到指定的地方
 4  
 5 Sub t1()
 6   Dim x As Integer
 7   Dim sr
 8 100:
 9   sr = Application.InputBox("请输入数字", "输入提示")
10   If Len(sr) = 0 Or Len(sr) = 5 Then GoTo 100
11   
12 End Sub
13  
14 'gosub..return ,跳过去,再跳回来
15  
16 Sub t2()
17   Dim x As Integer
18   For x = 1 To 10
19      If Cells(x, 1) Mod 2 = 0 Then GoSub 100
20   Next x
21 Exit Sub
22 100:
23    Cells(x, 1) = "偶数"
24    Return          '跳到gosub 100 这一句
25 End Sub
26  
27 'on error resume next '遇到错误,跳过继续执行下一句
28  
29  Sub t3()
30   On Error Resume Next
31   Dim x As Integer
32   For x = 1 To 10
33     Cells(x, 3) = Cells(x, 2) * Cells(x, 1)
34   Next x
35  End Sub
36  
37 'on error goto  '出错时跳到指定的行数
38  
39   Sub t4()
40   On Error GoTo 100
41   Dim x As Integer
42   For x = 1 To 10
43     Cells(x, 3) = Cells(x, 2) * Cells(x, 1)
44   Next x
45    Exit Sub
46 100:
47    MsgBox "在第" & x & "行出错了"
48   End Sub
49  
50  'on error goto 0 '取消错误跳转
51  
52   Sub t5()
53   On Error Resume Next
54   Dim x As Integer
55   For x = 1 To 10
56     If x > 5 Then On Error GoTo 0
57     Cells(x, 3) = Cells(x, 2) * Cells(x, 1)
58   Next x
59    Exit Sub
60  
61   End Sub