MS-Access、VBA 和错误处理

MS-Access, VBA and error handling

提问by Philippe Grondier

This is more an observation than a real question: MS-Access (and VBA in general) is desperately missing a tool where error handling code can be generated automatically, and where the line number can be displayed when an error occurs. Did you find a solution? What is it? I just realized how many hundreds of hours I spared since I found the right answer to this basic problem a few years ago, and I'd like to see what are your ideas and solutions on this very important issue.

这与其说是一个真正的问题，不如说是一个观察：MS-Access（以及一般的 VBA）极度缺少一种工具，可以自动生成错误处理代码，并且可以在发生错误时显示行号。你找到解决办法了吗？它是什么？我才意识到自从几年前我找到了这个基本问题的正确答案以来，我节省了多少时间，我想看看你对这个非常重要的问题有什么想法和解决方案。

采纳答案by Philippe Grondier

My solution is the following:

我的解决方案如下：

1. install MZ-Tools, a very interesting add-on for VBA. No they did not pay me to write this. Version 3 was free, but since version 8.0, the add-in is commercially sold.
2. program a standard error handler code such as this one (see MZ-Tools menu/Options/Error handler):

1. 安装MZ-Tools，一个非常有趣的 VBA 插件。不，他们没有付钱给我写这篇文章。第 3 版是免费的，但从 8.0 版开始，该插件已商业销售。
2. 编写一个标准的错误处理程序代码，例如这个（参见 MZ-Tools 菜单/选项/错误处理程序）：

---

---

On Error GoTo {PROCEDURE_NAME}_Error
{PROCEDURE_BODY}
On Error GoTo 0
Exit {PROCEDURE_TYPE}

{PROCEDURE_NAME}_Error:
debug.print "#" & Err.Number, Err.description, "l#" & erl, "{PROCEDURE_NAME}", "{MODULE_NAME}"

---

---

This standard error code can be then automatically added to all of your procs and function by clicking on the corresponding button in the MZ-Tools menu. You'll notice that we refer here to a hidden and undocumented function in the VBA standard library, 'Erl', which stands for 'error line'. You got it! If you ask MZ-Tools to automatically number your lines of code, 'Erl' will then give you the number of the line where the error occured. You will have a complete description of the error in your immediate window, such as:

然后，通过单击 MZ-Tools 菜单中的相应按钮，可以将此标准错误代码自动添加到您的所有过程和函数中。您会注意到我们在这里引用了 VBA 标准库中的一个隐藏且未记录的函数，“Erl”，它代表“错误行”。你说对了！如果你让 MZ-Tools 自动给你的代码行编号，'Erl' 就会给你出现错误的行号。您将在即时窗口中获得完整的错误描述，例如：

#91, Object variable or With block variable not set, l# 30, addNewField, Utilities

Of course, once you realize the interest of the system, you can think of a more sophisticated error handler, that will not only display the data in the debug window but will also:

当然，一旦你意识到系统的兴趣，你可以想到一个更复杂的错误处理程序，它不仅会在调试窗口中显示数据，还会：

1. display it as a message on the screen
2. Automatically insert a line in an error log file with the description of the error or
3. if you are working with Access or if you are connected to a database, automatically add a record to a Tbl_Error table!

1. 将其作为消息显示在屏幕上
2. 自动在错误日志文件中插入一行包含错误描述或
3. 如果您正在使用 Access 或连接到数据库，请自动将记录添加到 Tbl_Error 表！

meaning that each error generated at the user level can be stored either in a file or a table, somewhere on the machine or the network. Are we talking about building an automated error reporting systemworking with VBA?

这意味着在用户级别生成的每个错误都可以存储在文件或表中，位于机器或网络的某处。我们是在谈论构建一个使用 VBA的自动错误报告系统吗？

回答by BIBD

What about using "Erl", it will display the last label before the error (e.g., 10, 20, or 30)?

使用“Erl”怎么样，它会显示错误前的最后一个标签（例如，10、20 或 30）？

Private Sub mySUB()
On Error GoTo Err_mySUB
10:
    Dim stDocName As String
    Dim stLinkCriteria As String
20:
    stDocName = "MyDoc"
30:
    DoCmd.openform stDocName, acFormDS, , stLinkCriteria    
Exit_mySUB:
    Exit Sub
Err_mySUB:
    MsgBox Err.Number & ": " & Err.Description & " (" & Erl & ")"
    Resume Exit_mySUB
End Sub

回答by DJ.

