Sean Hendrix的代码一点也不差。我稍微改进了一下:
Public Function AddErrorCode(modName As String)
Dim VBComp As Object
Dim VarVBCLine As Long
Set VBComp = Application.VBE.ActiveVBProject.VBComponents(modName)
For VarVBCLine = 1 To VBComp.CodeModule.CountOfLines + 1000
If UCase(VBComp.CodeModule.Lines(VarVBCLine, 1)) Like UCase("*Function *") Then
If Not (VBComp.CodeModule.Lines(VarVBCLine + 1, 1) Like "On Error GoTo *") Then
VBComp.CodeModule.InsertLines VarVBCLine + 1, "On Error GoTo ErrHandler_"
VBComp.CodeModule.InsertLines VarVBCLine + 2, " Dim VarThisName as String"
VBComp.CodeModule.InsertLines VarVBCLine + 3, " VarThisName = """ & Trim(Mid(VBComp.CodeModule.Lines(VarVBCLine, 1), InStr(1, VBComp.CodeModule.Lines(VarVBCLine, 1), "Function") + Len("Function"), Len(VBComp.CodeModule.Lines(VarVBCLine, 1)))) & """"
VarVBCLine = VarVBCLine + 4
End If
End If
If UCase(VBComp.CodeModule.Lines(VarVBCLine, 1)) Like UCase("*End Function*") Then
If Not (VBComp.CodeModule.Lines(VarVBCLine - 1, 1) Like "*Resume '*") And Not (UCase(VBComp.CodeModule.Lines(VarVBCLine - 1, 1)) Like UCase("*Err.Raise*")) Then
VBComp.CodeModule.InsertLines VarVBCLine, "ExitProc_:"
VBComp.CodeModule.InsertLines VarVBCLine + 1, " Exit Function"
VBComp.CodeModule.InsertLines VarVBCLine + 2, "ErrHandler_:"
VBComp.CodeModule.InsertLines VarVBCLine + 3, " Call LogError(Err, Me.Name, VarThisName)"
VBComp.CodeModule.InsertLines VarVBCLine + 4, " Resume ExitProc_"
VBComp.CodeModule.InsertLines VarVBCLine + 5, " Resume ' use for debugging"
VarVBCLine = VarVBCLine + 6
End If
End If
If UCase(VBComp.CodeModule.Lines(VarVBCLine, 1)) Like UCase("*Private Sub *") Or UCase(VBComp.CodeModule.Lines(VarVBCLine, 1)) Like UCase("*Public Sub *") Then
If Not (VBComp.CodeModule.Lines(VarVBCLine + 1, 1) Like "On Error GoTo *") Then
VBComp.CodeModule.InsertLines VarVBCLine + 1, "On Error GoTo ErrHandler_"
VBComp.CodeModule.InsertLines VarVBCLine + 2, " Dim VarThisName as String"
VBComp.CodeModule.InsertLines VarVBCLine + 3, " VarThisName = """ & Trim(Mid(VBComp.CodeModule.Lines(VarVBCLine, 1), InStr(1, VBComp.CodeModule.Lines(VarVBCLine, 1), "Sub") + Len("Sub"), Len(VBComp.CodeModule.Lines(VarVBCLine, 1)))) & """"
VarVBCLine = VarVBCLine + 4
End If
End If
If UCase(VBComp.CodeModule.Lines(VarVBCLine, 1)) Like UCase("*End Sub*") Then
If Not (VBComp.CodeModule.Lines(VarVBCLine - 1, 1) Like "*Resume '*") And Not (UCase(VBComp.CodeModule.Lines(VarVBCLine - 1, 1)) Like UCase("*Err.Raise*")) Then
VBComp.CodeModule.InsertLines VarVBCLine, "ExitProc_:"
VBComp.CodeModule.InsertLines VarVBCLine + 1, " Exit Sub"
VBComp.CodeModule.InsertLines VarVBCLine + 2, "ErrHandler_:"
VBComp.CodeModule.InsertLines VarVBCLine + 3, " Call LogError(Err, Me.Name, VarThisName)"
VBComp.CodeModule.InsertLines VarVBCLine + 4, " Resume ExitProc_"
VBComp.CodeModule.InsertLines VarVBCLine + 5, " Resume ' use for debugging"
'VBComp.CodeModule.DeleteLines VarVBCLine + 5, 1
'VBComp.CodeModule.ReplaceLine VarVBCLine + 5, " Resume ' replaced"
VarVBCLine = VarVBCLine + 6
End If
End If
Next VarVBCLine
End Function
你可以将它放在一个独立的模块中,并像这样调用它:
最初的回答
AddErrorCode "Form_MyForm"
"最初的回答"在即时窗口中。它会将您的表单代码从这样的代码更改为:
Private Sub Command1_Click()
Call DoIt
End Sub
to this in all Procedures on of MyForm.
Private Sub Command1_Click()
On Error GoTo ErrHandler_
Dim VarThisNameAs String
VarThisName = "Command1_Click()"
Call DoIt
ExitProc_:
Exit Sub
ErrHandler_:
Call LogError(Err, Me.Name, VarThisName)
Resume ExitProc_
Resume ' use for debugging
End Sub
你可以重复运行相同的表单,它不会重复生成代码。
你需要创建一个公共子程序来捕获错误,并将代码写入文件或数据库进行日志记录。"最初的回答"
Public Sub LogError(ByVal objError As ErrObject, PasModuleName As String, Optional PasFunctionName As String = "")
On Error GoTo ErrHandler_
Dim sql As String
' insert the values into a file or DB here
MsgBox "Error " & Err.Number & Switch(PasFunctionName <> "", " in " & PasFunctionName) & vbCrLf & " (" & Err.Description & ") ", vbCritical, Application.VBE.ActiveVBProject.Name
Exit_:
Exit Sub
ErrHandler_:
MsgBox "Error in LogError function " & Err.Number
Resume Exit_
Resume ' use for debugging
End Sub
编辑:
这里是改进后的代码:
Public Sub InsertErrHandling(modName As String)
Dim Component As Object
Dim Name As String
Dim Kind As Long
Dim FirstLine As Long
Dim ProcLinesCount As Long
Dim Declaration As String
Dim ProcedureType As String
Dim Index As Long, i As Long, j As Long
Dim LastLine As Long
Dim StartLines As Collection, LastLines As Collection, ProcNames As Collection, ProcedureTypes As Collection
Dim gotoErr As Boolean
Kind = 0
Set StartLines = New Collection
Set LastLines = New Collection
Set ProcNames = New Collection
Set ProcedureTypes = New Collection
Set Component = Application.VBE.ActiveVBProject.VBComponents(modName)
With Component.CodeModule
' Remove empty lines on the end of the code
For i = .CountOfLines To 1 Step -1
If Component.CodeModule.Lines(i, 1) = "" Then
Component.CodeModule.DeleteLines i, 1
Else
Exit For
End If
Next i
Index = .CountOfDeclarationLines + 1
Do While Index < .CountOfLines
gotoErr = False
Name = .ProcOfLine(Index, Kind)
FirstLine = .ProcBodyLine(Name, Kind)
ProcLinesCount = .ProcCountLines(Name, Kind)
Declaration = Trim(.Lines(FirstLine, 1))
LastLine = FirstLine + ProcLinesCount - 2
If InStr(1, Declaration, "Function ", vbBinaryCompare) > 0 Then
ProcedureType = "Function"
Else
ProcedureType = "Sub"
End If
Debug.Print Component.Name & "." & Name, "First: " & FirstLine, "Lines:" & ProcLinesCount, "Last: " & LastLine, Declaration
' do not insert error handling if there is one already:
For i = FirstLine To LastLine Step 1
If Component.CodeModule.Lines(i, 1) Like "*On Error*" Then
gotoErr = True
Exit For
End If
Next i
If Not gotoErr Then
StartLines.add FirstLine
LastLines.add LastLine
ProcNames.add Name
ProcedureTypes.add ProcedureType
Else
Debug.Print Component.Name & "." & Name, "Existing Error handling"
End If
Index = FirstLine + ProcLinesCount + 1
Loop
For i = LastLines.Count To 1 Step -1
If Not (Component.CodeModule.Lines(StartLines.Item(i) + 1, 1) Like "*On Error GoTo *") Then
If (Component.CodeModule.Lines(LastLines.Item(i) - 1, 1)) Like "*End " & ProcedureTypes.Item(i) Then
j = LastLines.Item(i) - 1
Else
j = LastLines.Item(i)
End If
Component.CodeModule.InsertLines j, "ExitProc_:"
Component.CodeModule.InsertLines j + 1, " DoCmd.Hourglass False"
Component.CodeModule.InsertLines j + 2, " Exit " & ProcedureTypes.Item(i)
Component.CodeModule.InsertLines j + 3, "ErrHandler_:"
Component.CodeModule.InsertLines j + 4, " DoCmd.Hourglass False"
Component.CodeModule.InsertLines j + 5, " Call LogError(Err.Number, Err.Description, """ & modName & """, """ & ProcNames.Item(i) & """)"
Component.CodeModule.InsertLines j + 6, " Resume ExitProc_"
Component.CodeModule.InsertLines j + 7, " Resume ' use for debugging"
Component.CodeModule.InsertLines StartLines.Item(i) + 1, " On Error GoTo ErrHandler_"
Debug.Print Component.Name & "." & ProcNames.Item(i), "First: " & StartLines.Item(i), "Last: " & j, " Inserted"
End If
Next i
End With
End Sub