Are there any good unit testing packages for Excel? [closed]

Closed. This question does not meet Stack Overflow guidelines. It is not currently accepting answers.

---

We don’t allow questions seeking re开发者_开发百科commendations for books, tools, software libraries, and more. You can edit the question so it can be answered with facts and citations.

Closed 4 months ago.

Improve this question

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

---

I assume you want to unit test VBA code in modules.
First, even if there was a good unit testing framework available for VBA, I suspect testing would be very complex, because of the dependencies to the Workbook itself. If your code interacts with the workbook and its objects is going to be a total nightmare because you can't really mock any of this: imagine testing a module that reads data in a sheet and creates a chart in another one... In essence, an Excel workbook merges your persistence, domain and presentation all in one - not good for testing.
The other case is code which is mostly computation oriented. If that code gets complex enough that it warrants testing, one thing you might consider is to actually move your code outside of VBA, to make it testable. I often work with clients who have large financial models, with heavy VBA, and when I can I like to extract the VBA code to C# and make it a VSTO add-in. The benefit is that I can test the code, and work in Visual Studio, instead of the VBA IDE.

---

There are a couple of experiments kicking around the internet: EUnit and VBAUnit. Neither of these projects seem to have active communities, so they are unlikely to be as robust as (say) JUnit or NUnit. But they may be good enough for your purposes.

---

I use Debug.Assert. I organize the tests in modules. In each module I maintain a Sub RunAll that calls all the test methods in that module.

To run all tests in the project, I have a module AllTests with a RunAll that calls RunAll in all the test modules.

Simple and works great without any fuss.

MSDN article on VBA Debug.Assert

---

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()

---

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.

---

Here's a MS approved method of unit testing:

https://learn.microsoft.com/en-us/visualstudio/test/how-to-create-a-data-driven-unit-test?view=vs-2015&redirectedfrom=MSDN

Here's a quick summary of what you need to do:

1. Create a data source that contains the values that you use in the test method. The data source can be any type that is registered on the machine that runs the test.

2. Add a private TestContext field and a public TestContext property to the test class.

3. Create a unit test method and add a DataSourceAttribute attribute to it.

4. Use the DataRow indexer property to retrieve the values that you use in a test.

---

The FlyingKoala project can help with unit testing Excel formulas.

[FlyingKoala] provides extended functionality to xlwings.

Here's an example for unit testing formulas (and networks of formulas) from "inside" Excel (as found in the FlyingKoala library);

import unittest
import logging

import xlwings as xw
from flyingkoala import FlyingKoala
from pandas import DataFrame
from numpy import array
from numpy.testing import assert_array_equal

logging.basicConfig(level=logging.ERROR)

class Test_equation_1(unittest.TestCase):

    def setUp(self):

        self.workbook_name = r'growing_degrees_day.xlsm'

        if len(xw.apps) == 0:
            raise "We need an Excel workbook open for this unit test."

        self.my_fk = FlyingKoala(self.workbook_name, load_koala=True)
        self.my_fk.reload_koala('')
        self.equation_name = xw.Range('Equation_1')

        if self.equation_name not in self.my_fk.koala_models.keys():
            model = None
            wb = xw.books[self.workbook_name]
            wb.activate()
            for name in wb.names:
                self.my_fk.load_model(self.equation_name)
                if self.equation_name == name.name:
                    model = xw.Range(self.equation_name)
                    self.my_fk.generate_model_graph(model)

            if model is None:
                return 'Model "%s" has not been loaded into cache, if named range exists check spelling.' % self.equation_name


    def test_Equation_1(self):
        """First type of test for Equation_1"""

        xw.books[self.workbook_name].sheets['Growing Degree Day'].activate()

        goal = xw.books[self.workbook_name].sheets['Growing Degree Day'].range(xw.Range('D2'), xw.Range('D6')).options(array).value

        tmin = xw.books[self.workbook_name].sheets['Growing Degree Day'].range(xw.Range('B2'), xw.Range('B6')).options(array).value
        tmax = xw.books[self.workbook_name].sheets['Growing Degree Day'].range(xw.Range('C2'), xw.Range('C6')).options(array).value
        inputs_for_DegreeDay = DataFrame({'T_min': tmin, 'T_max': tmax})
        result = self.my_fk.evaluate_koala_model('Equation_1', inputs_for_DegreeDay).to_numpy()

        assert_array_equal(goal, result)


    def test_VBA_Equation_1(self):
        """
        The function definition being called;

        Function VBA_Equation_1(T_min As Double, T_max As Double) As Double
            VBA_Equation_1 = Application.WorksheetFunction.Max(((T_max + T_min) / 2) - 10, 0)
            End Function
    """

        goal = 20

        vba_equation_1 = xw.books[self.workbook_name].macro('VBA_Equation_1')
        result = vba_equation_1(20.0, 40.0)

        self.assertEqual(goal, result)

And here's an example from "outside" Excel (as found in the FlyingKoala library);

import io
import sys
import unittest

import xlwings as xw
import flyingkoala
from flyingkoala import *

sys.setrecursionlimit(3000)

workbook_path = 'C:\\Users\\yourself\\Documents\\Python\\example\\'
workbook_name = r'example.xlsm'

class Test_equation_1(unittest.TestCase):
    """Unit testing Equation_1 in Python
    Using;
    - named ranges to discover the address of the formula
    - Python as the calculation engine
    This approach requires;
    - Workbook to be tested needs to be open. In this case it's example.xlsm from the example directory in FlyingKoala++
        ++This needs to be changed so that FlyingKoala can use Koala to figure out the model for itself
    """

    equation_name = 'Equation_1'

    books = xw.books
    workbook = reload_koala('%s%s' % (workbook_path, workbook_name), ignore_sheets=['Raw Data'])

    equation_1 = None
    selected_book = None

    # find the equation, and its address
    for book in books:
        if book.name == workbook_name:
            selected_book = book

            for named_range in book.names:
                if named_range.name == equation_name:
                    equation_1 = named_range.refers_to_range
                    # parse the equation into Python
                    generate_model_graph(equation_1)

    def test_1(self):
        """First type of test for Equation_1"""

        # define test case inputs
        case_00 = {'terms' : {'T_base': 10, 'T_min': 10, 'T_max': 20}, 'result' : 5.0}

        # Do a calc
        result = flyingkoala.evaluate_koala_model_row(self.equation_name, case_00['terms'], flyingkoala.koala_models[self.equation_name])

        # test the result of the calculation
        self.assertEqual(case_00['result'], result)