When mocking Workbook->Worksheets: variable 'p' of type 'Microsoft.Office.Interop.Excel.Workbook' referenced from scope '', but it is not defined

Question

I'm just starting to learn how to use unit testing with NUnit and how to use mocking to fake the objects I need to test.

Currently I'm working with a project that implements a VSTO Excel Addin and would like to start using unit tests with that project.

So I have a function that takes a Workbook as parameter and returns a list of relevant worksheets that I want to process. This function I would like to test. I "just" need to fake the worksheet names, as that function only iterates through all the worksheets and checking their names.

With searching the internet resources I was trying to build the following solution trying to fake a Workbook with a list of Worksheets with the name list "SheetNames", which is of type List:

        var fakeWorksheetList = new List<Excel.Worksheet>();
        foreach (string sheetName in SheetNames)
        {
            var sheet = Mock.Of<Excel.Worksheet>();
            sheet.Name = sheetName;
            fakeWorksheetList.Add(sheet);
        }

        var fakeWorkbook = new Mock<Excel.Workbook>();
        fakeWorkbook.Setup(p => p.Worksheets.GetEnumerator())
            .Returns(() => fakeWorksheetList.GetEnumerator());

But when running the test code I get the following error message:

variable "p" of type "Microsoft.Office.Interop.Excel.Workbook" referenced from scope "", but it is not defined

What am I'm doing wrong. Why does the lambda expression in Setup() does give me such an error message? Is there a better way to fake the Worksheets List?

Please forgive me if I show some lack of understanding as I'm really just starting with the mocking topic.

This is the function I want to test, which I need in my add-in to get all the worksheets my add-in has to work with:

    public static Dictionary<int, Excel.Worksheet> GetApplicableYearSheets(Excel.Workbook Workbook, int iCurrentYear = 0)
    {
        if (iCurrentYear <= 0)
            iCurrentYear = DateTime.Now.Year;

        int iFromYear = iCurrentYear - 2;
        Dictionary<int, Excel.Worksheet> YearSheets = new Dictionary<int, Excel.Worksheet>();

        for (int iNr = 1; iNr <= Workbook.Worksheets.Count; iNr++)
        {
            int iYear = 0;
            string sWorksheetName = Workbook.Worksheets[iNr].Name;
            if ((sWorksheetName.Trim().Length == 4) && (int.TryParse(sWorksheetName, out iYear)))
            {
                if ((iYear >= iFromYear) && (iYear <= iCurrentYear))
                    YearSheets.Add(iYear, Workbook.Worksheets[iNr]);
            }
        }

        return YearSheets;
    }

The sheet names list I want to use for testing, for example is:

        List<string> SheetNames = new List<string>()
        {
            "2012",
            "2013",
            "2014",
            "2015",
            "2016",
            "2017",
            "2018",
            "2019",
            "Test",
            "Spezialfälle"
        };

My plan was to test calling this function with the mocked fakeWorkbook, for example in the following way:

Assert.That(InvoicingUtils.GetApplicableYearSheets(FakeWbFullNames, iCurrentYear: 2019), Has.Exactly(3).Items);

and so on

Answer 1

I think there is a better and more elegant way how to write the mocking code but this is working for me.
Please note:
- I use Moq as the mocking framework. I believe you use the same
- I changed the returning type of your method to IDictionary as I had some issues with just Dictionary

The code:
- For initializing your test sheets you can use a loop. I just made it simple and created just two sheets without any loop.
- I think you would be able to get it work with just few setups on the workbook object but I like it this way (just personal preference nothing else)
- As you can see the whole logic is ran on the sheets object. Basically I get an index from production code, subtracting -1 to get zero based index, save that to my variable sheetIndex and then return back a fake sheet from the worksheets collection

using System.Collections.Generic;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Moq;

namespace ExcelMocking.Tests
{
    [TestClass]
    public class UnitTest1
    {
        private List<Excel.Worksheet> worksheets = new List<Excel.Worksheet>();

        [TestMethod]
        public void TestMethod1()
        {
            var sheet = new Mock<Excel.Worksheet>();
            sheet.SetupGet(w => w.Name).Returns("2012");
            worksheets.Add(sheet.Object);

            sheet = new Mock<Excel.Worksheet>();
            sheet.SetupGet(w => w.Name).Returns("2013");
            worksheets.Add(sheet.Object);

            var sheetIndex = 0;
            var sheets = new Mock<Excel.Sheets>();
            sheets.Setup(s => s.Count).Returns(() => worksheets.Count);
            sheets.Setup(s => s[It.IsAny<object>()]).Callback<object>((theSheetIndex) =>
            {
                // getting the real sheet index from the production code that starts from 1
                // and simple subtracting -1 to get zero based index
                sheetIndex = (int)theSheetIndex;
                sheetIndex--;
            }).Returns(() => worksheets[sheetIndex]);

            sheets.Setup(s => s.GetEnumerator()).Returns(() => worksheets.GetEnumerator());
            sheets.As<Excel.Sheets>().Setup(s => s.GetEnumerator()).Returns(() => worksheets.GetEnumerator());

            var workbook = new Mock<Excel.Workbook>();
            workbook.Setup(w => w.Worksheets).Returns(sheets.Object);            

            IDictionary<int, Excel.Worksheet> result = ExcelMocking.Class1.GetApplicableYearSheets(workbook.Object);
        }
    }
}