[taney]

I am trying to move an *.xla add-in to 'some form' of a managed COM Add-In. Through trial and error, google searches, etc. I've realized that migrating the add-in to an 'entire managed COM Add-In' is not going to work.

I think I ended up at something similar to a quote by Mike in (http://www.xtremevbtalk.com/showpost...&postcount=3):

So I am using .NET for all versions of Excel, but I am only using VSTO for Excel 2007 and above. I also use a smattering of VBA and VB 6.0 in all versions as well -- sometimes these "older" technologies are still useful and can execute far faster, because they do not have to work through the .NET Interop. That is, they execute as COM add-ins natively. Where speed counts, VBA and VB 6.0 is still the way to go.

What I thought I'd architect is:

C# Managed 2003 COM Add-In - This would create UI and all functionality that is not 'heavy' on Excel object access.

VB6 DLL - All code that required 'heavy' Excel object access would be placed in here. But the goal would be to minimize the amount of code in this DLL as I'm more proficient in C# now than VB - I've chosen to forget it I guess .

So with this architecture the C# Managed Add-In would reference the VB6 DLL and make calls to it and pass in either a WorkSheet or WorkBook object and then have 'one interop' call to the VB6 DLL, and then the VB6 DLL could do as many Excel object calls as it wanted since it was 'in-process' and not working over COM Interop. There were several reasons for the move to VBA (which I could explain), but the end result is that I wanted as much code in C# without sacrificing performance and I thought this architecture solved that.

So below are some code snippets I was trying to make work to prototype what I wanted to see if it was even feasible to move in the direction I wanted. The problem was I get a 'weird COM error'.

C# Menu Handler:

var sheet = Application.Worksheets[ "Sheet1" ] as MsExcel.Worksheet;
var tester = new ExcelVBComTest.VBComClass();
tester.Test( ref sheet );

VB DLL Code

Public Sub Test(ws As Excel.Worksheet)
Dim i, j As Double
Dim r As Excel.Range

    For i = 1 To 50
        For j = 1 To 10000
            Set r = ws.Cells(j, i)
            r.Value = i * j
        Next
    Next
End Sub

I get an error on the call to ws.Cells: "Class does not support Automation or does not support expected interface"

If I add ws to the watch window in VB6, some properties are working on the ws but other properties (seems to be ones that return other 'COM objects') fail with same error.

So if anyone's made it this far, I really appreciate it and any suggestions are greatly appreciated.

[Mike Rosenblum]

Hi taney,

Welcome to the forum.

Your question brings up a number of interesting issues, and I'm not *sure* that you'll love the approach that you are trying, but it certainly workable and would have the best performance. What I find a little odd here is that you are ostensibly creating a ".NET solution", but really putting most of the code in VB6. But it certainly will work, and the same commands called from C# can be very awkward especially where the Excel object model returns a Variant (which comes through as Object in .NET) or were optional parameters are concerned, such as with the Range.Address, or where you have a property that has parameters such as Range.Value. It does not take long for one's C# code to look like a mess. :-(

I personally use C#, but I wrap every call and then call my own version. You only have to do this once, which is nice and with Extension methods this can be really nice. However, keep in mind that if you use Extension methods or other aspects of the .NET 3.5 Framework then you are limiting your operating system to be running on Vista or Windows XP SP2. (The code that you show uses the 'var' keyword, so you seem to be already targeting .NET 3.5, so maybe this is okay -- or maybe you do not realize the operating system restriction?) Personally, I cannot use .NET 3.5 for professional use yet as I cannot limit my operating system like this.

I am currently working on a .NET project where "clean code" is at a premium and so I elected to use VB.NET for this. This way all the calls are effectively "as intended" by the Microsoft Office object model without having to make your own custom wrappers. The only issue I have with VB.NET in this situation is that casting via CType() or DirectCast() is an ungainly syntax, but otherwise VB.NET is much cleaner than using C# when using Excel.

Your approach to "cleaning up C#" is to effectively not use it by pushing the actual Excel object model code to VB6. This is fine -- and will actually run faster, but in most cases I doubt you'll notice the speed increase and you might find this approach ungainly in the long run. I'm not sure. However, for the code example that you show, iterating over the cells one-by-one is very slow even in VBA / VB6 doing this and will be much slower still using .NET. So executing the code within VB6 could make sense here. However, it is much faster to manipulate an array of data when dealing with ranges instead of going cell-by-cell. Here are two examples:

(1) http://www.xtremevbtalk.com/showthre...49#post1148949
(2) http://www.dotnetforums.net/showthread.php?t=89304

As for why your VB6 code is not operating, I really do not have a clue! I assume that your project includes a reference to Excel? Are both the VB6 and C# versions both referencing the same Excel verion in their references? Could you try putting in a 'MsgBox(TypeName(ws))' call within your failing VB6 method and see what type is coming through here??

This is about all the thoughts I have for now, I hope this helps!

Mike

[taney]

Thanks Mike. Most of your concerns you mentioned I am aware of and are point well taken. Just to be clear on a couple things.

“What I find a little odd here is that you are ostensibly creating a ".NET solution", but really putting most of the code in VB6”

– I’m not putting most of the code in VB6, only parts of the code where looping Excel Ranges are required (which is only about 10% of my code). And I’m also familiar with the ‘array access’ method as well regarding Ranges, but as I’m sure you know, if access isn’t contiguous, you can’t use it…and that’s where my code falls in. The sample code I showed you was purely for performance comparisons against internal Excel VBA. When Excel VBA references that COM dll and calls the method (taking Managed Add-In out of the loop), it ‘barely’ beats VBA. I was hoping for better performance, but as long as it doesn’t degrade I’m happy with it.

“Your approach to "cleaning up C#" is to effectively not use it by pushing the actual Excel object model code to VB6.”

– Again, just to confirm, I’ll be using the Excel object model from C#, but only when a ‘handful’ of access methods are needed (as opposed to looping) and yes, I’ve written helper methods and extension methods.

Finally as for you thoughts on ‘real’ issue…can I start crying here Like I said in original post, it’s almost like a ‘partial’ type is coming through, but here is some output. Notice in the watch window how some properties work and others don’t – see the <Class does not support Automation or does not support expected interface> in the Value column (even the ones that don’t work have the proper type).

Immediate Window:

?TypeName(ws)
Worksheet

Watch Window:
See watchwindow.png attachment (is there a way to show that inline?)

So I guess the bottom line is: You said you had add-ins with Managed Code and VB6 and VBA sprinkled in as well. If you aren’t doing the same ‘framework’ as me, how are your ‘layers’ arranged and how does the Managed Code communicate to either VBA or VB6?

[Mike Rosenblum]

I've done this kind of thing a lot, so it should be no problem. I've used a VB6 COM DLL implementing IDTExtensiblity2 as my "front end" which in turn calls my .NET Library, which is exposed to COM. I've also gone the other way where the .NET Library is exposed to COM and implements IDTExtensibility2 and calls a VB6 DLL. Either way should be fine.

In your case, which is the "front end"? VB6 or .NET? Let me know which way you are going and I'll try whipping up a simple example and see if we can't get this working...

My best guess right now is something along the lines of the references not matching or a PIA issue or something like that. But it's only a guess at the moment.

[taney]

I think I figured it out. Or at least I think I figured out why it doesn’t work. When you use VSTO, it creates a new AppDomain to host your Add-In in for 'stability purposes'. I wanted to find the article I found last night as a reference link but alas, I can’t. Anyway, I think there is some sort of marshalling problem when trying to send the Excel objects to the VB6 COM dll in that new AppDomain. Reason being…after making the VB6 COM dll, let's call it VB6.dll, I get the following behavior:

• VBA referencing VB6.dll works.
• VBA referencing a C# COM Exposed assembly (not an add-in) that in turn references VB6.dll, all works as well.
• Managed COM Add-In created via VSTO referencing VB6.dll does not work.

I’m assuming since you are creating a .NET Add-In by implementing IDTExtensibility2, you are not making a new AppDomain and your scenario behaves identical to my case #2 above. So it has something to do with VSTO and loading a new AppDomain or ‘something else’ VSTO does, but something gets dinged up. So I’ll probably go about creating a C# Add-In implementing IDTExtensibility (any articles or boilerplate classes you can direct me to? I’m sick of googling

But my question to you is:

1) Where you are using VSTO (Excel 2007 for Ribbon support only I think you mentioned), do you references and VB6 dlls? If so are you passing in Interop objects? I’m assuming/hoping the answer is no, otherwise my theory is blown out of the water.

