vba 测试 MS Access 应用程序的最佳方式？

Best way to test a MS Access application?

提问by Renaud Bompuis

With the code, forms and data inside the same database I am wondering what are the best practices to design a suite of tests for a Microsoft Access application (say for Access 2007).

使用同一数据库中的代码、表单和数据，我想知道为 Microsoft Access 应用程序（例如 Access 2007）设计一套测试的最佳实践是什么。

One of the main issues with testing forms is that only a few controls have a hwndhandle and other controls only get one they have focus, which makes automation quite opaque since you cant get a list of controls on a form to act on.

测试表单的主要问题之一是只有少数控件有hwnd句柄，而其他控件只有它们有焦点的控件，这使得自动化非常不透明，因为您无法获得表单上的控件列表以进行操作。

Any experience to share?

有什么经验可以分享吗？

采纳答案by RubberDuck

1. Write Testable Code

1. 编写可测试的代码

First, stop writing business logic into your Form's code behind. That's not the place for it. It can't be properly tested there. In fact, you really shouldn't have to test your form itself at all. It should be a dead dumb simple view that responds to User Interaction and then delegates responsibility for responding to those actions to another class that istestable.

首先，停止将业务逻辑写入您的表单背后的代码中。那不是它的地方。在那里无法正确测试。事实上，您根本不需要测试表单本身。这应该是一个死哑巴简单的观点，即响应用户交互，然后代表为应对这些动作到另一个阶级的责任是可检验的。

How do you do that? Familiarizing yourself with the Model-View-Controller patternis a good start.

你是怎样做的？熟悉模型-视图-控制器模式是一个好的开始。

It can't be done perfectlyin VBA due to the fact that we get either events or interfaces, never both, but you can get pretty close. Consider this simple form that has a text box and a button.

由于我们要么得到事件要么得到接口，所以在 VBA 中无法完美地完成它，从来没有两者，但你可以非常接近。考虑这个具有文本框和按钮的简单表单。

In the form's code behind, we'll wrap the TextBox's value in a public property and re-raise any events we're interested in.

在表单后面的代码中，我们将 TextBox 的值包装在一个公共属性中，并重新引发我们感兴趣的任何事件。

Public Event OnSayHello()
Public Event AfterTextUpdate()

Public Property Let Text(value As String)
    Me.TextBox1.value = value
End Property

Public Property Get Text() As String
    Text = Me.TextBox1.value
End Property

Private Sub SayHello_Click()
    RaiseEvent OnSayHello
End Sub

Private Sub TextBox1_AfterUpdate()
    RaiseEvent AfterTextUpdate
End Sub

Now we need a model to work with. Here I've created a new class module named MyModel. Here lies the code we'll put under test. Note that it naturally shares a similar structure as our view.

现在我们需要一个模型来使用。在这里，我创建了一个名为MyModel. 这是我们将要测试的代码。请注意，它自然与我们的视图具有相似的结构。

Private mText As String
Public Property Let Text(value As String)
    mText = value
End Property

Public Property Get Text() As String
    Text = mText
End Property

Public Function Reversed() As String
    Dim result As String
    Dim length As Long

    length = Len(mText)

    Dim i As Long
    For i = 0 To length - 1
        result = result + Mid(mText, (length - i), 1)
    Next i

    Reversed = result
End Function

Public Sub SayHello()
    MsgBox Reversed()
End Sub

Finally, our controller wires it all together. The controller listens for form events and communicates changes to the model and triggers the model's routines.

最后，我们的控制器将它们连接在一起。控制器侦听表单事件并将更改传达给模型并触发模型的例程。

Private WithEvents view As Form_Form1
Private model As MyModel

Public Sub Run()
    Set model = New MyModel
    Set view = New Form_Form1
    view.Visible = True
End Sub

Private Sub view_AfterTextUpdate()
    model.Text = view.Text
End Sub

Private Sub view_OnSayHello()
    model.SayHello
    view.Text = model.Reversed()
End Sub

Now this code can be run from any other module. For the purposes of this example, I've used a standard module. I highly encourage you to build this yourself using the code I've provided and see it function.

