Coded UI Tests – Test Any Office Add-in

In my previous blog posts I’ve written about writing unit tests for Office add-ins. Today there’s different kinds of tests for almost any type of project in Visual Studio, and UI tests are one of them.

As you probably know, I’m a big fan of testing capabilities found in Visual Studio. I like using them together with an Office add-in, to automate across all Office versions and blends that the end-users may be running.

When you publish your Office add-in and it goes ‘into the wild’, your end-users will be running it on all kinds of systems and configurations, and inevitably problems will occur.

In Coded UI Tests you record user actions like mouse clicks, mouse moves, key presses, etc., just as if you were doing manual testing. These recordings of manual tests can then be repeated and played back later, as well as integrated into your build process.

Coded UI Tests aren’t exactly knew: they are Microsoft’s own UI testing framework bundled with Visual Studio, that allows developers to test Windows apps.

And since it’s from Microsoft, there are plenty of options when it comes to programming languages you can use to write your tests:

With the Coded UI Tests you’ll be able to test any Windows app, such as: Win32, VB6, WPF, .NET, UWP, you name it.

Coded UI Tests for Excel, Word, Outlook, PowerPoint, etc.?

In the context of development for Microsoft Office, it’s great knowing we can automate and test any of the Office desktop apps. And we don’t care if the app under test is VBA macros, COM add-ins, web apps, or something completely different. How sweet is that?

We can do things such as: launch Excel, activate a custom ribbon tab, press a button which shows a userform, then populate the userform etc.:

// Launch Excel 2016 from '%ProgramFiles%\Microsoft Office\root\Office16\EXCEL.EXE'
ApplicationUnderTest excelApp = ApplicationUnderTest.Launch(this.StartAndQuitExcelParams.ExePath, this.StartAndQuitExcelParams.AlternateExePath);
// After Excel loads, click the 'Blank workbook' list item
Mouse.Click(blankWorkbookListItem, new Point(117, 182));
// Select your add-in's ribbon tab
Mouse.Click(myAddinRibbonTab, new Point(276, 771));
// Click a control on your add-in's ribbon, in order to display the VBA userform
Mouse.Click(myHelloWorldControl, new Point(305, 72));
// Now that the userform is shown, populate the two textboxes with values
myFirstNameTextBox.Text = "John";
myLastNameTextBox.Text = "Doe";
// Press "OK" on the userform, and validate the UI behaves as expected
// Here the test can succeed or fail, which is exactly what we want!

Similarly to the simple scenario above, you may record any number of other scenarios, and test all the areas of your app as such.

How Coded UI Tests Work?

Coded UI Tests use a concept of “record and playback”, so they’re extremely easy to get started with. For recording the tests, we use a tool called “Coded UI Test Builder”.

Each interaction (such as a mouse click or keystroke) is being recorded. Depending on the complexity of your app’s UI, i.e. how many forms you have, you may want to have a different set of tests. It’s not a bad idea to consider putting tests for each form in a separate class or module file.

These tests can then later on be played back at a convenient time, to assure all the ‘screens’ of your add-in’s UI indeed behave as intended. With the Visual Studio Test Runner, you can basically totally control Excel (or your favorite Office app), regardless if you’re working with a COM add-in, VBA add-in, or something else.

Here’s the visual equivalent of the above code snippet:


“Coded UI Tests” are a great addition to your arsenal of Office development and testing tools for Windows, if you need to automate UI tests for add-ins in Excel, Outlook, PowerPoint, etc.

While it’s great that we’re able to automate testing of Office add-ins with Coded UI Tests, this tool is not perfect. One of the flaws being is that the code generated is pretty verbose, and tends to bloat easily and becomes difficult to manage.

There are other UI testing frameworks that do a better job while being less verbose. However, since Coded UI Tests are a Microsoft technology integrated into Visual Studio, it’s a natural starting place for many developers.

What’s next?

If you have questions related to this technology, or if you’re considering using it in-house, send me a message on my Contact Me page, or leave a comment below.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.