2) Assuming the answer is no, how useful/required is VSTO in creating Ribbons? I do plan to make an Office 2007 ‘interface’ (part of the reason for moving to VSTO vs *.xla) sooner rather than later, and wondering if I can just do it programmatically versus using the designer you have mentioned.

3) Have you heard of http://www.add-in-express.com/add-in-net/ ? Any comments about them? I read the first paragraph from here http://www.winterdom.com/weblog/2007...ousThings.aspx but don’t know how much truth there is to ‘beats the crap out of using VSTO’. I’ll probably contact that author for an opinion as well.

Anyway, any comments/confirmations/sample links are greatly appreciated.

[taney]

Originally Posted by taney

Have you heard of http://www.add-in-express.com/add-in-net/ ? Any comments about them? I read the first paragraph from here http://www.winterdom.com/weblog/2007...ousThings.aspx but don’t know how much truth there is to ‘beats the crap out of using VSTO’. I’ll probably contact that author for an opinion as well.

So I've gotten a response from him:

It does a bunch of things:

• Has better deployment story
• It allows you to create custom panes in several outlook versions and other applications (not just on 2007 like the latest VSTO)
• It has great support for both regular command bars and the ribbon (even both at the same time if you need to support both office 2007 and earlier versions)
• Doesn't force you to muck around with some nasty AppDomain issues that VSTO forced at times