现在可以从任何其他模块运行此代码。出于本示例的目的，我使用了一个标准模块。我强烈建议您使用我提供的代码自己构建它并查看它的功能。

Private controller As FormController

Public Sub Run()
    Set controller = New FormController
    controller.Run
End Sub

---

---

So, that's great and all but what does it have to do with testing?!Friend, it has everythingto do with testing. What we've done is make our code testable. In the example I've provided, there is no reason what-so-ever to even try to test the GUI. The only thing we really need to test is the model. That's where all of the real logic is.

所以，这很好，但它与测试有什么关系？！朋友，它具有的一切做测试。我们所做的是使我们的代码可测试。在我提供的示例中，甚至没有理由尝试测试 GUI。我们唯一真正需要测试的是model. 这就是所有真正的逻辑所在。

So, on to step two.

所以，进入第二步。

2. Choose a Unit Testing Framework

2. 选择单元测试框架

There aren't a lot of options here. Most frameworks require installing COM Add-ins, lots of boiler plate, weird syntax, writing tests as comments, etc. That's why I got involved in building one myself, so this part of my answer isn't impartial, but I'll try to give a fair summary of what's available.

这里没有很多选择。大多数框架需要安装 COM 加载项、大量样板、奇怪的语法、将测试编写为注释等。这就是我自己参与构建一个的原因，所以我的这部分答案并不公正，但我会尝试对可用的内容进行公正的总结。

1. AccUnit

   • Works only in Access.
   • Requires you to write tests as a strange hybrid of comments and code. (no intellisense for the comment part.
   • There isa graphical interface to help you write those strange looking tests though.
   • The project has not seen any updates since 2013.

2. VB Lite UnitI can't say I've personally used it. It's out there, but hasn't seen an update since 2005.

3. xlUnitxlUnit isn't awful, but it's not good either. It's clunky and there's lots of boiler plate code. It's the best of the worst, but it doesn't work in Access. So, that's out.

4. Build your own framework

   I've been there and done that. It's probably more than most people want to get into, but it is completely possible to build a Unit Testing framework in Native VBA code.

5. Rubberduck VBE Add-In's Unit Testing Framework
   Disclaimer: I'm one of the co-devs.

   I'm biased, but this is by far my favorite of the bunch.

   • Little to no boiler plate code.
   • Intellisense is available.
   • The project is active.
   • More documentation than most of these projects.
   • It works in most of the major office applications, not just Access.
   • It is, unfortunately, a COM Add-In, so it has to be installed onto your machine.

1. 计量单位

   • 仅适用于 Access。
   • 要求您将测试编写为注释和代码的奇怪混合体。（评论部分没有智能感知。
   • 这里是一个图形界面来帮你写，虽然那些奇怪的看着测试。
   • 该项目自 2013 年以来没有看到任何更新。

2. VB Lite Unit我不能说我个人使用过它。它就在那里，但自 2005 年以来一直没有更新。

3. xlUnitxlUnit 并不糟糕，但也不好。它很笨重，而且有很多样板代码。这是最好的，但它在 Access 中不起作用。所以，就这样了。

4. 构建自己的框架

   我去过那里并做到了。这可能比大多数人想要的要多，但完全有可能用原生 VBA 代码构建单元测试框架。

5. Rubberduck VBE 插件的单元测试框架
   免责声明：我是共同开发者之一。

   我有偏见，但这是迄今为止我最喜欢的一群。

   • 几乎没有样板代码。
   • 智能感知可用。
   • 该项目处于活动状态。
   • 比大多数这些项目更多的文档。
   • 它适用于大多数主要办公应用程序，而不仅仅是 Access。
   • 不幸的是，它是一个 COM 加载项，因此必须将其安装到您的计算机上。

3. Start writing tests

3. 开始编写测试

So, back to our code from section 1. The only code that we reallyneeded to test was the MyModel.Reversed()function. So, let's take a look at what that test could look like. (Example given uses Rubberduck, but it's a simple test and could translate into the framework of your choice.)

所以，回到第 1 节中的代码。我们真正需要测试的唯一代码是MyModel.Reversed()函数。那么，让我们来看看这个测试会是什么样子。（给出的示例使用 Rubberduck，但它是一个简单的测试，可以转换为您选择的框架。）

