Help needed on VBA COM interface

Add-in Express™ Support Service
That's what is more important than anything else

Help needed on VBA COM interface
 
Oliver Maltry


Guest


I am trying to migrate an Excel VSTO application level Add-in to ADX. A very important part of my VSTO solution is, to expose a few methods and a bunch of events to VBA clients using a COM interface.

My test Com-class looks like 

namespace MyAddin1
{
    using System.Runtime.InteropServices;

    [ComVisible(false)]
    public delegate void TestEvent1(string Name, string Description, int Sequence);


    // Event interface
    [ComVisible(true)]
    [Guid("5E6B18FE-EEF6-4754-8862-A683627C96C2")]
    [InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
    public interface ITestEvents
    {
        [DispId(101)]
        void OnTestEvent1(string Name, string Description, int Sequence);
    }


    // method interface
    [ComVisible(true)]
    [Guid("9E92E12F-48BF-43AB-A86D-D69C0EBBCC62")]
    [InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
    public interface ITestMethods
    {
        [DispId(201)]
        void TestMethod();
    }

    [ComVisible(true)]
    [Guid("748406EC-BAEC-41A7-B09D-1252D1422598")]
    [ClassInterface(ClassInterfaceType.None)]
    [ComSourceInterfaces(typeof(ITestEvents))]
    public class VBCom : ITestMethods
    {
        public VBCom()
        {
        }

        private event TestEvent1 OnTestEvent1;

        public void TestMethod()
        {
            RaiseTestEvent1();
        }

        private void RaiseTestEvent1()
        {
            if (OnTestEvent1 != null)
            {
                // raise VBA/VB6 event
                OnTestEvent1("foo", "bar", 12345);
            }
        }
    }
}


After performing Excel -> Alt+F11 -> Add reference -> MyAddin1.tlb, I can see my VBCom class containing, TestMethod and TestEvent in the object catalogue. So far, so good.

Next I want to test my test class from VBA. I expected the code on the VBA side would not change when migrating to ADX. 


' VBA code for VSTO solution
Private WithEvents objVBCom As MyAddin1.VBCom
Private addIn As Office.COMAddIn

Sub TestVBCom()
   
    If objVBCom Is Nothing Then
        Set addIn = Application.COMAddIns("MyAddin1")
        Set objVBCom = addIn.Object
    End If
    
    objVBCom.TestMethod
        
End Sub

Private Sub objVBCom_OnTestMethod(string Name, string Description, int Sequence)
    debug.print "Sequence: " & Sequence & " Name: " & Name & " Descr: " & Description
End Sub


I changed the line 
Set addIn = Application.COMAddIns("MyAddin1")
to ...COMAddIns("MyAddin1.AddinModule") in order to match ADX specs. I run the code but it fails on line 
Set objVBCom = addIn.Object
with a "Type mismatch" error :-(

I read through support section and blogs but could not find anything related to my issue.

Thanks in advance for your assistance
Posted 13 Aug, 2015 11:06:54 Top
OliverM


Guest


Event signature is 
Private Sub objVBCom_OnTestEvent(string Name, string Description, int Sequence)
not 
Private Sub objVBCom_OnTestMethod(string Name, string Description, int Sequence)
Posted 13 Aug, 2015 11:13:27 Top
Andrei Smolin


Add-in Express team


Posts: 18830
 Joined: 2006-05-11
Hello Oliver,

Please check this sample project: http://temp.add-in-express.com/support/com-addin-with-events.zip. It demonstrates an add-in with events (VB.NET).


Andrei Smolin
Add-in Express Team Leader
Posted 14 Aug, 2015 00:58:56 Top
OliverM


Guest


Hi Andrei,

Thanks for the link, unfortunately the project fails to open with: Unable to open project 'E:\Coding\ADX\com-addin-with-events\com-addin-with-events\ComAddinWithEventsSetup\1.0.0\ComAddinWithEventsSetup(1.0.0).vdproj'.

I tried to recreate the project using VS2013 -> File -> New -> Project from existing code.
It did create the project from your sources but also gave me 140 compile errors like "'AddinExpress' is not declared. It may be inaccessible due to its protection level."

I am stuck :-(
Posted 14 Aug, 2015 05:34:54 Top
Andrei Smolin


Add-in Express team


Posts: 18830
 Joined: 2006-05-11
Oliver,

Ignore that project; the is a setup project that you don't need at the moment. When you need it, you can install this VS 2013 extension: http://visualstudiogallery.msdn.microsoft.com/9abe329c-9bba-44a1-be59-0fbf6151054d.

OliverM writes:
It did create the project from your sources but also gave me 140 compile errors like "'AddinExpress' is not declared


Check the references. You may need to re-add AddinExpress.MSO.2005.dll.


Andrei Smolin
Add-in Express Team Leader
Posted 14 Aug, 2015 05:40:42 Top
OliverM


Guest


AddinExpress.MSO.2005 reference was there, unfortunately this was the only reference set. Took me a while to puzzle all missing references out.

Having checked the Com class, I could not see big differences between your and my code.

Before writing my test class yesterday, I searched the forum for information on 
 protected override object RequestComAddInAutomationService()
as this is a key point to expose functionality to 3rd parties in VSTO. The clear answer to the question, do I need to implement that, was
There is no need to implement this method in case of Add-in Express based COM add-ins.


I did not doubt that statement and searched for hours. Your VB sample worked, mine in C# did not. I changed my Com class interface types, rewrote the Com class, replaced my Com class code with yours. Nothing worked, all I got was "error 13 - type mismatch".

Meanwhile I know for sure the information RequestComAddInAutomationService() is not needed is NOT correct. It needs to be replaced with GetRemotingConfiguration(). 

protected override object RequestComAddInAutomationService()
in a VSTO solution seems to be a wrapper routine for 
protected override object GetRemotingConfiguration(ref System.Runtime.Remoting.Channels.IChannel channel, ref string url)
in an ADX solution.
Both routines behave identical, they both execute even before ThisAddIn_Startup/AddinModule_AddinInitialize fires and they both return an instance of the class being used as Com interface.

The only difference I spotted is:
- If in a VSTO solution RequestComAddInAutomationService() is missing or its return value equals null, I get "error 91 object variable or with block variable not set"
-If in an ADX solution GetRemotingConfiguration() is missing or its return value equals null, I get "error 13 type mismatch"

I would be happy, if you guys would update documentation accordingly, in order to save other users from wasted hours and a lot of frustration.

Appreciate your support, Andrei.

Regards
Oliver
Posted 14 Aug, 2015 12:05:18 Top
Andrei Smolin


Add-in Express team


Posts: 18830
 Joined: 2006-05-11
Hello Oliver,

I'm really sorry about that.

I've understood that we've never mentioned GetRemotingConfiguration() in the manual. We'll consider writing an article about it. Hope describing this method will help some of developers in the future.


Andrei Smolin
Add-in Express Team Leader
Posted 17 Aug, 2015 09:45:52 Top