Accessing an RTD Server via VBA

[Brian Flynn]

I'm porting over an old C# com addin to exceldna and I'm having trouble creating an instance of the RTD Sever via VBA.

The class definition looks like this:

    [ComVisible(true), Guid("01311A15-E3E9-48A0-B3CB-6432FD76AB62"), ProgId("Eclipse.RtdServer")]

    public class EclipseRTDServer : IRtdServer, ITopicListProvider

    {

      ...

    }

I can access the server via the RTD function from the spreadsheet, but I get "Run-time error '424': Object required" when I try to access it via VBA:

    Sub Test()

       Dim es As Object

       Set es = CreateObject("eclipse.rtdserver")

       ...

       Set es = Nothing

    End Sub

Some other info:

- The constructor of the class EclipseRTDServer does get called when I call CreateObject and it returns with no errors

- No exceptions are thrown in the C# code (I've stepped through the code in Visual Studio)

- I can create an instance of any other class via CreateObject() so long as it does not have the IRtdServer interface on it (as soon as I add the interface I get the error).

- If I remove the 'IRtdServer' interface from the class I can create it using VBA, but obviously no longer works as an RTD server

- I have tried the above with ClassInterfaceTypes: None, AutoDispatch an AutoDual

- I have tried implementing my own copies of the IRtdServer/IRTDUpdateEvent interfaces (using the same guids)

- This works without issue in the old COM version (which is registered with regsrv32)

- I have tried extending ExcelRTDServer - this fails also but I get an additional error different error about the base class not being marked as com visible

- I've tried with 0.34.6 and 0.35.1-beta2

- I've tried creating a very basic project from scratch with no dependencies other than exceldna - the issue still happens

- I have set ComServer=True in the dna file, and call ComServer.DllRegisterServer / Unregister in the Addin

- The issue happens in Excel 2007 and 2016 (64 bit) on multiple different machines

In an ideal world I would just move the functionality I require to access via VBA into a another class, but my company has hundreds of spreadsheets that already use the old library and Im not keen to update the VBA code in all of them.

[Govert van Drimmelen]

Hi Brian,

Could you post a minimal project that reproduces this, either here or Github, and I'll help sort it out.

-Govert

--
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.

[Brian Flynn]

Thanks Govert!

I've posted here: https://github.com/brifly/ExcelDnaRTDSample

[Govert van Drimmelen]

Hi Brian,

OK - I got the code and can reproduce the issue.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Brian Flynn [brian...@gmail.com]
Sent: 10 May 2018 05:38 AM
To: Excel-DNA
Subject: Re: [ExcelDna] Accessing an RTD Server via VBA

[Govert van Drimmelen]

Hi Brian,

I've debugged through the Excel-DNA code for this.

I can't see any obvious way in which a class implementing IRtdServer is treated differently to a class that doesn't for the COM creation from VBA.

It would seem that the problem arises inside Excel, when it sees the class implementing IRtdServer.

Would it be possible for you to post a simple C# COM library that works as both an RTD server and from VBA?

(Without Excel-DNA involved.)

That would be the next route for me to investigate.

As an aside, I did try to make the class derive from ExcelRtdServer, and did not have the unexpected problem you report about " the base class not being marked as com visible". All RTD servers you make with Excel-DNA should derive from ExcelRtdServer rather than implementing IRtdServer yourself. 

In the particular case of a class implementing IRtdServer, Excel-DNA wraps the class and will cause your VBA object not to expose the methods from that class to VBA anyway (since the object VBA gets is wrapped and only supports the IRtdServer methods).

But this issue is one step further along than then problem we face first, where the CreateObject itself fails from VBA.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Brian Flynn [brian...@gmail.com]

Sent: 09 May 2018 10:06 AM
To: Excel-DNA
Subject: [ExcelDna] Accessing an RTD Server via VBA

[Brian Flynn]

Hi Govert

Thanks for looking into this, and for the info on using ExcelRTDServer - I'll rewrite my existing server once I have the basics working.

I've uploaded a basic COM version that doesnt have the issue here:

https://github.com/brifly/ComRtdExample

Brian

[Govert van Drimmelen]

OK - I got the COM version and works (behaves as you describe) on my machine too.

-Govert

[Govert van Drimmelen]

I've looked a bit more. The problem when the class directly implements IRtdServer is definitely that Excel-DNA is creating a safety wrapper. If I skip the wrapper creation the code works from VBA.

What I don't understand yet is why the ExcelRtdServer case does not work - this seems to be an issue of understanding the required COM attributes and how the COM interop deals with base classes and interfaces.

I'm still investigating further.

-Govert

[Brian Flynn]

Ok thanks Govert - if you cant a solution I might create my own temporary version and remove that wrapper - at least until I can get the spreadsheets migrated to using another class.

I assume its the class SingletonClassFactory, the following lines?

 else if (riid == ComAPI.guidIRtdServer)

 {

       ppvObject = Marshal.GetComInterfaceForObject(_instance, typeof(IRtdServer));

 }

[Govert van Drimmelen]

You can try to remove these lines:

https://github.com/Excel-DNA/ExcelDna/blob/f381f59b55091f7e17afd3318d182997bc181416/Source/ExcelDna.Integration/ComRegistration.cs#L57

 

But this does not allow you to use the ExcelRtdServer base class, which I would highly recommend doing.

 

-Govert