'@TestModule
Private Assert As New Rubberduck.AssertClass

'@TestMethod
Public Sub ReversedReversesCorrectly()

Arrange:
    Dim model As New MyModel
    Const original As String = "Hello"
    Const expected As String = "olleH"
    Dim actual As String

    model.Text = original

Act:
    actual = model.Reversed

Assert:
    Assert.AreEqual expected, actual

End Sub

Guidelines for Writing Good Tests

编写好的测试指南

1. Only test one thing at a time.
2. Good tests only fail when there is a bug introduced into the system or the requirements have changed.
3. Don't include external dependencies such as databases and file systems. These external dependencies can make tests fail for reasons outside of your control. Secondly, they slow your tests down. If your tests are slow, you won't run them.
4. Use test names that describe what the test is testing. Don't worry if it gets long. It's most important that it is descriptive.

1. 一次只测试一件事。
2. 好的测试只会在系统中引入错误或需求发生变化时失败。
3. 不要包含外部依赖项，例如数据库和文件系统。这些外部依赖项可能会导致测试因您无法控制的原因而失败。其次，它们会减慢您的测试速度。如果你的测试很慢，你就不会运行它们。
4. 使用描述测试内容的测试名称。如果时间长了也不要担心。最重要的是它是描述性的。

---

---

I know that answer was a little long, and late, but hopefully it helps some people get started in writing unit tests for their VBA code.

我知道这个答案有点长而且有点晚，但希望它可以帮助一些人开始为他们的 VBA 代码编写单元测试。

回答by Philippe Grondier

I appreciated knox's and david's answers. My answer will be somewhere between theirs: just make forms that do not need to be debugged!

我很欣赏诺克斯和大卫的回答。我的答案介于两者之间：只需制作不需要调试的表格即可！

I think that forms should be exclusively used as what they are basically, meaning graphic interface only, meaning here that they do not have to be debugged! The debugging job is then limited to your VBA modules and objects, which is a lot easier to handle.

我认为表单应该完全按照它们的本质使用，仅表示图形界面，这意味着它们不必调试！然后调试工作仅限于您的 VBA 模块和对象，这更容易处理。

There is of course a natural tendency to add VBA code to forms and/or controls, specially when Access offers you these great "after Update" and "on change" events, but I definitely advise you notto put any form or control specific code in the form's module. This makes further maintenance and upgrade very costy, where your code is split between VBA modules and forms/controls modules.

将 VBA 代码添加到窗体和/或控件中当然是一种自然的趋势，特别是当 Access 为您提供这些很棒的“更新后”和“更改时”事件时，但我绝对建议您不要添加任何窗体或控件特定代码在表单的模块中。这使得进一步的维护和升级非常昂贵，您的代码在 VBA 模块和表单/控件模块之间拆分。

This does not mean you cannot use anymore this AfterUpdateevent! Just put standard code in the event, like this:

这并不意味着您不能再使用此AfterUpdate事件！只需在事件中放入标准代码，如下所示：

Private Sub myControl_AfterUpdate()  
    CTLAfterUpdate myControl
    On Error Resume Next
    Eval ("CTLAfterUpdate_MyForm()")
    On Error GoTo 0  
End sub

Where:

在哪里：

• CTLAfterUpdateis a standard procedure run each time a control is updated in a form

• CTLAfterUpdateMyFormis a specific procedure run each time a control is updated on MyForm

• CTLAfterUpdate是每次在表单中更新控件时运行的标准过程

• CTLAfterUpdateMyForm是每次在 MyForm 上更新控件时运行的特定程序

I have then 2 modules. The first one is

我有2个模块。第一个是

• utilityFormEvents
  where I will have my CTLAfterUpdate generic event

• utilityFormEvents
  我将在哪里有我的 CTLAfterUpdate 通用事件

The second one is

第二个是

• MyAppFormEvents
  containing the specific code of all specific forms of the MyApp application and including the CTLAfterUpdateMyForm procedure. Of course, CTLAfterUpdateMyForm might not exist if there are no specific code to run. This is why we turn the "On error" to "resume next" ...

