如何在 VBA Excel 宏中设置单元测试？

How to setup a unit test in VBA Excel Macro?

提问by dferraro

I am working on an Excel VBA macro that will take some excel file, scrub / clean it, and produce a workbook with 2 sheets - sheet 1 being the original 'dirty' file, sheet 2 being the cleanly scrubbed file.

我正在处理一个 Excel VBA 宏，它将获取一些 excel 文件，擦洗/清理它，并生成一个包含 2 张工作表的工作簿 - 工作表 1 是原始的“脏”文件，工作表 2 是干净的擦洗文件。

Since we have 10 different formats right now, and down the road 40+.. I would like to save this output for a given set of files, then write a unit test which takes the original input, runs our macro, then compares the macro's output to the saved, expected output we have.

由于我们现在有 10 种不同的格式，而且未来有 40 种以上的格式。输出到我们保存的预期输出。

Then anytime later down the road when we do maintenance, we can quickly and easily run our unit-tests to make sure we didn't break anything that already worked.

然后在进行维护时，我们可以快速轻松地运行单元测试，以确保我们没有破坏任何已经工作的东西。

This is my first time working with VBA. I googled around for frameworks or plug ins and can't find much help. I did find a function that will compare 2 whole excel spreadsheets - so I have that part down. But now I need to figure out how to actually write and execute this unit test. Writing the unit test should be pretty straight forward - but how do I execute it? I don't want to put a button on the spreadsheet that says 'run unit tests'... Is there a way in VBA/Excel to just run an arbitrary function, so I can just say right click on my UnitTest function and do 'run'?

这是我第一次使用 VBA。我在谷歌上搜索框架或插件，但找不到太多帮助。我确实找到了一个可以比较 2 个完整的 excel 电子表格的函数 - 所以我把那部分记下来了。但是现在我需要弄清楚如何实际编写和执行这个单元测试。编写单元测试应该非常简单——但是我该如何执行呢？我不想在电子表格上放一个按钮，上面写着“运行单元测试”...... VBA/Excel 中有没有办法只运行任意函数，所以我可以说右键单击我的 UnitTest 函数然后做'跑'？

Any additional feedback on what my plans for unit testing would also be appreciate. Thanks again.

任何关于我的单元测试计划的其他反馈也将不胜感激。再次感谢。

采纳答案by Oorang

If it were me, I would create a second spreadsheet that references the sheet to be tested. (Go to tools>references>browse) This new workbook can contain all the tests you want without having to dirty up your main workbook. This also frees you up to build an interface for yourself if you want one.

如果是我，我会创建第二个电子表格来引用要测试的工作表。（转到工具>参考>浏览）这个新工作簿可以包含您想要的所有测试，而不必弄脏主工作簿。如果您需要，这也可以让您腾出时间为自己构建一个界面。

As a side note, if you want to hide procedures from the Excel Macro menu but still have access to them, at the top of your module put:

附带说明一下，如果您想从 Excel 宏菜单中隐藏程序但仍然可以访问它们，请在模块顶部放置：

Option Private Module

Then make all the procedures you want to be able to use "Public". You can then call them from the immediate window (ctrl-g in the VBE) but they won't be listed in the macro list.

然后制作所有你希望能够使用的程序“公共”。然后，您可以从直接窗口（VBE 中的 ctrl-g）调用它们，但它们不会列在宏列表中。

But if you keep your unit tests separate (as you probably should), then you don't really have to worry about Public/Private modules.

但是如果你将你的单元测试分开（你可能应该这样做），那么你真的不必担心公共/私有模块。

回答by RubberDuck

Rubberduckis a free and open source VBE add-in that includes an IDE integrated Unit Testing framework and works on most of the major Office Products.

Rubberduck是一个免费的开源 VBE 插件，它包含一个 IDE 集成单元测试框架，适用于大多数主要的办公产品。

1. Download and install the latest release.
2. Open the Excel workbook your VBA project resides in.
3. Go to Tools >> Refrences and add a reference to Rubberduck.
4. Add a new standard module.

5. Add the '@TestModuleattribute and create an instance of the Rubberduck.AssertClass

   `@TestModule
   
   Private Assert As Rubberduck.Assert
   

6. Start writing tests. Any Public Sub marked with the '@TestMethodattribute will be discovered and run by the Test Explorer.

   '@TestMethod
   Public Sub OnePlusOneIsTwo()
       Assert.AreEqual 2, Add(1,1)
   End Sub
   

1. 下载并安装最新版本。
2. 打开您的 VBA 项目所在的 Excel 工作簿。
3. 转到工具>>引用并添加对Rubberduck的引用。
4. 添加一个新的标准模块。

5. 添加'@TestModule属性并创建一个实例Rubberduck.AssertClass

   `@TestModule
   
   Private Assert As Rubberduck.Assert
   

6. 开始编写测试。任何标有该'@TestMethod属性的Public Sub都将被测试资源管理器发现并运行。

   '@TestMethod
   Public Sub OnePlusOneIsTwo()
       Assert.AreEqual 2, Add(1,1)
   End Sub
   

For more information, please see the Unit Testing page of the project's wiki.

有关更多信息，请参阅项目 wiki的单元测试页面。

Disclaimer:I am one of the project's developers.

免责声明：我是该项目的开发人员之一。

回答by nitzmahone

Haven't looked at it in years, but vbaUnitused to work well...

好多年没看过了，但是vbaUnit曾经运行良好......

回答by Irwin M. Fletcher

Sure, under the Developer Tab (Excel 2007) there is a Macros button that list all available macros that you can run. You would choose from the list and click Run. Older versions have this same functionality, but I can not remember where they are located.

当然，在“开发人员”选项卡 (Excel 2007) 下有一个“宏”按钮，列出了您可以运行的所有可用宏。您将从列表中选择并单击运行。旧版本具有相同的功能，但我不记得它们位于何处。

Here is a link for 2003 version

这是 2003 版的链接

http://spreadsheets.about.com/od/advancedexcel/ss/excel_macro_5.htm

http://spreadsheets.about.com/od/advancedexcel/ss/excel_macro_5.htm