Are there any good unit testing packages for Excel?

Question

Are there any good frameworks that can help unit test code in Excel ?

Answer 1

I wrote a blog post about how you can use Python's unittest infrastructure easily to write unit tests for VBA but also cell logic in general: https://www.zoomeranalytics.com/blog/unittests-for-microsoft-excel

In essence, you can test the following VBA function:

Function mysum(x, y) As Double
    mysum = x + y
End Function

using the following unittest in Python:

import unittest
import xlwings as xw

class TestMyBook(unittest.TestCase):
    def setUp(self):
        # setUp will be called before the execution of each unit test
        self.wb = xw.Book('mybook.xlsm')  # map workbook
        self.mysum = self.wb.macro('Module1.mysum')  # map function

    def test_mysum(self):
        result = self.mysum(1, 2)  # get result from VBA
        self.assertAlmostEqual(3, result)  # test if result corresponds to the expected value

if __name__ == '__main__':
    # This allows us to easily run the tests from the command prompt
    unittest.main()

Answer 2

I'm surprised not to find Rubberduck amongst the other suggestions. It is a COM addin for the VBE (for all MS Office hosts: Excel, Word, PowerPoint, Access), open-source and under active development. Its main features are unit testing and code inspections.

A brief description, taken from the above linked wiki page:

Why Rubberduck?

Because we want a pet project, and this one is fun, and challenging. The idea is to do everything we can to make programming in VBA and refactoring legacy VBA code, as enjoyable as in the modern-day versions of Visual Studio. The VBE has had its VB6 feel for as long as I can remember, Rubberduck takes it... elsewhere.

It all started on Code Review, with a post about unit testing in VBA. It grew into a 100% VBA working solution, and then we wanted to have this unit testing functionality built into the IDE - the COM add-in solution started with it.

Having access to the entire VBE object model in a COM add-in that can add custom menus, toolbars and dockable windows, we wanted to parse and inspect the code, and fix issues - or at least point them out.

Then we just thought let's pack everything we want to implement to extend the VBE. We added ways to navigate the code more easily, a list of todo items, and then we had ideas like integrating source control, implementing some refactorings, using the Stack Exchange API to create and post your VBA code as a Code Review question.

I've been using it for about half a year now (primarily under Excel 2010), and it has been quite stable and helpful. Thus, I would recommend it.