Well there are a couple of tools that will do what you ask MZ Toolsand FMS Inccome to mind.

好吧，有几种工具可以满足您的要求MZ Tools和FMS Inc想到的。

Basically they involve adding an:

基本上，它们涉及添加：

On Error GoTo ErrorHandler

to the top of each proc and at the end they put an:

到每个 proc 的顶部，最后他们放了一个：

ErrorHandler:
  Call MyErrorhandler Err.Number, Err.Description, Err.LineNumber

label with usually a call to a global error handler where you can display and log custom error messages

标签通常调用全局错误处理程序，您可以在其中显示和记录自定义错误消息

回答by RubberDuck

You can always roll your own tool like Chip Pearsondid. VBA can actually access it's own IDE via the Microsoft Visual Basic for Applications Extensibility 5.3 Library. I've written a few class modules that make it easier to work with myself. They can be found on Code Review SE.

你总是可以像Chip Pearson那样推出自己的工具。VBA 实际上可以通过Microsoft Visual Basic for Applications Extensibility 5.3 Library访问它自己的 IDE 。我已经编写了一些类模块，可以更轻松地与自己合作。它们可以在Code Review SE上找到。

I use it to insert On Error GoTo ErrHandlerstatements and the appropriate labels and constants related to my error handling schema. I also use it to sync up the constants with the actual procedure names (if the function names should happen to change).

我用它来插入On Error GoTo ErrHandler语句以及与我的错误处理模式相关的适当标签和常量。我还使用它来同步常量与实际过程名称（如果函数名称发生变化）。

回答by Vlado

There is no need to buy tools DJ mentioned. Here is my code for free:

没有必要购买DJ提到的工具。这是我的免费代码：

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
    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
                Debug.Print "Declaration: " & Component.CodeModule.Lines(FirstLine, 1), FirstLine
                Debug.Print "Closing Proc: " & Component.CodeModule.Lines(LastLine, 1), LastLine

                ' 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
                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
                    Component.CodeModule.InsertLines LastLines.Item(i), "ExitProc_:"
                    Component.CodeModule.InsertLines LastLines.Item(i) + 1, "    Exit " & ProcedureTypes.Item(i)
                    Component.CodeModule.InsertLines LastLines.Item(i) + 2, "ErrHandler_:"
                    Component.CodeModule.InsertLines LastLines.Item(i) + 3, "    Call LogError(Err, Me.Name, """ & ProcNames.Item(i) & """)"
                    Component.CodeModule.InsertLines LastLines.Item(i) + 4, "    Resume ExitProc_"
                    Component.CodeModule.InsertLines LastLines.Item(i) + 5, "    Resume ' use for debugging"

                    Component.CodeModule.InsertLines StartLines.Item(i) + 1, "    On Error GoTo ErrHandler_"
                End If
            Next i
        End With
End Sub

Put it in a module and call it from Immediate Window every time you add new function or sub to a form or module like this (Form1 is name of your form):

每次将新函数或子添加到这样的表单或模块时，将它放在一个模块中并从立即窗口调用它（Form1 是您的表单的名称）：

MyModule.InsertErrHandling "Form_Form1"

It will alter your ode in Form1 from this:

它将改变您在 Form1 中的颂歌：

Private Function CloseIt()
    DoCmd.Close acForm, Me.Name
End Function

to this:

对此：

Private Function CloseIt()
    On Error GoTo ErrHandler_
        DoCmd.Close acForm, Me.Name
ExitProc_:
Exit Function
ErrHandler_:
    Call LogError(Err, Me.Name, "CloseIt")
    Resume ExitProc_
    Resume ' use for debugging
End Function

Create now in a module a Sub which will display the error dialog and where you can add inserting the error to a text file or database:

现在在一个模块中创建一个 Sub ，它将显示错误对话框，您可以在其中添加将错误插入文本文件或数据库：

Public Sub LogError(ByVal objError As ErrObject, moduleName As String, Optional procName As String = "")
    On Error GoTo ErrHandler_
    Dim sql As String
    MsgBox "Error " & Err.Number & " Module " & moduleName & Switch(procName <> "", " in " & procName) & vbCrLf & " (" & Err.Description & ") ", vbCritical
Exit_:
    Exit Sub
ErrHandler_:
    MsgBox "Error in LogError procedure " & Err.Number & ", " & Err.Description
    Resume Exit_
    Resume ' use for debugging
End Sub

This code does not enter error handling if there is already "On Error" statement in a proc.

如果 proc 中已经存在“On Error”语句，则此代码不会进入错误处理。