And it has a bunch of other stuff. Seriously, it's far far easier to use than VSTO. I haven't done office programming in a while, but it seriously impressed me when I used it.

[Mike Rosenblum]

Ah-ha! I think you are right!

I was starting to suspect the same kinds of things, except for the wrong reasons. As I wrote you last time, I suspected that the PIAs might be getting messed up or the like. But I was starting to think that you might be automating, which would create cross-process calls and could be the source of things.

Ok, yes, VSTO "shims" itself by running within a separate AppDomain. This protects against other assemblies that are running from calling Marshal.ReleaseComObject() and/or Marshal.FinalReleaseComObject() which terminates these objects for all processes within the same AppDomain.

I'm not sure what's goin on with the direct VSTO --> VB6 calls. It seems that you are right about the AppDomain issue. Bummer. I tried making a project myself, and the result created some duplicate references to 'Microsoft.Excel' and 'Microsoft.Office' from adding in my VB6 DLL as a reference, but once I removed them, it compiled and ran. However, it did not run right.

I modified your test code slightly to:

Public Sub Test(ws As Excel.Worksheet)

MsgBox ws.Name

Dim i, j As Double
Dim r As Excel.Range

For i = 1 To 3
For j = 1 To 3
Set r = ws.Cells(j, i)

MsgBox r.Address

r.Value = i * j
Next
Next
End Sub

In my case, the call to 'MsgBox ws.Name' worked fine, but the calls to 'MsgBox r.Address' and 'r.Value = i * j' appeared to be ignored. The odd thing is that it did "run": I could see it blinking, etc. It definately was running. Or at least it *thought* that it was running! Unfortunately, there was no visible output. :-(

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.

VSTO is really big advantage in two scenarios, but is very big if you need it:

(1) If making Worksheets or Workbooks that are visible to the user with controls on it, then VSTO can host standard .NET Windows Forms controls. Really nice. :-)

(2) The visual drag-and-drop Ribbon Designer is ridiculously nice compared to manually manipulating the XML. XML becomes ok after a while -- you do get used it it -- but I think that once one starts using the Ribbon Designer, there is no going back. Keep in mind, however, that while the Ribbon Designer is superbe for creating your own custom Tab with Ribbon controls on it, the current version cannot insert a group or control onto an existing Office Tab; for that you would still need to use XML by hand.

What I think that I would do is use the VSTO Ribbon designer and then export the result to XML code, which is then used in any kind of project: VBA, COM Add-in or .NET Managed COM Add-in. This is very easy to do. Note that the Ribbon designer can only work for your own custom Tabs and cannot modify built-in Office Tabs; for that you would need to use XML by hand anyway. (Or maybe Peter Schmidt's customizer, which I've never tried.)

