As you surely know, VBA allows to write very complex applications simplifying and optimizing business processes in a company. However VBA is gradually beginning to reach its limits and will certainly be removed in one of the future versions of MS Office. Major shortcomings of VBA applications are the low protection of the VBA Code or missing code management tools. With VSTO – Visual Studio Tools for Office – Microsoft provides specific templates and interfaces in Visual Studio to develop office applications based on .NET. This also means that the full power of .NET applications is made available to office Add-Ins. And, very important, the code is managed, each line of code is verified for its rights before excecution.
On one hand, you can create document based solutions with VSTO and, on the other hand, solutions on the application level. You need Visual Studio for using VSTO. Currently VSTO is available in Version 3.0.
For some time, I’m playing a little with this new possibilities. My first goal is to be able to migrate my VBA applications in the near future as effectively as possible. Certainly I will not avoid to rewrite some parts of my code and this would not be done in a few days, as some of my applications are containing more than 50.000 lines of code. So, I was searching the internet for tools for simplifying the migration process. I found the company „Add-In Express“ offering tools for what I was searching.
This company, located in Belarus, offers several add-on products for Visual Studio, including:
- Add-in Express 2009 for Microsoft Office and .NET
This product is the first visual tool for programming Microsoft Office extensions, including COM add-ins, smart tags, RTD servers and Microsoft Excel user defined functions. It completely supports Microsoft Visual Studio .NET (2003, 2005, 2008).
- Add-in Express 2009 for Microsoft Office and VSTO
This product is an extension of Microsoft Visual Studio Tools for Office designed for creating application-level add-ins for Microsoft Outlook, Microsoft Excel, Microsoft Word, Microsoft PowerPoint, Microsoft InfoPath, and Microsoft Visio. This product helps to write plug-ins with less effort and code.
- Specialized solutions
The company also offers specialized versions of their tools for Outlook or the Internet Explorer. And a version for Delphi is also available.
In my case, the product „Add-in Express 2009 for Microsoft Office and .NET“ looked very suitable for me. In this post, I will first describe how to install the product, then create my first COM Add-In for Excel and add a ribbon to Excel 2007. In further articles I will write about my experiences when migrating a VBA applications to VSTO. I used Visual Studio 2008 Team System for my tests. Please note, that in some of the screenshots, you will see the German Version of Visual Studio, as I have not access to the english version.
First, I downloaded the current version from the company’s website, then I registered and I got a product key. The documentation on their website is only available in English for the moment, fortunately the documents are also easy to understand for non native english speakers. The setup application welcomes me with the following screen:
After clicking on „Next“, I should accept the license and then I will reach a screen asking me to select my version of Visual Studio:
In the next step, I can choose a directory where to install the application and finally I’m asked to confirm my choices.
After the installation is complete, I had to activate the product. Please ensure that this application gets access to the internet, may be you have to create a rule for your firewall.
Now it will be interesting what happens when running Visual Studion. Hmm, at a first glance, nothing. Also I couldn’t find a new entry in the Tools menu. Ok, I’m very impatient, so let’s have a look at the PDF provided with the installation (you’ll find it in the new program group created by the setup). This PDF also contains instructions for the first steps and tipps for using the product.
Ok, let’s begin by creating a new project. Add-In Express adds some new templates in Visual Studio. You can find them, if you choose „Other project types – Extensibility“ on the left side of the new projects dialogue. And, as you can see in the picture below, the templates „ADX COM Add-in“, „ADX RTD Server“, „ADX Smart Tag“ and „ADX XLL Add-In“ are available. I choosed the COM-Add-In.
In the next step, I have to choose between some options:
I feel a little better in Visual Basic, so I change the default setting from C# to VB. I think, it’s also a good idea to generate a setup application for the Add-In. And last but not least, I selected Excel for my first test.
I also kept the option „Use version-neutral interop assemblies“ as I would like to use the Add-In in Excel 2007 and previous versions (2000 – 2003). And signing the application could not hurt. After clicking on „Finish“, the project is created.
Now, let’s take a look to the code in AddinModule.vb. You can find there a property ExcelApp(), which allows you to get access to the Excel instance.
I case you have also selected „Word“ in the wizard shown before, you additionally should also find a property WordApp() in the code.
For add a component to the project, we have to open the design view for the module. If you then right click on this view, you get a context menu from where you can choose various components.
After choosing “Add Ribbon Tab”, a new item is placed on the design view and you are able to add new ribbons by clicking on the button in „Controls“ as shown in the following picture:
Now you can add new items by using the window shown below, very easy:
You can use the context menu for adding groups, buttons, checkboxes and many other controls. On the right side of the windows it is possible to setup various settings for the items. I added a button and I had not to forget to implement the click event in my code view later for adding functionality to the button. For testing I just added a MsgBox to the code, displaying „Hello“ in German.
Ok, that’s all. Let’s compile the project and take a look what happens in Excel 2007. Please note, after compilation, we should register the Add-In using „Register ADX Project“ in the Build menu. When you start Excel 2007, you can now see a new ribbon including the button:
Cool, the MsgBox also works. But does the COM Add-In also work in Excel 2003? As Excel 2003 doesn’t know about ribbons, I should first create a CommandBar in the design view and add some entries.
After recompiling the Add-In and opening Excel 2003, you can see that the Add-In also works fine in Excel 2003.
At the conclusion of this first post, although I am still at the very beginning of exploring this product, the potential and opportunities are reallay great. I’m looking forward for more. And what are the costs? Well, the company offers different license types (Standard, Professional, Premium) and the prices are variing between aproximately 350,- and 950,- US Dollar. Compared with other products, this is not too much. And the company also offers different special offers for their products.
This article has also been published in German and in English on the following sites:
- Excel Ticker Blog, German
- Professional Excel Solutions by Maninweb.de, English
- Professional Excel Solutions by Maninweb.de, German