Shimming with DNA

Jan 10, 2012 at 12:13 PM

Hi Govert,

I wanted to inform my test results according to your comments at: http://netoffice.codeplex.com/discussions/285331?ProjectName=netoffice

and ask some additional questions:

 

Yesterday, I successfully compile and record an Add-in:

The steps followed were:

1. Create a project using # Develop, with the following components:

--------------------------------------------------------------------------------------

Assembly:

            [assembly: ComVisible(true)]

            [assembly: Guid("FA4467A8-659B-4F7A-8751-FE65070E4301")]

 

Interface:

            [InterfaceType(ComInterfaceType.InterfaceIsDual)]

            [GuidAttribute("EFE420A7-A78C-472A-9720-512948ECCCC7")]

            public interface ITestDual

           

            defines two simple methods: SayHello, AddTow

 

Class: (Explicit implementation)

            [ClassInterface(ClassInterfaceType.None)]

            [GuidAttribute("0DC27FAF-67BE-473D-BD7B-7FCDE57081A3"), ProgId("DNAAddIn.1")]

            public class TestDual : ITestDual

--------------------------------------------------------------------------------------

As you can see, I decorated with all the attributes you recommended.

 

2. I compiled the solution with "Register for COM Interop" = False

3. Create TypeLib use Tlbexp.exe

4. Create Pack with ExcelDNAPack

5. DNAAddInPacked.xll registered with regsvr32.exe

 

The resulting registry entries were:

--------------------------------------------------------------------------------------

// HKCR: 

ProgID:

\DNAADDIN.1

\DNAAddIn.1\CLSID     @="{0DC27FAF-67BE-473D-BD7B-7FCDE57081A3}"

 

Then, for the class exposed to COM:

HKEY_CLASSES_ROOT\CLSID\{0DC27FAF-67BE-473D-BD7B-7FCDE57081A3}

            \InProcServer32 @="...\\DNAAddInPacked.xll"

            \ProgId             @="DNAAddIn.1"

            \TypeLib           @="{FA4467A8-659B-4F7A-8751-FE65070E4301}"

 

Finally, for the TypeLib:

HKEY_CLASSES_ROOT\TypeLib\TypeLib\{FA4467A8-659B-4F7A-8751-FE65070E4301}

            \1.0                  @="DNAAddIn"

            \1.0\HELPDIR   @="...\Debug" (Path de Net Assembly)

            \1.0\0\win32      @="...\\Debug\\DNAAddInPacked.xll\\1" (Supongo que refiere a donde la tlb reside: packetizada)

 

