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:
Quote:
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