如何在VBA Excel宏中设置单元测试？ [英] How to setup a unit test in VBA Excel Macro?

问题描述

我正在处理一个Excel VBA宏，它将使用一些excel文件，擦洗/清理它，并生成一个包含2张纸的工作簿 - 第1页是原始的脏文件，表2是干净的擦除文件。 / p>

由于我们现在有10种不同的格式，而在路上40+ ..我想保存这个输出给定的一组文件，然后写一个单元测试它采用原始输入，运行我们的宏，然后将宏的输出与保存的预期输出进行比较。

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

这是我第一次使用VBA。我在搜索框架或插件，并找不到很多帮助。我确实发现了一个功能，将比较2整个excel电子表格 - 所以我有这个部分。但现在我需要弄清楚如何实际写入和执行这个单元测试。编写单元测试应该很简单 - 但是如何执行？我不想在电子表格上放一个按钮，表示运行单元测试...在VBA / Excel中有一种方法来运行任意函数，所以我可以右键单击我的UnitTest函数运行？

有关我的单元测试计划的任何其他反馈也将得到欣赏。再次感谢

解决方案

如果是我，我将创建一个引用待测试表的第二个电子表格。 （去工具>参考>浏览）这个新的工作簿可以包含你想要的所有测试，而不必弄脏主工作簿。如果你想要一个，这也让你自己建立一个界面。

作为附注，如果要隐藏Excel宏菜单中的过程，但仍可访问它们，请在模块的顶部放置：

 选项私人模块
  

然后让你想要的所有程序能够使用公共。然后，您可以从即时窗口（VBE中的ctrl-g）调用它们，但不会在宏列表中列出。

但是，如果您保留您的单位测试分开（你可能应该），那么你真的不用担心公共/私人模块。

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.

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.

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'?

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

解决方案

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:

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.

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

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