Creating XLLs with Add-In Express.NET – Part 1

Introduction

Recently I had a discussion with a local colleague and friend about using third-party developing tools. He prefers to not use any other tools than Excel and VS.NET. In that way he says he gets 100 % control of the developing process.

One of my main targets is to reduce the overall time the developing process takes. To achieve it I’m also prepared to use any third-party tools that can support me. After all, we are always depended on some softwares in order to develop solutions. So even when I’m using third-party tools I still feel that I have full control. By reducing the developing time my clients will also be saving money as their costs will be reduced.

Today it exist rapid application (RAD) tools that allow us to set focus on the core part in the developing process. One of them is Add-in Express 2010 for Office and .NET which I have found to be a good companion.

Of course, when using RAD tools and third-party controls it’s important that we can legally redistribute any assemblies that are required in order to run the solutions. Another critical aspect is that it must be easy to update the present versions when the vendors release new versions.

This article is the first of a series of total three articles that will cover how to create XLL add-ins with Add-in Express 2010 for Office and .NET. With the tool we can also easily create solutions for managed COM Add-ins, managed Automation Add-ins, Smart Tags and Real Time Data (RTD) Servers. The RAD tool is fully integrated in VS.NET and it works well with the latest versions of VS.NET and of Excel, including the 64-bit  version of Excel 2010.

In this first article I will describe how we easily can create an XLL add-in with the RAD tool. The second article will discuss the deployment of the XLL add-in. The final article will set focus on the pros and cons of using a RAD tool like Add-in Express .NET 2010 for developing XLL add-ins. I hope the series will shed some light on how we can develop solutions with the RAD tool.

The Add-in Express 2010 for Office and NET has recently been released. However, when I developed the first case here I used the beta version of the RAD tool together with Windows 7 x86, VS.NET 2010 and Office 2010 Professional.

Creating a XLL solution with Add-in Express 2010 .NET
Let us launch VS 2010 and start to create the project for the XLL add-in as the following screen shot shows:

Here we will create an XLL add-in so we select the “ADX XLL Add-in” template in the dialog. As we can see in the screen shot we will be using the 4.0 version of the framework. It should also be noted that we will, by selecting this template, create an isolated XLL add-in. I will discuss this more in detail in the third and final article. As we can see from the next screen shot (see below) the wizard is started which will guide us through the process of creating the framework for the XLL add-i n. In the first step we select the programming language we want to use and we also select the Excel version we intend to target (and above) as the below screen shot shows:

In the next step we either chose to create a new strong name key file or use an existing key file for the project as the following screen shot shows:

In the next step the project solution is created and the following screen shot shows its main parts in the Solution Explorer:

That is all! Now we can start to create our UDFs. However, in this case we will be using the example UDF that is created in the project, AllSupportedExcelTypes, so we will move to the next step. In order to debug the UDF we need to register the project on the development computer which is done by selecting the command Build from the main menu in VS IDE and select the command Register ADX Project as the following screen shot shows:

The next step is to add a description for the XLL add-in as well as a description of the UDF. To do so we need to open the Visual Designer for the main Component Class (XLLModule.vb) by right clicking on the class in the Solution Explorer and from the menu select the command View Designer as the below screen shot also shows.

In the Designer View we right click to access the menu from where we can add the wanted component. Here we will add the Excel Function Category component so we select the command Add Excel Function Category as the following screen shot shows:

When we have added the component to the Visual Designer we can access its properties and add a category name together with a description for the UDF as the following screen shot shows:

Next we can add a description for the only required argument in the UDF as the following screen shot also shows:

Now we have walked through all the basic steps in order to create a XLL add-in project and it’s now time to see it in action.

The following screen shot shows that the add-in is now available and active on the development computer:

When we want to access the UDF from the Insert Function wizard we see that it has its own category, XLDennis UDFs, and when selecting the UDF we see its description as the following screen shot shows as well:

When the UDF is selected and we click on the OK button the UDF’s argument(s) is showed as the following screen shot show:

When clicking on the OK button Excel inserts the UDF and show the result as the following screen shot shows.

In my opinion it’s very easy to create XLL add-ins with Add-in Express 2010 for Office and .NET which I also hope the walkthrough here has showed. In the next article I will discuss the deploymen of XLL add-ins t in more detail.

Kind regards,
Dennis

Note: The second article in the series can be found here.