Then entries are repeated in [HKEY_CLASSES_ROOT \ Wow6432Node (My operating system is W7 X64)

 

// HKCU:

Similar to previous (HKCR) entries:

HKEY_CURRENT_USER\Software\Classes\

HKEY_CURRENT_USER\Software\Classes\CLSID\

HKEY_CURRENT_USER\Software\Classes\TypeLib\

 

// HKLM:

NO entries

--------------------------------------------------------------------------------------

 

QUESTIONS I HAVE TO MAKE ARE:

1. INTERFACE:

Not recorded.

In. NET explicit implementation is recommended to avoid versioning problems.

What happens in this case? VBA obtains information of class members from the interface or by directly linking the class?

 

2 HKLM:

Is there any way that the record is routed to HKLM instead of HKCU?

Much of my applications are in control systems with tens of users. The application must be available to all and always run.

 

3 REGISTRY, PACKING, DEBUGGING:

Following your example, registry entries are writen after package with ExcelDNAPack -> DNAAddInPacked.xll

Is there any chance to write registry without packing?

 

Thus I have no possibility to debugging the application (at least not from Excel).

Do you have any recommendation on this?

A few days ago, in another of my tests (not packed), I added a console tester, where:

Access to OM

ExcelDnaUtil.Application: application was accessed via excel.

Marshal.GetActiveObject (progID): application accessed from console tester.

However, I can not use this strategy with the packed dll.

 

 

These are my questions for now.

Thanks for your help,

For me, set the correct framework to migrate the VBA applications is very important.

Through your project and comments, your help is invaluable

 

Thank you very much,

regards,

Marco

Coordinator
Jan 10, 2012 at 5:58 PM

Hi Marco,

 

1. When you say Interface not recorded, my vague understanding is that VBA will store the interface ID, and if you make a new interface with different methods then VBA will notice. You'll have to experiment a bit.

As far as I know, there is no point in explicitly registering the interface in the Registry. We need the registry just to support object activation (and discovery of the TypeLib.)

If I am wrong, I'd be happy to learn more about how this should work. My knowledge and experience of this part is not great, I just tried to put in place something that seemed to work right.

 

2. You are right, registration is in HKCU instead of HKLM. That has many advantages, in particular it means we need no admin rights to register. If you are deploying to many users, I suggest you:

(a) put the registration in the AutoOpen, via an explicit call to ComServer.DllRegisterServer(), so that the COM Server parts are always registered when the add-in is loaded, and 

(b) figure out how the .xll add-in can be registered for all users. This is more problematic, since Excel supports no registry keys that allow this. To be pragmatic, you need to either make an installer that deals with this (tricky, but some discussion and my best suggestions here: http://groups.google.com/group/exceldna/browse_frm/thread/87fe183d2c670bbe/ff30edb3b3261d16) or live with the fact that every user will have to open your add-in at least once (the add-in can easily register itself once it is running).

But since getting the add-in running is already a problem you have to solve, I'm not sure Excel-DNA needs to deal with the problems of registering the COM parts in HKLM. Certainly adding code that deals with the potential security errors related to HKLM registration, and makes it configurable etc. already makes it beyond what I'd be able to do soon.

 

3. I don't think you need the the packing for any of the registration to work. The type library name just has to match the <Addin>.xll part. Just be sure to register the .xll, not any of the .dlls. If you have problems registering unpacked add-ins, please let me know. This should also sort out debugging - you should be able to instantiate the COM servers from VBA without packing. 

 

In general, my suggestion would be to move code into the VB.NET library, than rely on the COM server interaction, where possible. While the integration between .NET and VBA that Excel-DNA allows is attractive, it also complicates things a lot. Having everything in VB.NET makes life a lot easier. I was able to move a smallish 1000 line add-in from VBA to VB.NET in a few hours. For a large code base one could work on some automated tools.

In any event I'd be very keen to hear how you decide proceed with the VBA =>> .NET migration, and what your experiences are. Please write back.

 

Regards,

Govert

Jan 11, 2012 at 5:51 PM

 

Hello, Govert:
My comments:
1. I Check the  tlb with OLE-COM Object Viewer. The interface is listed as with  uuid =  Guid assigned via attributes.
As you know, working with explicit interfaces prevents the "class interface" to be exposed to COM world. (Object members: GetHashCode, ToString, etc.. Are not shown). The coClass only expose the explicit (custom) interface. The CoClass versioning does not create problems because its members are not accessed directly but only through the interface. As you said, I have to experiment a bit.  I'll try different builds to see if any breaks the client

2. Thanks for your feedback. I can live with for the moment HKCU and investigate which of the alternatives they propose is the best. I also think that some simple scripts can work.

3. I tested to register the xll without packing. It works. All COM members are registered.
However, I still can not debug.
From #Develop I can: Start External Program / Command Line. Excel runs but my breakpoints are not served (The program works well, but not stop)

I do not understand the reason: attribute may be missing? It may not be able to debug via xll? Routing through the interface can be the reason?

Regarding your comments:
Basically I have developed (for many years with VBA or VB) two types of programs:
Automatic reports which have little interaction with users
Analysis programs in which the user interacts with Excel via custom toolbars.

Obviously, DNA is the best option for interaction with users (ExcelCommand attribute)
For the tasks that are not launched by user events, my idea is not to expose all C# classes to Excel(COM) but simply facades. Perhaps a single interface. Automated programs got sequential execution: sequential read / validate settings, preview, perform, publish, save, etc.., And it isn´t difficult to define a contract (interface) first and then develop implementation (coclass).

Well, that's basically my plan. Debug from Excel is not absolutely necessary (I can test the facade with a C# tester) but it would be convenient.

 

Thank yow very much,

Regards,

Marco

Coordinator
Jan 11, 2012 at 6:09 PM

Hi Marco,

1. You need to distinguish between the contents of the Type Library (where the interface IIDs etc are defined, and which is magically used by VBA in ways I don't understand) and the registration of the ProgId -> ClsId -> InProcServer32 activation path in the registry. Excel-DNA has no involvement with what goes into the Type Library, how it is generated or how it is used. All of that is standard .NET COM interop stuff. Excel-DNA just facilitates the activation of classes from ProgIds, by acting as a COM server and registering those classes (via the ProgId and ClassId) that the Excel-DNA COM server will host. Once the class is activated, Excel-DNA is again not involved. Whatever VBA does with the Type Lib, like dealing with different versions, Excel-DNA is not involved with that part at all.

I think careful investigation will show you how it works. Respecting the idea the interfaces are immutable in COM should help maintain compatibility between versions.

3. Debugging via SharpDevelop. This should work - I have tested it in the past. For SharpDevelop 4.0 +, you need to be sure that your Excel-DNA add-in is hosting the .NET runtime version 4. You do this by a setting in the <AddIn>.dna file:

<DnaLibrary Name="Whatever" RuntimeVersion="v4.0" >

Check this discussion: http://community.sharpdevelop.net/forums/p/13737/36827.aspx

If that does not resolve your debugging issue, I suggest you start again with a simple .dll library that is referenced in a new Excel-DNA add-in (with the RuntimeVersion="v4.0"). Then check whether you can debug the simple case, before trying your COM activated .NET code.

I have sometimes found debugging a bit tricky, but it can be made to work.

Regards,

Govert

Jan 12, 2012 at 11:00 AM

Hello, Govert:

I tried NET 4.0 and everything was perfect. I can debug the application without problems.
But, the final solution must target 3.5

Just to understand
What is the reason why we can not debug the runtime prior to 4.0?

Thank you very much,
regards,
Marco

Coordinator
Jan 12, 2012 at 12:50 PM

Hi Marco,

The issue of debugging a native process (Excel.exe) with managed code from the .NET 2.0 runtime is a current limitation of SharpDevelop. I'm guessing there are some setting when the .NET debugger is started, which SharpDevelop doesn't currently set. But to add the feature to SharpDevelop will also take some UI work to surface the option.

I don't know more about the status that what you see in the discussion on the SharpDevelop forum, http://community.sharpdevelop.net/forums/p/13737/36827.aspx. Some gentle encouragement from you might help them along.

However, if you are planning to port VBA code to C# you should definitely use .NET 4. There are significant enhancements in the COM interop from C# 4.

Regards,

Govert