As mentionned in my first post, I would like to share my experiences when porting my VBA applications to .NET. Ok, now after playing with Visual Studio, I had to say that I also have to familiarize myself with some features of Visual Studio. This, even if I’m coming from Borland C++ and later Microsoft C++ (really very long time ago, still someone knows a Borland C ++?).
To start, I intended to migrate my very small application „Hide and unhide objects in Excel“. I expect that the new application would run both in Excel 2003 and Excel 2007. And it would be nice, if I also can use the application in Word and PowerPoint.
Ok, let’s begin. First, I created a new project in Visual Studio. As mentionned in part one of this post, I choose „ADX COM Add-in“ from „Other project types – Extensibility“
The I choose in the wizard „Visual Basic Project“ with the standard options and I select „Microsoft Excel“ as host. Before we continue, let’s have a look on the original German Userform of my VBA application. When starting this Excel Add-In, a new command bar is created and you can open the Userform by clicking on a button on the command bar.
After searching the internet if it is possible to import VBA Userforms to a Visual Studio project, I was unfortunately disappointed. Microsoft itself recommends on MSDN to reimplement Userforms in Visual Studio and create Windows Forms instead. Even there are some ways to convert VBA Code to VB .NET, it should be better to reimplement most parts. And, in addition, many of the functions I developed in VBA are available in standard system libraries of .NET. So, I think, it’s a chance to optimize the code and perhaps perform some redesigns.
Well, let’s create a new Windows Form in Visual Studio. This is very easy, just add a new Windows Form from the project menu and then add the needed controls. When adding a new Windows Form, I selected an empty form from the wizard. Below a picture how my Windows Form looks like:
Now, I create a new command bar in the same manner as described in part one of this post (shortly summarized: double click on „AddinModule.vb“, right click on the design view, select „Add CommandBar“ from the context menu). However, in contrast to the previous article and in contrast to the VBA solution, I want to anchor my menu in the main menu of Excel.
You can programmatically address to the main menu of Excel by using „Worksheet Menu Bar“ as key. So, we have just to enter this key in the appropriate field as shown in the picture above. I also changed the setting „SupportedApps“ for supporting Excel only (the standard setting contains more Office apps), as the main menus in other Office applications have different names. Finally I had to add some items by using the button on the row „Controls“.
As you can see on the top of the window, you have access to a preview of your command bar items. Below you will find some icons for inserting new items. In my example, I inserted the new popup menu „Maninweb“ and then I added the two command bar buttons. If you wish to address a specific existent menu bar entry of Excel (here I choosed „Datei“ the German name for „File“), just type the ID of the entry in the field ID. Of course, you should know the ID, Add-In-Express offers a tool for retrieving them.
At this point I would like to show how easy it is to create new context menu entries for Excel. The only thing I should know is the name „Cell“ for addressing this menu. You can insert a context menu in Add-In-Express by right clicking on the design view and selecting „AdxContextMenu1“. Then proceed in the same manner like for command bar menus to insert new context menu entries.
Ok, what about Office 2007? As I don’t want to see my command bar menu in the Add-Ins Tab of Office 2007, I should ensure that the setting „UseForRibbon“ is set to False. Then, I add a new ribbon control by selecting „Add Ribbon Tab“ from the context menu of the design view. Here, my result:
In a first step, I also change the value of „Ribbons“ to Excel only. Then, I compiled and registered the application and checked if all menus are really appearing in the different apps. Please note, that the new context menu in Excel is available both in Excel 2003 and 2007.
My orginal VBA application implements the events for activating a worksheet and a workbook on application level using a VBA class module. So, we need a similar implementation for our Add-In Express application and we have to add the events component to our design view.
I renamed this componenent to „MIW_Excel“ and then added in my code view the two events „Private Sub MIW_Excel_SheetActivate(ByVal sender As Object, ByVal hostObj As Object) Handles MIW_Excel.SheetActivate“ and „Private Sub MIW_Excel_WorkbookActivate (ByVal sender As Object, ByVal hostObj As Object) Handles MIW_Excel.Workbook
Activate“. My original VBA application also contained a module with a function for handling this events. It was not very difficult to migrate this function to VB .NET.
By the way, you can also insert to your project a component handling events on document level. I’ll give this a try in further posts.
If we open the code view of „AddinModule.vb“, we can see some automatic generated code by the different components. We have just to insert our OnClick handlers, for example for calling our Windows Form.
If we now make a test, we can see the follwing results in Excel 2003, Excel 2007 and Excel 2010, very nice.
As mentionned before, it would be great, if I can also run my application in Word and/or PowerPoint. As Word has its own key name for its main menu („Menu Bar“), I added another menu component to my design view and then created some entries. And I also check the option „Word“ after recalling „Add Application Events“. Last but not least, I had to open my ribbon menu and add „Word“ to the option „Ribbons“.
Please note, that we have not to forget to modify the setting „SupportedApps“ by also adding „Word“. That’s all. Let’s now take a look to Word.
So, the only things I should modify is my function for enumerating the shapes of the documents. Just create a copy and adapt it to the Word object model. If I would like to run my Add-In in PowerPoint I should repeat this steps. As you can see, it’s very easy to develop an Add-In for multiple Office applications. For preparing my next articles, I will try out the XLL, RTD Server and Smart Tags features. Finally some intersting links:
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