Coded UI Tests – Test Any Office Add-in

I’ve found a blog post on my site where I’m using Rubberduck for writing unit tests in VBA as early as May this year. Today there’s different kinds of tests for almost any app type on Windows, and UI tests are one of them.

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 many Office desktop apps (Excel, Word, Outlook, PowerPoint, etc.). Coded UI Tests 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:

Unfortunately, Coded UI Tests are not found in the free Community Edition of Visual Studio, so you’ll need to upgrade. However, once you do, you’ll be able to test any Windows app. That means ANY app: Win32, VB6, WPF, .NET, UWP, you name it.

Coded UI Tests for Excel,  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.:

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:

Summary

“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. However, since Coded UI Tests are a Microsoft technology integrated into Visual Studio, it’s a natural starting place for many developers.

If you have questions related to this technology, or if you’re wondering if it’s right for you, just let me know in the comments below!