Share via

Unit Testing for VSTO (part 2 Automated Tests)

  • Article

In my last post, Unit Testing for VSTO (Part 1 Manual Tests), I showed you how to use VSTS to create manual unit tests. In this post you will learn how to create automated tests.

Automated Tests

You now have a basic understanding of how the manual unit testing works. But you don’t want to sit there and run hundred of tests manually. Automated tests allow you to pass data to your application to test various scenarios. The test can automatically determine if the test passes or fails. Let’s take a look at how to enable automated tests for VSTO 2005.

1. Open the ExcelWorkbook1 project you created in the previous section.

2. Add Remoting Server to the host app. The first thing you will do is add the server code to your Excel workbook. When the workbook opens the server will start. This will allow the test client to communicate with the workbook to run the tests. Add a new class to your ExcelWorkbook1 project called RemotingServer.vb. Add the following code to this class.

Imports System.Runtime.Remoting

Imports System.Runtime.Remoting.Channels

Imports System.Runtime.Remoting.Channels.Tcp

'This class is to enable unit testing

Public Class RemotingServer

    Shared channel As TcpChannel

    Public Shared Sub Start()

        'pick any open channel number

        channel = New TcpChannel(8085)

        ChannelServices.RegisterChannel(channel, False)

        RemotingServices.Marshal(Globals.ThisWorkbook, " ExcelWorkbook1")

    End Sub

    Public Shared Sub Unregister()

        ChannelServices.UnregisterChannel(channel)

    End Sub

End Class

  1. Add a Reference to System.Runtime.Remoting. Right Click on the ExcelWorkbook1 project and choose add references. Browse the .Net references for System.Runtime.Remoting.
  2. Start remoting server on startup. Add the following code to your existing workbook startup event handler

    Private Sub ThisWorkbook_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup

        button1.Text = "Click to Test"

        ActionsPane.Controls.Add(button1)

        'Start the remoting server so Unit Testing can occur

        RemotingServer.Start()

    End Sub

  1. Verify Host. Press F5 to Build and Run the ExcelWorkbook1 project. The workbook should open with no error.

Create the Client test hookup

  1. Add 2 Refereces to UnitTest1 project. Right Click on the UnitTest1 project and click Add References. Under the Com tab of the add references dialog select Microsoft Excel 11 Object Library. Click OK. Now add a reference to the ExcelWorkbook1 project following the same steps as above. Note you may need to click on browse tab to browse to the output location where the ExcelWorkbook1.dll is. The last reference you need to add is under the .net tab of the add references dialog, System.Runtime.Remoting.
  2. Open the automated test. Double click on the UnitTest1.vb file in the TestProject1 project. The automated test will open.
    These next steps are shown in the code block in step 6
  3. Add the Import statements.
  4. Add shared variables to hold references to the workbook you are testing
  5. Add a ClassInitialize to hook up the unit test to the workbook. This is the client part that talks to the server you created in the previous section.
  6. Add a ClassCleanup to close the workbook when the tests are finished.
    Replace the code in the UnitTest1.vb file with the following code.

Imports System

Imports System.Text

Imports System.Collections.Generic

Imports Microsoft.VisualStudio.TestTools.UnitTesting

Imports System.Runtime.Remoting

Imports System.Runtime.Remoting.Channels

Imports System.Runtime.Remoting.Channels.Tcp

<TestClass()> Public Class UnitTest1

    Shared ThisWorkbook As ExcelWorkbook1.ThisWorkbook

    Shared workbookToTest As String = "C:ExcelWorkbook1ExcelWorkbook1binDebugExcelWorkbook1.xls"

    <ClassInitialize()> Public Shared Sub MyClassInitialize(ByVal testContext As TestContext)

        'start the VSTO Word or Excel document

        Dim ExcelApp As Microsoft.Office.Interop.Excel.Application

        ExcelApp = New Microsoft.Office.Interop.Excel.Application

        ExcelApp.Workbooks.Open(workbookToTest)

        ExcelApp.Visible = True

        'Get the VSTOTestWrapper object

        Dim channel As New TcpChannel()

        ChannelServices.RegisterChannel(channel, False)

        ThisWorkbook = CType(Activator.GetObject( _

        GetType(ExcelWorkbook1.ThisWorkbook), _

        "tcp://localhost:8085/ExcelWorkbook1"), ExcelWorkbook1.ThisWorkbook)

    End Sub

    <ClassCleanup()> Public Shared Sub MyClassCleanup()

        'Get a reference to the workbook to close it

        Dim workbook As Microsoft.Office.Interop.Excel.Workbook

        workbook = GetObject(workbookToTest)

        workbook.Saved = True

        workbook.Close()

        workbook.Application.Quit()

    End Sub

    <TestMethod()> Public Sub TestMethod1()

        ' TODO: Add test logic here

    End Sub

End Class

Add the unit tests

You are now ready to start creating your unit tests. These are the automated tests that will exercise the features in your application. Let’s get started by creating a few tests.

1. Add the following 3 unit tests to the UnitTest1 class.

    <TestMethod(), Description("Test reading from a cell")> _

    Public Sub ReadFromCellTest()

        'Sub to test

        ThisWorkbook.ReadFromCell()

       'passed if there are no exceptions

        Assert.IsTrue(True)

    End Sub

    <TestMethod(), Description("Test writing to a cell")> _

    Public Sub WriteToCellTest()

        Dim TestString As String = "Test String"

        ThisWorkbook.WriteToCell(TestString)

        'now read the string to verify that it wrote it

        Dim celltext As String = ThisWorkbook.ReadFromCell()

        'verify the 2 strings are the same

        Assert.IsTrue(TestString = celltext)

    End

Comments

  • Anonymous
    September 15, 2006
    PingBack from http://blogs.msdn.com/pstubbs/archive/2006/09/15/756167.aspx

  • Anonymous
    September 19, 2006
    Paul Stubbs has posted two interesting articles about Unit Testing for VSTO using Visual Studio 2005...

  • Anonymous
    November 20, 2006
    You can update data in your office documents using ServerDocument . You can cache data in an office document

  • Anonymous
    November 25, 2008
    Anyone try this code in VS2008? The "Globals.ThisWorkbook" can't be casted to MarshalByRefObject now so that the sample above is blocked. Anyone has solution for this? Thanks

  • Anonymous
    April 28, 2009
    I stopped with the same issue in VS 2008. The "Globals.ThisWorkbook" can't be casted to MarshalByRefObject now so that the sample above is blocked. Any solution?

  • Anonymous
    June 09, 2010
    Can you please post the link on code coverage for vsto test? At the end of you have mentioned about it.

  • Anonymous
    February 22, 2012
    I don't think there ever was a post on code coverage for VSTO tests...  Also the Globals.ThisWorkbook doesn't work nor its equivalent Globals.ThisDocument in Word VSTO.