To create a Managed COM add-in from C# is pretty easy. I would see this article here: http://support.microsoft.com/kb/302901, but ignore all their code. Rip it all out, just leaving the 5 stubs (which map one-to-one to the IDTExtensibiliy2 interface callbacks), but nothing in them except maybe a MessageBox.Show() call to prove that they are being called. You'll typically only need to make use of OnConnection(), OnStartupComplete() and OnDisconnection().

I hope this gets you going. Let me know if I missed anything or if there's any other way that I can help...

Mike

[Mike Rosenblum]

Originally Posted by taney

Have you heard of http://www.add-in-express.com/add-in-net/ ? Any comments about them? I read the first paragraph from here http://www.winterdom.com/weblog/2007...ousThings.aspx but don’t know how much truth there is to ‘beats the crap out of using VSTO’. I’ll probably contact that author for an opinion as well.

Originally Posted by taney

So I've gotten a response from him:

It does a bunch of things:

• Has better deployment story
• It allows you to create custom panes in several outlook versions and other applications (not just on 2007 like the latest VSTO)
• It has great support for both regular command bars and the ribbon (even both at the same time if you need to support both office 2007 and earlier versions)
• Doesn't force you to muck around with some nasty AppDomain issues that VSTO forced at times

And it has a bunch of other stuff. Seriously, it's far far easier to use than VSTO. I haven't done office programming in a while, but it seriously impressed me when I used it.

I haven't used Add-In-Express myself, but it has a very good reputation. Dennis Wallentin has a very thorough review of it here:

http://xldennis.wordpress.com/2007/0...microsoft-net/

If you give it a try, we'd love to hear what you think.

[taney]

Originally Posted by Mike_R

To create a Managed COM add-in from C# is pretty easy. I would see this article here: http://support.microsoft.com/kb/302901, but ignore all their code. Rip it all out, just leaving the 5 stubs (which map one-to-one to the IDTExtensibiliy2 interface callbacks), but nothing in them except maybe a MessageBox.Show() call to prove that they are being called. You'll typically only need to make use of OnConnection(), OnStartupComplete() and OnDisconnection().

Yeah, this is what I did last night. But I'm curious why they are using Reflection and what not. Is it simply because the Add-In is meant to 'possibly' be shared across Office Applications? If I'm only targeting Excel, I don't see a need for it right?

Anyway, thanks for all the information and banter. I was going to try and push more to understand why AppDomains are 'truly' the issue. I'm by far an expert in AppDomains, but I was guessing that my managed Add-In would have 'loaded' the COM DLL into it's AppDomain so there wouldn't have been any marshalling issues. But there must be some sort of marshalling going on somewhere.

However, I do vaguely remember coming across some documentation about AppDomains/Marshalling regarding an object not being passed 'across boundaries' more than X times without marshalling starting to 'fail' (I'm sure that sounds like I'm 'making' that up But I swear I rememeber hearing/reading about that somewhere).

So it was a fun adventure...sounds like I'm dropping VSTO and using IDTExtensibility2 or whatever (possibly Add-In express) which is a shame because I feel 'dirty' that I'm not using the 'latest and greatest' stuff from MS. You do any search about VS and Office and you get bombarded with VSTO hits...so it's a hot technology and MS is obviously putting effort into it. I guess once I recover from this 'battle' I could submit it as a bug to MS

[taney]

Originally Posted by Mike_R

I haven't used Add-In-Express myself, but it has a very good reputation. Dennis Wallentin has a very thorough review of it here:

http://xldennis.wordpress.com/2007/0...microsoft-net/

If you give it a try, we'd love to hear what you think.

Interesting...one of the features is:

It uses its own ‘Shim’ to isolate Add-in Express projects from other add-ins

So probably simliar to VSTO, however from one of the screen shots in that review, I see you can choose if your Add-In is isolated or not which could maybe get around that problem.

I've sent an email to Add-In Express and if I get a reply I will post it here.

[Mike Rosenblum]

Originally Posted by taney

Yeah, this is what I did last night. But I'm curious why they are using Reflection and what not. Is it simply because the Add-In is meant to 'possibly' be shared across Office Applications? If I'm only targeting Excel, I don't see a need for it right?

Yes, I agree. Even if I were using multiple targets, I would personally prefer to avoid the use of Reflection code and instead test the type and then act on the correct type using early binding. Something like this:

