VSTO & .NET & Excel

July 8, 2010

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

Filed under: .NET & Excel, COM Add-ins, Excel, XLLs — Dennis M Wallentin @ 5:00 pm

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.

Advertisements

17 Comments »

  1. Nice post Dennis, look forward to the next 2. I’ve been looking at XL DNA recently and have also found that to be excellent for XLL’s.

    Many thanks
    Ross

    Comment by ross — July 9, 2010 @ 10:26 am

    • Thanks Ross 🙂

      Hopefully I will have the time to post next article very soon. Excel DNA is interesting and since I know that both You and Simon cover it I thought I will add this RAD Tool.

      BTW, will You be releasing any white paper from the upcoming conference?

      Kind regards,
      Dennis

      Comment by Dennis Wallentin — July 9, 2010 @ 11:50 am

  2. Well I’m planing a number of demo’s during the talk so i will try and make these into videos. I’ll probably be posting quite a bit about DNA in the next few months so, watch this space as they say 😉

    thanks
    Ross

    Comment by ross — July 9, 2010 @ 1:42 pm

    • Ross,

      Sounds good to me and I will look forward to take part of them. Good luck with the conference.

      Kind regards,
      Dennis

      Comment by Dennis Wallentin — July 9, 2010 @ 4:17 pm

  3. Dennis – I am just starting to look seriously at using AddIn Express with Visual Studio and VB.Net. I currently have the VS Express version installed, and have tried the first XLL Project example from Addin Express.

    At the end of the process (much as you have described in this article) it says to build and register the project. I can’t find any options for registering, and the project has compliled as a dll, rather than an xll.

    Am I missing something, or is it perhaps a limitation of VS Express?

    Any thoughts?

    Comment by dougaj4 — July 18, 2010 @ 1:50 pm

  4. Doug,

    In my second coming article I will discuss what You have find out already:
    The RAD tool does not generate real XLL files and each solutions requires a COM Shim which the RAD tool add to the solutions.

    I have no experience with VS Express but I find it remarkable if projects cannot be registered. If my memory don’t trick me the Express editions are supported.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — July 19, 2010 @ 2:51 am

    • I’ve just installed VS Pro (currently available with 90 day trial period), and un-installed and re-installed Addin Express.

      The sample xll project now works exactly as shown in the manual, with a “register ADX Project” option available in the right click menu.

      I also note that in PED (2nd Edition) it says in table 24-1 that VS Express supports only “Automate Excel”, not “Shared Add-in template”, “Office templates”, or “Visual Studio Tools for Office”.

      Am I right in thinking that xlls need the VSTO tools, so this may be why the xll function didn’t work with VS Express?

      Comment by dougaj4 — July 20, 2010 @ 3:54 am

  5. Doug,

    First of all, the comment in PED refers to VS itself and not including third-party tools like Add-in Express for Microsoft Office and .NET

    Add-in Express for Microsoft Office and .NET supports VB.NET Express as stated in the following link: http://www.add-in-express.com/add-in-net/requirements.php

    Hope it will clear some mud and answer Your question. No, any XLL solution created with the RAD tool does not require any VSTO tools.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — July 21, 2010 @ 5:27 pm

    • Dennis – OK, but the question remains why the “registr ADX Project” option is there after intalling VS Pro, but not when I only had VS Express installed.

      Comment by dougaj4 — July 26, 2010 @ 1:16 am

      • Doug,

        I have no clue why. Have You been in contact with Add-in Express about it?

        Kind regards,
        Dennis

        Comment by Dennis Wallentin — July 26, 2010 @ 10:22 pm

  6. Hi Dennis,

    Really love your blog as it’s cluing me into areas of Excel development that I’m just getting started in.

    I’ve developed a lot of very complex royalty calculation formulas for my company that are currently in a VBA Add-In and I want to convert them to XLL’s for faster performance. Will I be able to use Add-In Express for this without learning a higher-level language like C++? Thanks for your feedback.

    Jeff

    Comment by Jeff — July 28, 2010 @ 7:48 pm

  7. Jeff,
    Thanks for Your kind words about my blog. If You’re aiming to gain a faster performance then the answer to Your question is no.

    With Add-in Express we create a .NET assembly, i.e, a DLL and not a XLL that Excel can directly communicate with. To let Excel know that the assembly exists we must use a DLL that creates a separate AppDomain for the assembly (isolation in a similar way as with shimmed managed COM add-ins and with VSTO add-ins) and load the assembly into the created AppDomain. This is OK as it’s only done one time -when the assembly is loaded.
    Because of the isolation of the assembly the calls from Excel to the assembly is always cross-boundary calls. These calls are much slower to do then calling native XLLs or VBA.

    Add-in Express has some really great features and it allow us to reduce the development time but it comes with a price within this specific context.

    I’m a bit behind my the time table for the series so questions that are raised now will have their answers in the coming entries.

    Back to Your situation; I would suggest that You take a closer look into ExcelDNA which can help You achieve what You want and it’s also free: http://exceldna.codeplex.com/

    Edit: Please see my next comment and I would suggest that You, at least, test the Add-in Express. Especially if You have an interest in managed COM add-ins and RTD Server well.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — July 29, 2010 @ 3:01 am

    • Thanks Dennis, I’ve been meaning to try ExcelDNA and it worked great with the simple example UDF that Govert supplied.

      One question I have is how to have all of the UDF’s I’m developing this way in one add-in rather than having each UDF as its own add-in as it currently shows up. If you could point me to an information source for doing that, I would really appreciate it. I have the old version of PED but will gladly pick up the new edition if it contains the info I’m seeking.

      Thanks again for helping yet another Excel/VBA guy make the quantum leap to real development.

      Jeff

      Comment by Jeff — July 29, 2010 @ 8:47 pm

  8. Jeff,

    I took a closer look into ExcelDNA and it seems that it also use a loader to create a new AppDomain and load the assembly into the created AppDomain.

    So per definition there is no difference between Add-in Express .NET and ExcelDNA when it comes to this aspect.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — July 31, 2010 @ 2:41 am

  9. Jeff, you can put as many UDF as you like in to one XL DNA xll? What is it you are struggling with?

    Thanks
    Ross

    ps. Sorry to hi jack this post Dennis, Jeff feel free to e-mail me direct if you like – rossmclean at gmail dot com

    Comment by ross — August 2, 2010 @ 1:08 pm

    • Ross,

      Thanks and I suggest that Jeff get in touch with You via e-mail as it seems to be a more specific question then a general question.

      Thanks,
      Dennis

      Comment by Dennis Wallentin — August 2, 2010 @ 1:29 pm

  10. Hi Dennis, awesome review there. In case you’re interested, I’ve also written a review of Add-in Express:
    http://www.bernardvukas.com/add-in-express-for-office-and-net-review/

    Comment by bervukas — February 1, 2016 @ 2:48 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: