如何单元测试Excel VBA代码 [英] How to unit test Excel VBA code

问题描述

有没有人有任何单元测试Excel VBA代码的经验？我想尽可能无痛地将单元测试引入一些旧的Excel VBA代码。一个想法是使用VSTO从Excel工作簿中调用代码。我想知道其他人是否尝试了单元测试Excel代码，以及可能用于单元测试Excel VBA的任何其他方法。

我将会感谢关于单元测试Excel VBA代码的任何可用框架和/或提示的一些指针。

解决方案

免责声明：我拥有Rubberduck的GitHub存储库，我是项目中涉及的开发人员之一。

Rubberduck 正在积极发展。对于VBA而言，它的单元测试工具不仅仅是 ，但是它的工作原理很好，可以让您无需任何样板就可以编写VBA单元测试：

 '@ TestModule 
私有断言作为新的Rubberduck.AssertClass 
 
'@TestMethod 
 Public Sub TestMethod1（）
 Assert 确定测试方法尚未写入。 
 End Sub 
 
'@TestMethod 
 Public Sub AnotherTestMethod（）
 Assert.IsTrue False，有什么问题吗？ 
 End Sub 
  

然后，您可以在停靠的工具窗口中导航并运行测试方法这也为您提供了快速添加排序行为方法存根的菜单，并且 AssertClass 也可以是迟到的，所以你

---

Rubberduck的GitHub存储库中的href =https://github.com/rubberduck-vba/Rubberduck/wiki/Unit-Testing =nofollow noreferrer>单元测试wiki页面几乎解释了所有的内容关于使用它的解释。

---

最新的2.1预发行版本包括假框架的开始，可以是用于劫持一些通常会干扰单元测试的标准库调用，通过逐字将标准库转换为可以设置为指定的测试假当在Rubberduck单元测试的上下文中执行时，fied被执行，例如 MsgBox 调用：

 '@ TestMethod 
 Public Sub TestMethod1（）
错误GoTo TestFail 
 
 Fakes.MsgBox.Returns 42'MsgBox函数将返回42 
 
'这里你会调用你想测试的程序
 Debug.Print MsgBox（这个MsgBox不会弹出！，vbOkOnly，Rubberduck）'打印42 
 
与Fakes.MsgBox.Verify'如果MsgBox被调用指定
 .Parameter提示符，这个MsgBox不会弹出！ 
。参数按钮，vbOkOnly 
。参数标题，Rubberduck
结束
 TestExit：
退出子
 TestFail：
 Assert.Fail测试引发错误：＃&错误编号&  - & Err.Description 
 End Sub 
  

贡献扩大 / code> API覆盖更多功能更受欢迎。覆盖 FileSystemObject 调用将特别有用。

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.

I would appreciate some pointers as to any available frameworks and/or tips on unit testing Excel VBA code as well.

解决方案

Disclaimer: I own Rubberduck's GitHub repository, and I'm one of the devs involved in the project.

Rubberduck is under active development. It's much more 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:

'@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-assert method stubs, and the AssertClass can 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.

---

The unit testing wiki page on Rubberduck's GitHub repository explains pretty much everything there is to explain about using it.

---

The latest 2.1 pre-release versions includes the beginnings of a "fakes" framework that can be used to hijack 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 MsgBox calls:

'@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 Fakes API to cover more functions are more than welcome. Covering FileSystemObject invocations would be particularly useful.

这篇关于如何单元测试Excel VBA代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！