MS-Access，VBA和错误处理

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)拼命错过了一个可以自动生成错误处理代码的工具,以及发生错误时可以显示行号的工具。你找到了解决方案吗?它是什么?我刚刚意识到,自从几年前我找到了解决这个基本问题的正确答案后,我节省了几百个小时,我想看看你在这个非常重要的问题上有什么想法和解决方案。

4 个解决方案

#1

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

#2

My solution is the following:

我的解决方案如下:

install MZ-Tools, a very interesting add-on for VBA. No they did not pay me, anyway it is free.

安装MZ-Tools,这是一个非常有趣的VBA附加组件。不,他们没有付钱给我,无论如何它是免费的。

program a standard error handler code such as this one (see MZ tools menu/Options/Error handler):

编写一个标准的错误处理程序代码,例如这个(参见MZ工具菜单/选项/错误处理程序):

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 an undocumented value/property of VBA (2003 edition), '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(2003版)的'无证值/属性','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:

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

display it as a message on the screen

将其显示为屏幕上的消息

Automatically insert a line in an error log file with the description of the error or

自动在错误日志文件中插入一行,其中包含错误或的描述

if you are working with Access or if you are connected to a database, automatically add a record to a Tbl_Error table!

如果您正在使用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 system working with VBA?

意味着在用户级别生成的每个错误都可以存储在文件或表中,机器或网络上的某个位置。我们是否正在讨论构建与VBA一起使用的自动错误报告系统?

#3

Well there are a couple of tools that will do what you ask MZ Tools and FMS Inc come 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:

到每个过程的顶部,最后他们把:

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

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

#4

You can always roll your own tool like Chip Pearson did. 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 ErrHandler statements 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语句以及与我的错误处理模式相关的适当标签和常量。我还使用它来将常量与实际的过程名称同步(如果函数名称恰好发生变化)。

#1