• MyAppFormEvents
  包含 MyApp 应用程序所有特定表单的特定代码并包括 CTLAfterUpdateMyForm 过程。当然，如果没有要运行的特定代码，CTLAfterUpdateMyForm 可能不存在。这就是为什么我们将“发生错误”改为“下一个继续”......

Choosing such a generic solution means a lot. It means you are reaching a high level of code normalization (meaning painless maintenance of code). And when you say that you do not have any form-specific code, it also means that form modules are fully standardized, and their production can be automated: just say which events you want to manage at the form/control level, and define your generic/specific procedures terminology.
Write your automation code, once for all.
It takes a few days of work but it give exciting results. I have been using this solution for the last 2 years and it is clearly the right one: my forms are fully and automatically created from scratch with a "Forms Table", linked to a "Controls Table".
I can then spend my time working on the specific procedures of the form, if any.

选择这样一个通用的解决方案意义重大。这意味着您正在达到高水平的代码规范化（意味着轻松维护代码）。当你说你没有任何特定于表单的代码时，也意味着表单模块是完全标准化的，它们的生产可以自动化：只需说出你想要在表单/控件级别管理哪些事件，并定义你的通用/特定程序术语。
一劳永逸地编写自动化代码。
这需要几天的工作，但它会产生令人兴奋的结果。在过去的 2 年里我一直在使用这个解决方案，它显然是正确的：我的表单是完全自动从头开始创建的，带有一个链接到“控件表”的“表单表”。
然后我可以花时间处理表格的具体程序（如果有的话）。

Code normalization, even with MS Access, is a long process. But it is really worth the pain!

代码规范化，即使使用 MS Access，也是一个漫长的过程。但这真的是值得的痛苦！

回答by Ray

Another advantage of Access being a COM applicationis that you can create an .NET application to run and test an Access application via Automation. The advantage of this is that then you can use a more powerful testing framework such as NUnitto write automated assert tests against an Access app.

Access 作为 COM 应用程序的另一个优点是您可以创建一个.NET 应用程序来通过自动化运行和测试 Access 应用程序。这样做的好处是，您可以使用更强大的测试框架（例如NUnit）针对 Access 应用程序编写自动断言测试。

Therefore, if you are proficient in either C# or VB.NET combined with something like NUnit then you can more easily create greater test coverage for your Access app.

因此，如果您精通 C# 或 VB.NET 并结合 NUnit 之类的东西，那么您可以更轻松地为您的 Access 应用程序创建更大的测试覆盖率。

回答by mwolfe02

I've taken a page out of Python's doctestconcept and implemented a DocTests procedure in Access VBA. This is obviously not a full-blown unit-testing solution. It's still relatively young, so I doubt I've worked out all the bugs, but I think it's mature enough to release into the wild.

我从Python 的 doctest概念中取出了一页，并在 Access VBA 中实现了一个 DocTests 过程。这显然不是一个成熟的单元测试解决方案。它仍然相对年轻，所以我怀疑我已经解决了所有的错误，但我认为它已经足够成熟，可以发布到野外。

Just copy the following code into a standard code module and press F5 inside the Sub to see it in action:

只需将以下代码复制到标准代码模块中，然后在 Sub 中按 F5 即可查看它的运行情况：

