<< New site look and features...and added purpose | Source Control workarounds for Excel xla files >>

Moving from an Excel xla add-in to a C# add-in

April 12, 2008 05:29 by author

At my day job, we use Microsoft Excel spreadsheets as a pseudo "specification document" (spec sheet) for the websites, which are actuarial in nature, we create. At the time (several years back), since we chose Excel, obviously we needed an add-in for the few automated processes we supported and we needed something immediately (you know how it goes in small companies). The easiest way for us to create the add-in we needed was to create an Excel add-in file (*.xla). My background (5-6 years ago) was from VB6 anyway, so even though I'd switched to C#, VB6 was still fresh in my mind and writing VBA was a breeze - whether the code was clean or not, I've got not comment ;). I've recently made the decision to migrate an existing Microsoft Excel Add-In (*.xla) file to managed C# code. There were several motivating factors to this decision along with almost as many speed bumps

Why convert?

To me, there were a some compelling reasons to convert along with a few reasons that just turned out to be nice 'bonuses'.

Coding stability - This is probably my most important reason of all. Our add-in had grown in both functionality, size, and importance over the years and had become a pretty large behemoth. I don't really know what causes the problem, but over the last year, Excel has become very temperamental while coding. I can't count the number of hours of work I've lost due to Excel GPFs. These GPFs happened at the worst times...upon clicking File - Save from the VBA editor or Debug - Compile VBAProject (it should go without saying, but I have the Compile On Demand option unchecked, so I think File - Save implicitly compiles anyway). So getting all this code out of Excel and into the more stable Visual Studio environment was a major driving factor. Additionally, some of the times after a GPF, Excel would corrupt my *.xla file and I could no longer open it. To alleviate this 'pain', every time my add-in was saved, it did a little local 'versioning'. I automatically saved a 'cycle' of 10 copies of my *.xla. I did this so that if I was working on the file for an extended period of time before actually checking in a valid copy into a source control tool (we use Visual SourceSafe), I was protecting myself so that I could at least recover to the last save action instead of rolling all the way back to the last version in source control. Of course, to aid in this process, you have to 'train' yourself to hit Ctrl-S (save) about every 15 minutes. That was fun (sarcasm) and productive (the *.xla had grown to 2MB, so saving, renaming, etc. too several seconds).
Version Control - By default, since *.xla files are binary files, they can be managed in a source control tool but you can not view differences for the file (let alone the VBA code files) from version to version. To get around this, you could export each of your code files and check them into source control each time you have a revision. Of course you'd want to do this in an automated fashion. I added a menu to my add-in to automatically checked out the VBAProject files, exported files from Excel, and check files back into SourceSafe. The only draw back to this automation is that if you have worksheets in your *.xla and they contain code, you can not export the code from these files (or at least I never bothered to figure out how since the worksheets in my *.xla do not contain code).
Code Security - A low priority for our company, but *.xla files makes viewing your code / intellectual property available to everyone. Even if password protected, your *.xla is not secure. There are Excel password crackers that will destroy that false sense of security in seconds. Of course, if your add-in is in C# (like mine is going to be), you'd have to obfuscate your code to protect it from Reflector but that is beyond scope of this post (and my knowledge).
Development Experience - As I said, I was a VB programmer for a long time before I switched to C#. I don't have the links to the documentation now, but when .NET came out, I vaguely remember 2-3 things you could do in C# that you couldn't in VB.NET, so I decided to move to C# then to get the 'full' benefit of the .NET CLR. I'm not sure if that is still the case, but I have to say that the C# language seems so much cleaner than the VB/VBA code I used to use or the VB.NET code I've peeked at a couple of times. Let's not turn this post into a religious war of C# vs VB.NET. I just like the syntax of C# better. But for you VB'ers that went on to VB.NET, I'm willing to bet you like VB.NET better than legacy VB/VBA syntax. Disregarding your language syntax preference, comparing Visual Studio (especially 2008) versus Excel VBA Editor (and MS Forms...I think that's what they are called) is hardly worth mentioning. The richer, more productive experience you will get in the former compared to the later is laughable.
Better support for Office 2007 - Excel 2007 (the entire Office suite) introduced a new UI that was dramatically different than previous versions. You can find a pretty entertaining, albeit long, presentation given by Jensen Harris that is very informative. With the limited Google-ing I did, I'm not sure an *.xla add-in could even manipulate the ribbon, but even if it could, I think it will be supported much better in managed code.