if (application is Excel.Application)
    ((Excel.Application)application).DoSomething()
else if (application is Word.Application)
    ((Word.Application)application).DoSomethingElse()
else
    // etc..

In VB.NET this would be:

If TypeOf application is Excel.Application Then
    CType(application, Excel.Application).DoSomething()
Else If TypeOf application is Word.Application Then
    CType(application, Word.Application).DoSomethingElse()
Else
    ' etc..
End If

Originally Posted by taney

Anyway, thanks for all the information and banter. I was going to try and push more to understand why AppDomains are 'truly' the issue. I'm by far an expert in AppDomains, but I was guessing that my managed Add-In would have 'loaded' the COM DLL into it's AppDomain so there wouldn't have been any marshalling issues. But there must be some sort of marshalling going on somewhere.

A Managed COM add-in does not automatically load itself into a separate AppDomain and so generally has none of the problems that we've been kicking around here. However, there are two basic ways that you can run your COM Addin from a separate AppDomain:

(1) Make use of the COM Shim Wizard Version 2.3

(2) Use Visual Studio Tools for Office (VSTO)

VSTO uses it's own shiming approach which is not quite the same, but it has the same result: the COM Addin is running from a separate AppDomain from the code within Excel.

To be honest, I don't 100&#37; see why the VB6 DLL is thrown by this. VB6 can call out-of-process code. It does all the time: it's called Automation. So I don't *really* see a problem here. My best guess is that the VB6 DLL is getting loaded within the Excel AppDomain and not within the VSTO AppDomain and so when called from your code that is shimmed by VSTO they clash. There *may* be a way to get the VB6 DLL to be loaded w/in the VSTO AppDomain intsead, but this is well above my pay grade.

If you were intrepid you could ask the guys at the Visual Studio Tools for Office
- MSDN Forums. However, even if this is possible (and I suppose that it is, but it's probably hard), the VB6 DLL would then be executing out-of-process with respect to Excel and you'd lose most of your execution speed anyway.

However, I do vaguely remember coming across some documentation about AppDomains/Marshalling regarding an object not being passed 'across boundaries' more than X times without marshalling starting to 'fail' (I'm sure that sounds like I'm 'making' that up But I swear I rememeber hearing/reading about that somewhere).

Well, just because someone said it doesn't make it true! I'm with you here: it sounds fishy. That said, it will run very slowly. Generally, cross-process code runs 50x slower in my experience, and I've seen others reporting as bad as 80x slower. I've not tested across AppDomains, but I'll guess that the marshalling involved is similar and is therefore a similar cost.

So it was a fun adventure...sounds like I'm dropping VSTO and using IDTExtensibility2 or whatever (possibly Add-In express) which is a shame because I feel 'dirty' that I'm not using the 'latest and greatest' stuff from MS.

By executing from an external AppDomain they gain security but there is a huge price in execution speed, unfortunately.

You do any search about VS and Office and you get bombarded with VSTO hits...so it's a hot technology and MS is obviously putting effort into it.

It is an astounding technology. The visual Ribbon designer is outnstanding and the ability to drag-and-drop standard .NET controls onto a Worksheet is just amazing. That said, it's still not a fully-integrated with Excel, so I don't think it's reached it's final goal yet...

I guess once I recover from this 'battle' I could submit it as a bug to MS

I think this is one of those "by design" kind of a things. Yeah, we all roll our eyes at this, but once the security decision is made to run from a separate AppDomain, then the rest of it is kind of set in motion...

[Mike Rosenblum]

I'm not sure why I didn't think of this before, but in replying to your blog article here, Moving from an Excel xla add-in to a C# add-in, I realized what we were doing wrong. I'll repeat my quote on your blog article here:

Originally Posted by Mike_R

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's, Worksheet's, 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 Workbook or Worksheet's, pass such values by name only, not as objects. If you do this, then you'll have no troubles.

Anyway, in your other thread here you and I are kicking around using a VBA add-in and/or exposing a Managed COM Add-in's class via the .Object property. Both are workable, but your original idea here would have been fine too! (Sorry, I can't believe I missed this earlier. )

All these approaches share the same restriction though: only basic data types such as Double, String, etc. or an Array of such values can be passed or returned as values; Excel objects cannot be.

-- Mike