'>>> 1 + 1
'2
'>>> 3 - 1
'0
Sub DocTests()
Dim Comp As Object, i As Long, CM As Object
Dim Expr As String, ExpectedResult As Variant, TestsPassed As Long, TestsFailed As Long
Dim Evaluation As Variant
    For Each Comp In Application.VBE.ActiveVBProject.VBComponents
        Set CM = Comp.CodeModule
        For i = 1 To CM.CountOfLines
            If Left(Trim(CM.Lines(i, 1)), 4) = "'>>>" Then
                Expr = Trim(Mid(CM.Lines(i, 1), 5))
                On Error Resume Next
                Evaluation = Eval(Expr)
                If Err.Number = 2425 And Comp.Type <> 1 Then
                    'The expression you entered has a function name that ''  can't find.
                    'This is not surprising because we are not in a standard code module (Comp.Type <> 1).
                    'So we will just ignore it.
                    GoTo NextLine
                ElseIf Err.Number <> 0 Then
                    Debug.Print Err.Number, Err.Description, Expr
                    GoTo NextLine
                End If
                On Error GoTo 0
                ExpectedResult = Trim(Mid(CM.Lines(i + 1, 1), InStr(CM.Lines(i + 1, 1), "'") + 1))
                Select Case ExpectedResult
                Case "True": ExpectedResult = True
                Case "False": ExpectedResult = False
                Case "Null": ExpectedResult = Null
                End Select
                Select Case TypeName(Evaluation)
                Case "Long", "Integer", "Short", "Byte", "Single", "Double", "Decimal", "Currency"
                    ExpectedResult = Eval(ExpectedResult)
                Case "Date"
                    If IsDate(ExpectedResult) Then ExpectedResult = CDate(ExpectedResult)
                End Select
                If (Evaluation = ExpectedResult) Then
                    TestsPassed = TestsPassed + 1
                ElseIf (IsNull(Evaluation) And IsNull(ExpectedResult)) Then
                    TestsPassed = TestsPassed + 1
                Else
                    Debug.Print Comp.Name; ": "; Expr; " evaluates to: "; Evaluation; " Expected: "; ExpectedResult
                    TestsFailed = TestsFailed + 1
                End If
            End If
NextLine:
        Next i
    Next Comp
    Debug.Print "Tests passed: "; TestsPassed; " of "; TestsPassed + TestsFailed
End Sub

Copying, pasting, and running the above code from a module named Module1 yields:

从名为 Module1 的模块复制、粘贴和运行上述代码会产生：

Module: 3 - 1 evaluates to:  2  Expected:  0 
Tests passed:  1  of  2

A few quick notes:

一些快速说明：

• It has no dependencies (when used from within Access)
• It makes use of Evalwhich is a function in the Access.Application object model; this means you coulduse it outside of Access but it would require creating an Access.Application object and fully qualifying the Evalcalls
• There are some idiosyncrasies associated with Evalto be aware of
• It can only be used on functions that return a result that fits on a single line

• 它没有依赖项（在 Access 中使用时）
• 它利用Eval了Access.Application对象模型中的一个函数；这意味着您可以在 Access 之外使用它，但它需要创建一个 Access.Application 对象并完全限定Eval调用
• 有一些需要注意的特质Eval
• 它只能用于返回适合单行的结果的函数

Despite its limitations, I still think it provides quite a bit of bang for your buck.

尽管它有局限性，但我仍然认为它为您提供了相当多的收益。

Edit: Here is a simple function with "doctest rules" the function must satisfy.

编辑：这是一个简单的函数，该函数必须满足“doctest 规则”。

Public Function AddTwoValues(ByVal p1 As Variant, _
        ByVal p2 As Variant) As Variant
'>>> AddTwoValues(1,1)
'2
'>>> AddTwoValues(1,1) = 1
'False
'>>> AddTwoValues(1,Null)
'Null
'>>> IsError(AddTwoValues(1,"foo"))
'True

On Error GoTo ErrorHandler

    AddTwoValues = p1 + p2

ExitHere:
    On Error GoTo 0
    Exit Function

ErrorHandler:
    AddTwoValues = CVErr(Err.Number)
    GoTo ExitHere
End Function

回答by paulroho

Although that being a very old answer:

虽然这是一个非常古老的答案：

There is AccUnit, a specialized Unit-Test framework for Microsoft Access.

有AccUnit，为Microsoft Access一个专门的单元测试框架。

回答by Knox

I would design the application to have as much work as possible done in queries and in vba subroutines so that your testing could be made up of populating test databases, running sets of the production queries and vba against those databases and then looking at the output and comparing to make sure the output is good. This approach doesn't test the GUI obviously, so you could augment the testing with a series of test scripts (here I mean like a word document that says open form 1, and click control 1) that are manually executed.