Okay, you've convinced me, now what?

If you've made it this far and agree with me that you want to get out of *.xla add-ins, you have to decide the architecture/frameworks you are going to use. To develop the add-in you have a few choices:

Excel dll add-in (*.xll) - This is a C++ dll that...ah hell, I know nothing of C++ and you shouldn't either based on the title of this blog. This option is out. (If you really do want to read up on this, you can view the Developing Add-ins (XLLs) in Excel 2007 article at MSDN...good luck!)
Create a COM add-in using Visual Basic 6 - To do this, you'd probably have the 'fastest' migration seeing that all of your existing VBA code would port directly to VB6 AFAIK. For an example of how to do this, you could read a nice article by Chip Pearson.
Create a COM add-in using C# / VB.NET - Since one of our reasons for converting was to use the Visual Studio environment, minimally, we'd want to choose this path. MSDN has an article explaining how to do this: How to build an Office COM add-in by using Visual C# .NET.
Visual Studio Tools for Office (VSTO) - If you Google for "Excel add-in C#", almost all your page hits are going to revolve around VSTO. Admittedly, I had no experience with VSTO at the time of my decision, but given its popularity and looking at the VSTO portal, it was obvious to me that this was a 'hot technology' and Microsoft seems to be putting a lot of effort into it. I assumed this would be the route I would take. You can find a very informative 'review' of VSTO from 'Mike R' on the Xtreme Visual Basic Talk website.

So there were my choices, and as I mentioned, I immediately chose VSTO and built a simple 'Hello World' add-in (CommandBar and message box) with the Excel 2003 Add-In template taking all the default settings and viola, I had my first C# add-in ;)

The architecture I chose is...

For anyone that has done any automation of Excel or regular COM Interop to Excel, you know that performance can come to a grinding halt when you start accessing the COM objects in a repetitive fashion. This was one of the reasons for our original *.xla in the first place. To delegate the repetitive COM object access calls via 'one automation/interop call' back into the *.xla and then let it internally do all the looping. For an more information on this performance degradation, read another one of Mike R's articles. So given this knowledge, I knew that I was not going to be able to put 100% of my code into C# and I had get a plan of attack for this.

To eliminate (or at least drastically reduce) the performance problem with automation/interop, I knew I was going to have to have a legacy VB6 COM dll in the picture. Obviously I want to reduce the amount of code in this library to only repetitive COM object access code being called during a performance sensitive process. I estimate that about only 5-6% of my code will end up in the library. With that, we get something like the following:

Basically, the automation client will instantiate an Excel Application which in turn, loads its add-ins. The line between AutomationClient and CSharpAddIn does not 'really' exist. The automation client always talks with Excel, but it can call add-in methods via the Excel.Application.Run method. Then our C# dll would reference the VB6DLL library and delegate all heavy COM object processing to it. So whether our add-in is invoked via Excel automation or directly from within Excel by a user, we have the VB6DLL COM library at our disposable to overcome any performance issues that automation/interop imposed on us.

So where are the speed bumps?

So everything sounded great so far. I was going to prototype some of the different functional aspects our current *.xla add-in to prove that the C# add-in could accomplish the same things. Of course things are never as easy as they appear. Using the VSTO template, I ran into several hurdles. One being even before I had my 'Hello World' version working. C# add-in assemblies must be granted full trust in order to work. I didn't know this when I created my simple add-in and ran the included setup project in the solution. At first, the setup completed and when I ran Excel, 'nothing' happened. I didn't see any new CommandBar objects or any failure messages. I found an article from Misha Shneerson that eventually led me to the solution - that the included setup project does not grant trust by default. The most important pieces of information that I got from this article were setting the VSTO_SUPPRESSDISPLAYALERTS environment variable to 0 (once I did this I indeed got a dialog complaining about requiring full trust) and a link to Mad Nissen's (I'm guessing at last name) custom installer class to add/remove full trust during install/uninstall. So 'technically', after all this I had my Hello World add-in working.

