如何单元测试Excel VBA代码 [英] How to unit test Excel VBA code
问题描述
我将会感谢关于单元测试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屋!