我会将应用程序设计为在查询和 vba 子例程中完成尽可能多的工作，以便您的测试可以由填充测试数据库、针对这些数据库运行生产查询和 vba 集，然后查看输出和比较以确保输出良好。这种方法显然不会测试 GUI，因此您可以使用一系列手动执行的测试脚本（这里我的意思是说打开表单 1，然后单击控件 1）来增强测试。

It depends on the scope of the project as the level of automation necessary for the testing aspect.

这取决于项目的范围作为测试方面所需的自动化水平。

回答by Ray

If your interested in testing your Access application at a more granular level specifically the VBA code itself then VB Lite Unitis a great unit testing framework for that purpose.

如果您有兴趣在更细粒度的级别测试您的 Access 应用程序，特别是 VBA 代码本身，那么VB Lite Unit是用于此目的的出色单元测试框架。

回答by AndrewM

I find that there are relatively few opportunities for unit testing in my applications. Most of the code that I write interacts with table data or the filing system so is fundamentally hard to unit test. Early on, I tried an approach that may be similar to mocking (spoofing) where I created code that had an optional parameter. If the parameter was used, then the procedure would use the parameter instead of fetching data from the database. It is quite easy to set up a user defined type that has the same field types as a row of data and to pass that to a function. I now have a way of getting test data into the procedure that I want to test. Inside each procedure there was some code that swapped out the real data source for the test data source. This allowed me to use unit testing on a wider variety of function, using my own unit testing functions. Writing unit test is easy, it is just repetitive and boring. In the end, I gave up with unit tests and started using a different approach.

我发现在我的应用程序中进行单元测试的机会相对较少。我编写的大部分代码都与表数据或文件系统交互，因此从根本上很难进行单元测试。早些时候，我尝试了一种可能类似于模拟（欺骗）的方法，在这种方法中我创建了具有可选参数的代码。如果使用了该参数，则该过程将使用该参数而不是从数据库中获取数据。设置与一行数据具有相同字段类型的用户定义类型并将其传递给函数非常容易。我现在有一种方法可以将测试数据放入我想要测试的程序中。在每个过程中，都有一些代码将真实数据源替换为测试数据源。这让我可以在更广泛的功能上使用单元测试，使用我自己的单元测试功能。编写单元测试很容易，只是重复和无聊。最后，我放弃了单元测试并开始使用不同的方法。

I write in-house applications for myself mainly so I can afford wait till issues find me rather than having to have perfect code. If I do write applications for customers, generally the customer is not fully aware of how much software development costs so I need a low cost way of getting results. Writing unit tests is all about writing a test that pushes bad data at a procedure to see if the procedure can handle it appropriately. Unit tests also confirm that good data is handled appropriately. My current approach is based on writing input validation into every procedure within an application and raising a success flag when the code has completed successfully. Each calling procedure checks for the success flag before using the result. If an issue occurs, it is reported by way of an error message. Each function has a success flag, a return value, an error message, a comment and an origin. A user defined type (fr for function return) contains the data members. Any given function many populate only some of the data members in the user defined type. When a function is run, it usually returns success = true and a return value and sometimes a comment. If a function fails, it returns success = false and an error message. If a chain of functions fails, the error messages are daisy changed but the result is actually a lot more readable that a normal stack trace. The origins are also chained so I know where the issue occurred. The application rarely crashes and accurately reports any issues. The result is a hell of a lot better than standard error handling.