Note: I came across this article as well from MSDN that seems to address this same issue and explain how to get the setup project customized to grant trust. But the easiest walk through for this was written by Thomas Tingsted Mathiesen who dummied it down for all of us.

The next hurdle I had is that VSTO setup project installs the add-in only for the current user. Our requirement was that it installed for all users on a machine. I don't remember how I found this so quickly in my prototyping, but fixing the setup project was easy enough...just cut and paste the 'registry' entries from HKCU to HCLM.

Now after running the setup, the add-in loaded and functioned properly, but I could not find my add-in within the Tools - COM Add-Ins... dialog in Excel. I Googled around and found this article that explained it was designed behavior. So even though it will not show up in the COM Add-Ins dialog in Excel 2003, I'm declaring that acceptable. So thus far, I'd spent the better part of a day floundering through a couple of issues that should have been no brainers and I hadn't even started writing any code to test my architecture. Now this is where the show stopper emerged...

I made my VB6DLL library with a simple public class/method exposed for my C# add-in to call. Just simple loop:

   1: Public Sub Test(ws As Excel.Worksheet, cnt As Long)

   2: Dim i As Double

   3: Dim j As Double

   4: Dim r As Excel.Range

5:

   6:     For i = 1 To 50

   7:         For j = 1 To cnt

   8:             Set r = ws.Cells(j, i)

   9:             r.Value = i * j

  10:         Next

  11:     Next

  12: End Sub

View Plain | Copy To Clipboard

However, upon calling this from my VSTO C# add-in, I received an "Class does not support Automation or does not support expected interface" error on the Set r = ws.Cells() line. But what was even more strange was the fact that when I examined the ws object in the Watch Window I got the following results:

So immediately I was concerned with the marshalling of the Worksheet object from C# to VB6 since some properties worked and some didn't. I tried changing the declaration type to 'object' and that didn't help matters at all. This is the point I discovered the Xtreme Visual Basic Talk website and I would strongly recommend this site to anyone. Reading several of the posts, there seem to be a few good 'experts' that are more than willing to provide you with excellent information. If you want to read the thread regarding this issue, you can view it here. It turns out there is some sort of marshalling issue with VSTO. I don't know exactly the reason, but I think it has to do with the VSTO add-in loader that creates a separate AppDomain for each loaded add-in. I was able to prove VSTO was the culprit (event if I didn't fully understand why) by simply creating a COM callable C# library that referenced my VB6DLL library. I could then successfully reference the COM callable C# assembly from Excel VBA and call a method that in turn called the VB6DLL Test method.

So where did I end up?

After 3-4 days of floundering, I finally was able to create the add-in architecture that I wanted (note that I have not actually done any of the actual porting code yet...doh!) and in regards to how to accomplish it, unfortunately at this time I was going to have to eliminate VSTO from my toolbox and just create a C# add-in the old fashioned way. When I do get to implementing an Excel 2007 version, I am hopeful that I can either programmatically manipulate the ribbon pretty easily (as opposed to being dependent upon the VSTO designer) or simply use the VSTO designer to create a layout then export some XML/code that I can then 'just use' in my non-VSTO implementation. Even Mike R, who at first was praising VSTO, is now a little leery about how promising VSTO is going to be:

I should clarify my experience with VSTO: I have yet to use it other than for a few test projects. But I do forsee using it in a future project for Excel 2007 only because I just love the visual Ribbon designer, but otherwise I don't have a need for it. Given the problems you highlighted here, I'm less sure that I'll be using it myself.

In summary, even after all this I am still happy and excited to migrate off of the old *.xla technology and into primarily C# code. If you've made it this far, I hope I've provided you with some gotcha's (obscure as they might be) to watch out for when coding a C# add-in along with some good reasons to possibly migrate your *.xla add-ins should you feel up to it!

Currently rated 3.6 by 22 people

Currently 3.636363/5 Stars.
1
2
3
4
5

Comments (21) | RSS comment feed

Comment RSS

