Making a referenced class ComVisible

[ateene]

In my application I have a referenced ClassLibrary with a class that I want to make visible in my COM Server so I can use it in VBA

I have it working but something seemed to have changed recently

Here is real simple example of MyClassLibrary

using System.Runtime.InteropServices;

namespace MyClassLibrary

{

    [ComVisible(true)]

    [ClassInterface(ClassInterfaceType.AutoDual)]

    public class LibQuote

    {

        public string Symbol { set; get; }

        public double Bid { set; get; }

        public double Ask { set; get; }

        public double Last { set; get; }

        public LibQuote() { }

    }

}

This is the ComServer example

using System;

using ExcelDna.Integration;

using ExcelDna.ComInterop;

using System.Runtime.InteropServices;

namespace ComServerTest

{

    [ComVisible(true)]

    [ClassInterface(ClassInterfaceType.AutoDual)]

    public class WebQuote

    {

        public double GetLibPrice(MyClassLibrary.LibQuote quote) { return quote.Last; }

        public MyClassLibrary.LibQuote GetLibQuote(string symbol)

        {

            var quote = new MyClassLibrary.LibQuote() { Symbol = symbol };

            var r = new Random((int)DateTime.Now.Ticks);

            quote.Last = r.NextDouble() * 5.0;

            return quote;

        }

    }

    [ComVisible(false)]

    class ExcelAddin : IExcelAddIn

    {

        public void AutoOpen()

        {

            ComServer.DllRegisterServer();

        }

        public void AutoClose()

        {

            ComServer.DllUnregisterServer();

        }

    }

}

Running tlbexp ComServerTest.dll  will generate the ComServerTest.tlb and the MyClassLibrary.tlb if it doesn't exist. Subsequent runs of tlbexp ComServerTest.dll will not regenerate the MyClassLibrary.tlb even if MyClassLibrary.dll has changed. So If I make a change to the MyClassLibrary I need to delete the MyClassLibrary.tlb before I generate the ComServerTest.tlb.

Here is the VBA code

Sub TestQuote()

    Dim q As LibQuote

    Dim wq As New WebQuote

    

    Set q = wq.GetLibQuote("FOO")

    Debug.Print "LibQuote " & q.Symbol & " " & wq.GetLibPrice(q)

End Sub

In Excel I have to reference both the ComServerTest.tlb and MyClassLibrary.tlb . 

I'm not sure what changed but up to a few days ago I didn't need to reference the Class Library tlb file in Excel application. Is there a way to set up the ComServer so I don't have add a reference to Class Library tlb ?

Thanks, Andres

[Govert van Drimmelen]

Hi Andres,

You have to be quite careful when changing COM interfaces - the COM rules specify that a published COM interface can never change, and Excel might use this fact in its internal caching of information from your references. Using explicit interfaces instead of AutoDual as the class interface can help a bit with this.

From your example it seems like you would need both type libraries to describe your COM server, since the WebQuote class has a method that returns a type from MyClassLibrary. I'm not sure why you're surprised that you need both - how else would Excel know what a LibQuote is?

I don't know anything about how TlbExp deals with dependencies - the code is somewhere on the web, though. But it seems safest to always regenerate all the type libraries you need, or perhaps to make a single library that contains all the  COM interfaces you'll use from VBA.

Excel-DNA has a packing feature that will put the .tlb file into the .xll file, and then you can reference the .xll file directly from the VBA Tools->References. This helps you distribute only a single file for your add-in, but won't make any difference in the above issues of versioning COM interfaces or what Tlbexp generates.

Another option is to make everything late-bound from VBA, then you don't need to Tools->Reference anything, but you lose IntelliSense, compile-time error checking etc. I presume that's where you starting when trying out the Excel-DNA COM Server features.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of ateene [atee...@gmail.com]
Sent: 15 February 2017 10:24 PM
To: Excel-DNA
Subject: [ExcelDna] Making a referenced class ComVisible

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.