我主要为自己编写内部应用程序，这样我就可以等到问题找到我，而不必拥有完美的代码。如果我确实为客户编写应用程序，通常客户并不完全了解软件开发成本，因此我需要一种低成本的方式来获得结果。编写单元测试就是编写一个测试，将坏数据推送到过程中，以查看过程是否可以适当地处理它。单元测试还确认良好的数据得到了适当的处理。我目前的方法是将输入验证写入应用程序中的每个过程，并在代码成功完成时引发成功标志。每个调用过程在使用结果之前检查成功标志。如果出现问题，则会通过错误消息进行报告。每个函数都有一个成功标志，返回值、错误消息、注释和来源。用户定义的类型（函数返回的 fr）包含数据成员。任何给定的函数都只填充用户定义类型中的一些数据成员。当一个函数运行时，它通常返回success = true 和一个返回值，有时还有一个注释。如果函数失败，则返回成功 = false 和错误消息。如果函数链失败，错误消息会发生变化，但结果实际上比普通堆栈跟踪更具可读性。起源也是连锁的，所以我知道问题发生在哪里。该应用程序很少崩溃并准确报告任何问题。结果比标准错误处理要好得多。用户定义的类型（函数返回的 fr）包含数据成员。任何给定的函数都只填充用户定义类型中的一些数据成员。当一个函数运行时，它通常返回success = true 和一个返回值，有时还有一个注释。如果函数失败，则返回成功 = false 和错误消息。如果函数链失败，错误消息会发生变化，但结果实际上比普通堆栈跟踪更具可读性。起源也是连锁的，所以我知道问题发生在哪里。该应用程序很少崩溃并准确报告任何问题。结果比标准错误处理要好得多。用户定义的类型（函数返回的 fr）包含数据成员。任何给定的函数都只填充用户定义类型中的一些数据成员。当一个函数运行时，它通常返回success = true 和一个返回值，有时还有一个注释。如果函数失败，则返回成功 = false 和错误消息。如果函数链失败，错误消息会发生变化，但结果实际上比普通堆栈跟踪更具可读性。起源也是连锁的，所以我知道问题发生在哪里。该应用程序很少崩溃并准确报告任何问题。结果比标准错误处理要好得多。它通常返回success = true 和一个返回值，有时还返回一个注释。如果函数失败，则返回成功 = false 和错误消息。如果函数链失败，错误消息会发生变化，但结果实际上比普通堆栈跟踪更具可读性。起源也是连锁的，所以我知道问题发生在哪里。该应用程序很少崩溃并准确报告任何问题。结果比标准错误处理要好得多。它通常返回success = true 和一个返回值，有时还返回一个注释。如果函数失败，则返回成功 = false 和错误消息。如果函数链失败，错误消息会发生变化，但结果实际上比普通堆栈跟踪更具可读性。起源也是连锁的，所以我知道问题发生在哪里。该应用程序很少崩溃并准确报告任何问题。结果比标准错误处理要好得多。该应用程序很少崩溃并准确报告任何问题。结果比标准错误处理要好得多。该应用程序很少崩溃并准确报告任何问题。结果比标准错误处理要好得多。

Public Function GetOutputFolder(OutputFolder As eOutputFolder) As  FunctRet

        '///Returns a full path when provided with a target folder alias. e.g. 'temp' folder

            Dim fr As FunctRet

            Select Case OutputFolder
            Case 1
                fr.Rtn = "C:\Temp\"
                fr.Success = True
            Case 2
                fr.Rtn = TrailingSlash(Application.CurrentProject.path)
                fr.Success = True
            Case 3
                fr.EM = "Can't set custom paths – not yet implemented"
            Case Else
                fr.EM = "Unrecognised output destination requested"
            End Select

    exitproc:
        GetOutputFolder = fr

    End Function

Code explained. eOutputFolder is a user defined Enum as below

代码解释。eOutputFolder 是用户定义的枚举，如下所示

Public Enum eOutputFolder
    eDefaultDirectory = 1
    eAppPath = 2
    eCustomPath = 3
End Enum

I am using Enum for passing parameters to functions as this creates a limited set of known choices that a function can accept. Enums also provide intellisense when entering parameters into functions. I suppose they provide a rudimentary interface for a function.

我使用 Enum 将参数传递给函数，因为这会创建一组有限的函数可以接受的已知选择。在将参数输入到函数中时，枚举还提供智能感知。我想他们为一个函数提供了一个基本的接口。

'Type FunctRet is used as a generic means of reporting function returns
Public Type  FunctRet
    Success As Long     'Boolean flag for success, boolean not used to avoid nulls
    Rtn As Variant      'Return Value
    EM As String        'Error message
    Cmt As String       'Comments
    Origin As String    'Originating procedure/function
End Type

A user defined type such as a FunctRet also provides code completion which helps. Within the procedure, I usually store internal results to an anonymous internal variable (fr) before assigning the results to the return variable (GetOutputFolder). This makes renaming procedures very easy as only the top and bottom have be changed.