Source Control workarounds for Excel xla filesPreviously, I mentioned that I automated getting the code files out of the Office VBAProject and int...Office Is Finally Done!The things you'll do for love...

Comments

April 14. 2008 20:18

Hi Terry,

What a superb article. You covered a ton of high-level material.

I just want to clarify what I wrote about my experience with VSTO and my likelihood to use it in the future. My experience so far is with VSTO are few testing projects with which all went extremely well. The Visual Ribbon Designer is a killer-feature that makes you want to use VSTO and not look back.

That said, the Ribbon only exists for Excel 2007+ and I think that VSTO can only work with Excel 2003 and above. So for myself, I consider VSTO an Excel 2007 product because other than the Visual Ribbon Designer, I not personally have much need for it.

VSTO's other killer-feature is that you can utilize standard .NET Windows Controls on an Excel Worksheet using a drag-and-drop design time environment. It is very similar to how VBA works currently with Forms controls and ActiveX controls execept that the code-behind for VSTO is either VB.NET or C#.

On the other hand, I had never tested VSTO using calls across the AppDomain as you have been kicking around and the results were poor -- and hence, my quote! However, I'll say two things with respect to that:

(1) I am generally unconcerned about execution speed for VSTO and do not generally have a need for "high-speed" operations except for my user-defined worksheet functions and so for my projects. I would simply use VSTO for the top-level Ribbon and CommandBar commands, but create a separate add-in without VSTO for my worksheet functions, which need to run fastAnd I do not have a need for having these two add-ins communicate -- they are separate functionality, even if they work together as a package. (You have a need for speed, however, and so the desire for cross-AppDomain calls is warranted.)

(2) The problems you and I stumbled on could actually be easily avoided: your VSTO add-in could call your VB6 add-in without any trouble. The catch is that you simply need to avoid passing Excel objects such as Range, Worksheet, etc., across the AppDomain. So you could pass in String or Double data types and return the same. Or even an entire Array of values. If you *need* to communicate a Range, then do it by passing in a String address via Range.Address(External:=True) or the like. Similarly, for a Workbook or Worksheet, pass such values by name only, not as objects. If you do this, then you'll have no troubles.

Anyway, so, yes, the experiments we performed did give me pause with respect to VSTO, but only because we were failing so badly with the cross-AppDomain call issues. Overall, though, in my opinion, (1) VSTO does too much else superbly to avoid it just for this, and (2) done right, these cross-AppDomain calls should not be too much trouble after all.

Anyway, a really outstanding article Terry, I've now printed it out so that I can read it a few times...

Mike

Mike Rosenblum

April 15. 2008 05:14

Thanks Mike. Thanks for clarifying your thoughts. I 'knew' that is what you meant, but I guess I was too tired to convey it correctly while writing this article ;)

Anwyay, another 'possible' upside for VSTO and Office 2007 is that maybe 2007 doesn't have this 'weird' stuff going on that interferes with C# -> VB COM calls. I found this article last night at dreagis.com (http://www.dreagis.com/PermaLink,guid,04e5c576-4516-4eaf-ba8d-6be1204bb323.aspx) that says 2007 loads it a bit differently than 2003. So I'll have to test 2007 to see if it marshalls objects properly.

As for your comment:

The catch is that you simply need to avoid passing Excel objects such as Range, Worksheet, etc., across the AppDomain. So you could pass in String or Double data types and return the same. Or even an entire Array of values. If you *need* to communicate a Range, then do it by passing in a String address via Range.Address(External:=True) or the like.

I don't see how this would work. If I pass the address of the cell I want, how would the VB6COMDLL be able to 'communicate' with Excel? I mean the C# add-in would be passed a reference to the ExcelApplication object during OnConnection (or whatever that method is), but if the Excel objects aren't passed to the COM dll, I don't see how it could communicate with it - i.e. grab a Range object given an address.

Terry Aney

April 15. 2008 11:54

Hey Terry,

Yes, you hit on the right issue when you wrote:

"How would the VB6COMDLL be able to 'communicate' with Excel? I mean the C# add-in would be passed a reference to the ExcelApplication object during OnConnection (or whatever that method is), but if the Excel objects aren't passed to the COM dll..."

