VB.NET Class with COM Interop, missing most properties when used from VBA (Access)

Question

I have written a VB.NET class that has COM Interop enabled so it can be utilized in VBA - specifically, MS Access.

The class works fine in VB.NET. With Accees, I can add the reference to it, instantiate the main object and set and return some properties.

But Access does not recognize anything relating to the sub-classes underneath the main class. VB.NET has no problem exposing these classes, but not VBA.

Is this simply a limitation of COM Interop and/or VBA?

Is there a work-around?

Answer 1

No you can’t get interop to generate the sub classes for you (to appear in VBA)

However, keep in mind that nested classes are really the same as non-nested. That sub class instance HAS to be initialized anyway. And there is nothing you can't do if the classes were to be separated. And you can well place many classes in one code module.

So this is purely a syntax preference you are looking for.

However, what you can do declare a pubic instance of any sub class in the main class (variables area as public).

Take this simple example.

Imports System.Runtime.InteropServices

<ClassInterface(ClassInterfaceType.AutoDual)>
Public Class Class1

   Private m_Company As String = ""

   Public Function MyHello()
       MsgBox("Hello world")
   End Function

   Public Property Company As String
       Get
           Return m_Company
       End Get
       Set(value As String)
           m_Company = value
       End Set
   End Property

   <ClassInterface(ClassInterfaceType.AutoDual)>
   Public Class Class2
       Private m_FirstValue As Integer = 2

       Public Property V1 As Integer
           Get
               Return m_FirstValue
           End Get
           Set(value As Integer)
               m_FirstValue = value
           End Set
       End Property

       Public Function MyTimes2() As Integer
           Return m_FirstValue * 2
       End Function

   End Class

End Class

NOTE above the nested class “class2” in above.

Ok, so check the make com assembly visible = True, and for testing check the “register for com interop”

Compile the above, set the reference in Access. (Note how you don’t have to build a custom interface either!!!).

Now, in VBA you get this in intel-sense.

NOTE carefully how the sub class Class2 does not appear.

If you really want the intel-sense and sub class to appear, then to the above vb.net class, simple add this;

Public Class Class1

   Private m_Company As String = ""

   Public SClass2 As New Class2  <--- add this line to expose as public
   Private m_Company As String = ""

.etc. etc. etc.

Now I put a “S” in from of the name – you unfortunately can’t use the same name as the nested class. (so either put something in front of the nested class, or something in front of the public instance of that class (that is what I did in above).

Now if we compile, then in VBA you get this:

Note the class2 DOES appear as a sub class

And if I hit a “dot” in VBA editor, then the sub class methods show like this:

So quite sure the above is the only way to get the sub-classes working with COM interop