用户定义的类型（例如 FunctRet）也提供了有助于的代码完成。在过程中，我通常在将结果分配给返回变量 (GetOutputFolder) 之前将内部结果存储到匿名内部变量 (fr)。这使得重命名过程非常容易，因为只有顶部和底部发生了变化。

So in summary, I have developed a framework with ms-access that covers all operations that involve VBA. The testing is permanently written into the procedures, rather than a development time unit test. In practice, the code still runs very fast. I am very careful to optimise lower level functions that can be called ten thousand times a minute. Furthermore, I can use the code in production as it is being developed. If an error occurs, it is user friendly and the source and reason for the error are usually obvious. Errors are reported from the calling form, not from some module in the business layer, which is an important principal of application design. Furthermore, I don't have the burden of maintaining unit testing code, which is really important when I am evolving a design rather than coding a clearly conceptualised design.

所以总而言之，我开发了一个带有 ms-access 的框架，它涵盖了所有涉及 VBA 的操作。测试被永久写入程序，而不是开发时单元测试。在实践中，代码仍然运行得非常快。我非常小心地优化每分钟可以调用一万次的低级函数。此外，我可以在生产中使用正在开发的代码。如果发生错误，它是用户友好的，错误的来源和原因通常很明显。错误是从调用表单报告的，而不是从业务层的某个模块报告的，这是应用程序设计的一个重要原则。此外，我没有维护单元测试代码的负担，这在我发展设计而不是编写清晰概念化的设计时非常重要。

There are some potential issues. The testing is not automated and new bad code is only detected when the application is run. The code does not look like standard VBA code (it is usually shorter). Still, the approach has some advantages. It is far better that using an error handler just to log an error as the users will usually contact me and give me a meaningful error message. It can also handle procedures that work with external data. JavaScript reminds me of VBA, I wonder why JavaScript is the land of frameworks and VBA in ms-access is not.

有一些潜在的问题。测试不是自动化的，只有在应用程序运行时才会检测到新的错误代码。代码看起来不像标准的 VBA 代码（通常更短）。尽管如此，该方法仍有一些优点。使用错误处理程序只是为了记录错误要好得多，因为用户通常会联系我并给我一个有意义的错误消息。它还可以处理使用外部数据的过程。JavaScript 让我想起了 VBA，我想知道为什么 JavaScript 是框架之乡，而 ms-access 中的 VBA 不是。

A few days after writing this post, I found an article on The CodeProjectthat comes close to what I have written above. The article compares and contrasts exception handling and error handling. What I have suggested above is akin to exception handling.

写完这篇文章几天后，我发现了一篇关于 The CodeProject的文章，与我上面写的很接近。文章比较和对比了异常处理和错误处理。我上面的建议类似于异常处理。

回答by AndrewM

Access is a COM application. Use COM, not Windows API. to test things in Access.

Access 是一个 COM 应用程序。使用 COM，而不是 Windows API。在 Access 中测试事物。

The best Test environment for an Access Application is Access. All of your Forms/Reports/Tables/Code/Queries are available, there is a scripting language similar to MS Test (Ok, you probably don't remember MS Test), there is database environment for holding your test scripts and test results, and the skills you build here are transferable to your application.

Access 应用程序的最佳测试环境是 Access。你所有的表格/报告/表格/代码/查询都可用，有一种类似于 MS Test 的脚本语言（好吧，你可能不记得 MS Test），有数据库环境来保存你的测试脚本和测试结果，并且您在这里培养的技能可以转移到您的应用程序中。

回答by Steve Mallory

There are good suggestions here, but I'm surprised no one mentioned centralized error processing. You can get addins that allow for quick function/sub templating and for adding line numbers (I use MZ-tools). Then send all errors to a single function where you can log them. You can also then break on all errors by setting a single break point.

这里有很好的建议，但我很惊讶没有人提到集中错误处理。您可以获得允许快速函数/子模板和添加行号的插件（我使用 MZ 工具）。然后将所有错误发送到单个函数，您可以在其中记录它们。然后，您还可以通过设置单个断点来中断所有错误。