如何对 Excel VBA 代码进行单元测试
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4547487/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to unit test Excel VBA code
提问by Dr Zimmerman
Does anyone have any experience with unit testing Excel VBA code? I want to introduce unit tests into some legacy Excel VBA code as painlessly as possible. One idea I had would be to use VSTO to call code from inside the Excel workbook. I would like to know if others have tried this for the purpose of unit testing the Excel code, as well as any other methods they may have used for unit testing Excel VBA.
有没有人有单元测试 Excel VBA 代码的经验？我想尽可能轻松地将单元测试引入到一些遗留的 Excel VBA 代码中。我的一个想法是使用 VSTO 从 Excel 工作簿内部调用代码。我想知道其他人是否为了单元测试 Excel 代码以及他们可能用于单元测试 Excel VBA 的任何其他方法而尝试过这个。
I would appreciate some pointers as to any available frameworks and/or tips on unit testing Excel VBA code as well.
我希望得到一些关于任何可用框架和/或单元测试 Excel VBA 代码提示的提示。
回答by Mathieu Guindon
Disclaimer: I own Rubberduck's GitHub repository, and I'm one of the devs involved in the project.
免责声明：我拥有 Rubberduck 的 GitHub 存储库，并且我是参与该项目的开发人员之一。
Rubberduckis under active development. It's muchmore than a unit testing tool for VBA though, but it works pretty well and lets you write VBA unit tests pretty much without any boilerplate:
Rubberduck正在积极开发中。这是很多多的单元测试工具，VBA，虽然，但它工作得很好，让你编写VBA单元测试几乎没有任何样板：
'@TestModule
Private Assert As New Rubberduck.AssertClass
'@TestMethod
Public Sub TestMethod1()
Assert.Inconclusive "Test method is not written yet."
End Sub
'@TestMethod
Public Sub AnotherTestMethod()
Assert.IsTrue False, "Something's wrong?"
End Sub
And then you get to navigate and run your test methods in a docked toolwindow that also gives you menus for quickly adding arrange-act-assertmethod stubs, and the AssertClasscan be late-bound, too, so you don't have to worry about deploying Rubberduck anywhere else than on your development environment just to keep the code compilable.
然后您可以在停靠的工具窗口中导航和运行您的测试方法，该窗口还为您提供用于快速添加排列行为断言方法存根的菜单，并且AssertClass也可以是后期绑定的，因此您不必担心将 Rubberduck 部署在开发环境以外的任何地方，只是为了保持代码的可编译性。
The unit testing wiki pageon Rubberduck's GitHub repository explains pretty much everything there is to explain about using it.
Rubberduck 的 GitHub 存储库上的单元测试 wiki 页面解释了几乎所有关于使用它的解释。
The latest 2.1 pre-release versions includes the beginnings of a "fakes" framework that can be used to hiHyman a number of standard library calls that would normally interfere with unit tests, by literally turning the standard library into "test fakes" that can be setup to behave as specified when executed in the context of a Rubberduck unit test, for example MsgBoxcalls:
最新的 2.1 预发布版本包括一个“伪造”框架的开始，该框架可用于劫持许多通常会干扰单元测试的标准库调用，通过将标准库从字面上转换为“测试伪造”，可以在 Rubberduck 单元测试的上下文中执行时，设置行为如指定，例如MsgBox调用：
'@TestMethod
Public Sub TestMethod1()
On Error GoTo TestFail
Fakes.MsgBox.Returns 42 ' MsgBox function will return 42
'here you'd invoke the procedure you want to test
Debug.Print MsgBox("This MsgBox isn't going to pop up!", vbOkOnly, "Rubberduck") 'prints 42
With Fakes.MsgBox.Verify ' Test will pass if MsgBox was invoked as specified
.Parameter "prompt", "This MsgBox isn't going to pop up!"
.Parameter "buttons", vbOkOnly
.Parameter "title", "Rubberduck"
End With
TestExit:
Exit Sub
TestFail:
Assert.Fail "Test raised an error: #" & Err.Number & " - " & Err.Description
End Sub
Contributions to expand that FakesAPI to cover more functions are more than welcome. Covering FileSystemObjectinvocations would be particularly useful.
Fakes非常欢迎对扩展该API 以涵盖更多功能的贡献。覆盖FileSystemObject调用将特别有用。
回答by tony722
I was just looking for the same thing and found: http://accunit.access-codelib.net/which integrates into the VBA IDE quite nicely.
我只是在寻找同样的东西，发现：http: //accunit.access-codelib.net/很好地集成到 VBA IDE 中。
Note: Viewing the page in German and using the browser translation tools seems to yield more info than the English pages.
注意：查看德语页面并使用浏览器翻译工具似乎比英语页面产生更多信息。
回答by Bjoern Stiel
The xlwingsguys just demoed how to unit tests VBA functions in Python. Essentially, you do not need to add anything to your workbook but you rater write "stand alone" Python code that call your VBA functions so that you can compare expected vs actual results of your VBA functions.
该xlwings家伙只是演示了如何单元在Python测试VBA函数。从本质上讲，您不需要向工作簿添加任何内容，但您的评估员会编写“独立”的 Python 代码来调用您的 VBA 函数，以便您可以比较 VBA 函数的预期结果与实际结果。