The answer is that the VB 6.0 COM DLL would have to pick up its own Excel.Application reference sparately! That is, both the VB 6.0 COM DLL would have to be an actual COM Add-in implementing the IDTExtensibility2 interface and then the Automation client would access the addin via the Excel.Application.COMAddins collection and then access the COMAddin.Object property, and then cast to the class interface type exposed by your VB 6.0 COM DLL. I've not tested this using VSTO, but the following works for out-of-process Automation and so I'm highly confident that this should also work for VSTO, which is not working out-of-process, but *is* operating across AppDomains.

As an example, create a new VB 6.0 Addin project, add a reference to Microsoft.Excel, and then use the following code within the 'Connect' class:

------------------------------------
Option Explicit

Dim xlApp As Excel.Application

Private Sub AddinInstance_OnConnection( _
ByVal Application As Object, _
ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _
ByVal AddInInst As Object, _
custom() As Variant)

On Error GoTo RTE
Set xlApp = Application
AddInInst.Object = Me
Exit Sub
RTE:
MsgBox "MyAddin.OnConnection(): Run-Time Error!"
End Sub

Public Sub SomeMethod(ByVal inString As String)
MsgBox "SomeMethod(""" & inString & """) called!"
End Sub

Public Sub Range_SetValue(ByVal rngFullAddress As String, ByVal newValue As Variant)
xlApp.Range(rngFullAddress).Value = newValue
End Sub
------------------------------------

Once created, you can then make an Automation client using C# with the following code:

------------------------------------
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;

namespace csExcelAutomation
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
Excel.Application xlApp = new Excel.Application();
xlApp.Visible = true;

Excel.Workbook wb = xlApp.Workbooks.Add(Type.Missing);
Excel.Worksheet ws = (Excel.Worksheet) wb.Worksheets[1];
Excel.Range rng = (Excel.Range) ws.Cells[1, 1];

object progId = "MyAddIn.Connect";
Office.COMAddIn comAddin = xlApp.COMAddIns.Item(ref progId);
MyAddIn.Connect iMyAddin = (MyAddIn.Connect)comAddin.Object;

iMyAddin.SomeMethod("Hello");

string rngAddress = rng.get_Address(
Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1,
true, Type.Missing);
iMyAddin.Range_SetValue(rngAddress, "Hello");

MessageBox.Show("Ready to exit?");

GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();

Marshal.FinalReleaseComObject(rng);
Marshal.FinalReleaseComObject(ws);

wb.Close(false, Type.Missing, Type.Missing);
Marshal.FinalReleaseComObject(wb);

xlApp.Quit();
Marshal.FinalReleaseComObject(xlApp);
}
}
}
------------------------------------

The above will open Excel, create a new Workbook, then call 'iMyAddin.SomeMethod("Hello")' and then set the value of cell A1 to "Hello".

Give it a try, I think that this is what you are looking for. That said, again, I've not tried this where VSTO is the client, but I think it should run just fine.

,
Mike

Mike Rosenblum

April 16. 2008 00:36

Ah yes, I see how that would work. If you mentioned a second add-in on your original comment I apologize. Right now, I'm wanting only one add-in, but if writing two and allowing communication under VSTO works, then that should definately be an option. After I test it all out, I'll add that as a choice a user can make when creating the addins.

Terry Aney

April 16. 2008 08:56

Oh sorry, yes, I wasn't too clear about that in my first post above; I didn't mention that the callee would also need to be an add-in. But, yes, the VB6 DLL would need to implement IDTExtensibility2 so that it could pick up its own Excel.Application instance.

The code example above worked on my machine using out-of-process Automation, but you'll have to test it using VSTO. I do believe that it should work fine, but I'll keep my fingers crossed for you...

Mike Rosenblum

April 17. 2008 11:44

I don't know if this will help, but there's one other experiment you should try to make sure it isn't causing troubles for your scenario.

For Excel C# add-ins, we have this thing called the LCIDProxy that wraps the Excel object model and makes sure that it works the same in all locales that you run in.

You might want to try passing things to your VB6 library without the LCIDProxy. So the way you would do this in VSTO 2005 SE is remove these lines of code in the Startup event and try without the proxy being used.

#region VSTO generated code

this.Application = (Excel.Application)Microsoft.Office.Tools.Excel.ExcelLocale1033Proxy.Wrap(typeof(Excel.Application), this.Application);

#endregion

Eric Carter

April 17. 2008 19:02

Hey Terry,

Why go to xtremevbtalk if you could have just contacted VSTO product team i.e. myself. I noticed you have already found my blog

I think Eric is right on the money about the failure. But you can always contact me directly using the email link on my blog cause I love this kind of puzzles.

Misha Shneerson

April 17. 2008 19:36

Thanks Eric and Misha...

I'll definately be trying that out in next day or so (swamped at the moment). Misha, I guess since a blog isn't a 'forum' I didn't feel right posting a 'big question' on a random post. But you've opened up the flood gates now ;) If I have any questions, I'll definately be sending you an email!

Thanks again.

Terry Aney

April 20. 2008 22:55

Eric,

Thanks for the tip. You are correct. Commenting that out made everything work. So what am I giving up by commenting that line? I'll only work in en-US locale?

Terry Aney

April 22. 2008 19:42

If you only work on ENU system - you do not loose anything - in fact, you will gain significant performance. The purpose of the wrapper is to pretend it is just another Excel's object, intercept all calls on it, analyze the returned values and wrap returned objects by a similar wrapper if it belongs to Excel's OM. As you can see this is pretty heavy machinery that is there to take care of an obvious adoption blocker of running managed code on non-ENU systems (see blogs.msdn.com/.../453680.aspx).

For those who do work on non-ENU systems but would like to get the naked Excel pointer from the wrapper in scenarios like the one described in this post - you can always use the ExcelLocale1033Proxy.Unwrap method (even though it is marked as 'obsolete' in VSTO 3.0).

Misha Shneerson

June 9. 2008 23:02

You may find my (ongoing) series of use at:

ausdotnet.wordpress.com/.../

Matthew Wills

July 3. 2008 16:57

How can I prevent the loading of page with "Trust not granted" message, and load a particular page?

how can I check whether or not the "Trust not granted" page will display?

can you help me?

Thanks in advance,
Rajith.

Rajith

July 4. 2008 21:03

Hi, Aney
May I ask you one question?

hi, there
I created C# automation excel add-in named "MyExcelAddin" like that link.
http://msdn.microsoft.com/en-us/library/ms173189

"MyExcelAddin.MyClass1" contains one function:
public string GetName()
{
return "Employee";
}
I can use this function in excel like that.
In Selected Cell , =GetName(). It works well.

So, I try one thing like that
I created a DLL named "MyDBDLL" that connected directly with MSSQL database server.
"MyDBDLL.MyClass2" contains one function:
public string[] GetNames()
{
string[] srtArr;
//....my code are here...
// i assigned strArr names from MSSQL database
return strArr;
}
I also created XML file for "MYDBDLL" dll.
And in "MyExcelAddin.MyClass1" , I change my code like that
public string GetName()
{
MyDBDLL.MyClass2 CL2 = new MyDBDLL.MyClass2();
string[] st = CL2.GetNames();
return st[0];
}
At that time, In excel , I cannot use like that .
In Seletced Cell, =GetName(). It gived no result.
But I test "MyExcelAddin" DLL in Console Application. It works well.
How can i solve this problem...? Please help me.
Thanks You Very Much.

kkzin

November 10. 2008 18:26

Is there a way to add a menu to Excel with sub menus in it ?
thank you in advance.
Friendly Yours.
Lotfi

phoenix

November 23. 2008 01:55

Terry,

I'm another dev in the VSTO team (a colleague of both Eric's and Misha's). As a point of interest, this issue has been fixed in the VSTO Runtime since version 3.0 which supports Office 2007 and later and was first available for developers in Visual Studio 2008.

The bug does, unfortunately, still exist in the VSTO 2005 Second Edition Runtime which is the runtime provided to support add-ins in Office 2003. I'll reiterate Misha's recommendation: disabling the Excel locale proxy is the best workaround for this issue in Office 2003 add-ins using the VSTO 2005 SE Runtime.

Thanks, Richard.

Richard Cook

November 23. 2008 01:58

Rajith - I'm not 100% sure what you are asking. I've never really had to protect against that, but under Tools-Macro-Security there are the trust settings. You can either trust all add-ins otherwise get the addin digitally signed and have users trust the appropriately signed add-in. Is that what you were asking?

Terry Aney

November 23. 2008 02:05

Lotfi - Adding menus to pre Excel 2007 is different than the 2007 Ribbon bar. I've only experience with pre 2007 and that is pretty straight forward.

For example in VBA it would be something like:

Dim menu As CommandBarPopup
Dim subMenu As CommandBarPopup
Dim menuItem As commandBarButton

Set menu = Application.CommandBars.item("Worksheet Menu Bar").Controls.Add(msoControlPopup, , , , True)
menu.Caption = "Top Menu"
menu.Visible = True

Set menuItem = menu.Controls.Add(Type:=msmenuItem)
menuItem.Caption = "Menu Item"
menuItem.OnAction = ThisWorkbook.Name & "!MacroName"

Set subMenu = oCmdBar.Controls.Add(msoControlPopup)
subMen.Caption = "Sub Menu"

Set menuItem = subMenu.Controls.Add(Type:=msmenuItem)
menuItem.Caption = "Sub Menu Item"
menuItem.OnAction = ThisWorkbook.Name & "!SubMacroName"

If you are talking C#, I'm sure you can translate those calls into the appropriate Interop calls.

Terry Aney

November 23. 2008 02:19

kkzin - Admittedly, after going through this initial battle of researching the add-in framework I wanted to write, I unfortunately have been swamped at work and migrating the add-in to C# hasnt' been permitted. So I'm a bit rusty on all the information I gathered before. I'll probably have to run this whole experiement again as I've been notified that VSTO has been updated and maybe behave itself more favorably towards my goals.

As for your code, everything appears as though it should work. Have you ran your add-in in debug mode and stepped through to make sure st[0] is valid element?

Also, are you given a blank result or an error is throw? You should put some Trace lines in there to see.

My 'only' guess is that somehow all the assemblies of your application/add-in weren't properly updated and Excel has a bit of a mismatch going on.

Maybe some VSTO team members could pipe in on common 'updating' problems.

Note, the link you referenced has been 'retired' :O Not sure what that means.

Terry Aney

June 5. 2009 23:52

I have difficult experiences trying to protect excel codes and workbooks. I have made a com add-in but was not satisfied as it seems to be sluggish. Have any one here tried DoneEx XCell Compiler. I would like to hear if it is really helpful against vba crackers?

nelson

June 5. 2009 23:55

In my experience converting to com add-in entails some changes in object declarations and treatment as the resulting add-in is basically extrinsic to Excel.

nelson

June 24. 2009 16:43

... This option is out. (If you really do want to read up on this, you can view the Developing Add-ins (XLLs) in Excel 2007 article at MSDN...good luck!) Required

Thats not actualy true. It is indeed very easy to create xlls with C# using xlw4 as the following clip shows

http://www.youtube.com/watch?v=1yLodcb32sI

xlw4.0.0b0 can be downloaded from sourceforge

AlexesDad

Search

Calendar

Su	Mo	Tu	We	Th	Fr	Sa
28	29	30	1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31	1
2	3	4	5	6	7	8

Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

Aney Adventures Online

Moving from an Excel xla add-in to a C# add-in

Why convert?

Okay, you've convinced me, now what?

The architecture I chose is...

So where are the speed bumps?

So where did I end up?

Related posts

Comments

Search

Calendar

Archive

Tags

Categories

Blogroll

Disclaimer

Su	Mo	Tu	We	Th	Fr	Sa
28	29	30	1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31	1
2	3	4	5	6	7	8

Su	Mo	Tu	We	Th	Fr	Sa
28	29	30	1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31	1
2	3	4	5	6	7	8

Su	Mo	Tu	We	Th	Fr	Sa
28	29	30	1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31	1
2	3	4	5	6	7	8