Cannot see all classes from referenced Com-Visible DLL

I have a DLL VB.net application written in Visual Studio 2015 RC.  It's Com-visible and I checked "Register for COM interop".  The name of the dll file (I believe this is called the "assembly name"?), the project name, and the root namespace name are all "AltekVB".  I copied AltekVB.tlb from the Debug folder to the system32 folder and referenced it in an Access 2003 .mdb database.  Why would only some of the classes defined in AltekVB.dll be recognized (come up in intellisense) from VBA?
LVL 1
Declan_BasileITAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jacques Bourgeois (James Burger)PresidentCommented:
Only classes declared as public are visible. If some classes are declared as Private, Friend, MustInherit or with almost any other modifier, then they will not be visible from outside of the dll itself.

They might also be available on a secondary namespace, something like AltekVB.Foo.YourClass.

You might also not see stuff that deal with type that are not available in VBA. As an example, a Long in .NET is not the same thing as a Long in VBA and cannot be handled by VBA because it is too big.

If a class has not default constructor, a New without parameters, COM cannot use it, be cause parametized constructors where not available in COM.

There might be attributes, modifiers between < > before the declaration that hide the classes. This is sometimes used to hide internal classes that should not be called directly.
Declan_BasileITAuthor Commented:
It looks like the issue is that VBA is not seeing the classes where I've created constructors with parameters.  Do you recommend that I use a factory model, where I implement a class (one with a default constructor that VBA can see) that has methods to create and return a reference to an instance of the other class that has parameterized constructors?  Do you know of another, perhaps preferred, way around this limitation?
Also, what data type in VB.net is analogous to the "long" data type in VBA?
Jacques Bourgeois (James Burger)PresidentCommented:
Even if you create a class that has a default constructor, you would not be able to pass it the initialization parameters, because that class would also need a parameterless constructor.

VBA did not provide for a complete object oriented environment, and you have to live with the limitations of VBA on that one. Design your .NET class the way you would design a VBA class. A default constructor without parameters, and then you either assign the values to the properties or call some kind of Init method that you call to provide the parameters.

VBA cannot handle the .NET Long (same for the .NET Decimal), because it's a 64-bit value and VBA is designed for 32-bit. Do you really need more than the maximum 2,147,483,647 value that a .NET Integer provides?

Maybe you do not know that some of the integer types have changed between VBA and VB.NET:

VBA           .NET
Integer     Short        16-bit      
Long         Integer     32-bit

You need to read a .NET Integer into a Long in VBA. Stated otherwise, if you need a Long in VBA, you pass an Integer from .NET.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Declan_BasileITAuthor Commented:
I tried using a private constructor and a shared function which works great VB.net, but apparently VBA doesn't recognize shared functions either.  Is that true?  The following code works in VB.net but the "FromEmployeeNumber" and "FromUserId" functions aren't recognized in VBA.

Public Class Form1

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim c1 As Class1 = Class1.FromEmployeeNumber(240)
        Dim c2 As Class1 = Class1.FromUserID(123)
    End Sub

End Class

Public Class Class1

    Public EmployeeNumber As Long
    Public UserID As Long

    Private Sub New()
        ' default constructor is PRIVATE so it cannot be created directly!
        ' This is not possible now:
        ' Dim c1 As New Class1() ' <-- impossible from OUTSIDE of Class1
    End Sub

    Public Shared Function FromEmployeeNumber(ByVal empNum As Long) As Class1
        Dim c1 As New Class1 ' <-- this is allowed because we are already inside Class1
        c1.EmployeeNumber = empNum
        ' ... some code here specific to empNum? ...
        Return c1
    End Function

    Public Shared Function FromUserID(ByVal userID As Long) As Class1
        Dim c1 As New Class1 ' <-- this is allowed because we are already inside Class1
        c1.UserID = userID
        ' ... some other code here specific to userID? ...
        Return c1
    End Function

End Class
Jacques Bourgeois (James Burger)PresidentCommented:
I am not sure about the possibility of calling Shared functions from VBA. I would need to try it and do not have time to do that because I do not have any .NET project right now that is build to be used by VBA and could readily be used for a quick test. The concept of a Shared function does not exist in VBA, but in VB.NET, a module is compiled as a class that contains only Shared methods. So we have something in between. I do not know how the interop interprets that.

Your idea is a strange one anyway. Using a class as the mean to instantiate an individual object of that class is not something I remember having encountered before, and I have been at it since the beginning of the 90's.

I would rather go for a public default constructor and initialization methods. I know that it is not "elegant", but you are dealing with VBA, which does not follow all the conventions of OOP, so you have to make concessions somewhere.

One question to ask before all else would be to determine if a .NET class is the best way to go. I would program in .NET anytime over VBA. But in some situations, you gain nothing but a loss of performance when calling .NET from VBA. If you do not expect that dll to be eventually used by a .NET application, and if it does not require features that are not available from VBA, then your best bet would be to stick to VBA all the way.

Or maybe design a .NET application that uses your database underneath. It's a lot easier to address Access from .NET than it is to call .NET from VBA. And if all you need is a few extra features in an already existing Access application, I have seen interesting things done from a button in Access that simply Shell a .NET application and pass it parameters. For the Access user, the switch to a Windows Form was not an inconvenient. Even more so if you do not need to provide an interface from .NET.

This is specially something to think about, specially taking into consideration the fact that Microsoft does not support Access 2003 anymore. You could eventually hit a dead end by going the .NET route, because they do not feel obliged to consider Office 2003 when they update the framework.

Only floating ideas here.

Finally, reviewing your question, I see that you are still in the RC edition. The final Community Edition has been out for a good month now. I would use that one instead of the RC.
Declan_BasileITAuthor Commented:
Thanks for questioning if VB.net is appropriate for my situation.  I'd like to talk more about that after I get this one .dll to work from VBA.  I deleted my own constructor and added the "SetUser" method (think of "SetUser" as the "Init" method) to the Class definition.  Why won't VBA recognize the "SetUser" method in the following code?  Does "SetUser" need to be a function instead of a Sub?  

VBA code:
    Dim Login1 As AltekVB.Login
    Set Login1 = New AltekVB.Login

   Login1.SetUser(arguments do here)

VB.net code:

Public Class Login

    Public Sub SetUser(eType As EmpRefType, intId As Integer)
               'code goes here
    End Sub

End Class
Jacques Bourgeois (James Burger)PresidentCommented:
So many things can go wrong. That is why I suggested keeping everything in VBA or using .NET to handle your database instead of going the way you go. Specially since you seem to be at the beginning of your work on the dll.

This is what I personally ended up doing many years ago, after spending hours encountering problems such as the ones you are exposing. Every time you solve one, another crops on you. My customers had a lot invested in Access/Excel/Word VBA, and on my side I wanted to switch as much work as possible to .NET that is superior in so many ways to VB6 and VBA. But it was so much troubles that we eventually kept maintaining the old stuff entirely in the classic VB environment, and used .NET only for new applications.

.NET was made to understand COM (VBA follows the COM specifications). But COM came long before .NET and does not understand .NET. So the compiler has to perform many tricks on the .NET side for COM to understand it. Making the library COMVisible does some of the work, but not all. And there are a few things that you cannot do at all. Can an old horse feed on gasoline?

I do not remember seeing anything preventing you to call subs, but now that you ask the question, I do not remember needing subs in the few instances where I had to call .NET from VBA. It was always so that the .NET side returned some information.

Subs as they are known to VB programmers do not exist in .NET. .NET has functions that return a void instead of subs. The VB compiler simply hides that from you so that you see subs as you did in VBA. So its possible that this causes a problem on the VBA side. Easy to check for you. Have you tried calling a simple Sub, and if not, simply make it a Function to see if it makes a difference?

But do it with a simple function, that uses only basic types, not with your routine. This is because I see another potential problem in your code. What is EmpRefType? If it is a Structure, then you might have a problem. Because structures are passed as values, and values for some types do not map directly.

A Date in .NET for instance does not have the same format as in VBA. It needs to be converted when going from one to the other. This is usually done by the interop layer when you use classes, but not with structures, so it is recommended to always use classes in a situation with yours.

If you try to map a VB.NET class or structure, you should  also be careful about the changes to the basic types. If a given member is a Integer in the VB.NET class, it should be a Long in the VBA class. If you map an Integer to an Integer, the 2 are seen as incompatible.

Finally, as told sooner, COMVisible does not do all the work. Some authors suggest to always mark your classes with the following attributes to put all the chances that all the elements are visible to IntelliSense on the VBA side and that the VB.NET compiler does everything necessary :

<Serializable(),  ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)>
Public Class Login

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Declan_BasileITAuthor Commented:
The attributes you gave me worked great.  You've been a tremendous help.  Thank you so much.  Now that I've had the experience of creating the .dll using VB.net and getting it to work from VBA I can better decide which tool to use in what circumstances.  
   
I've developed Access applications for as long as you've been writing OOP applications and have been extremely satisfied with it, mostly for its simplicity.  All I've had to do over the past 20 years was specify what linked SQL Server table is the recordsource of a form and Access automatically handles saving changes that the users make (additions, deletions, updates) back to the SQL Server table for me.  There is such little overhead, I could write an application so quickly, and it's so easy to maintain.  I became frustrated trying to create a multiple column listbox in VB.NET when in Access I just have to specify the query to use as the rowsource, how many of the columns to display and the width of each column. It appears to me that I have to do so much extra unnecessary work writing the same applications in .NET.  I'm extremely intrigued by VB.NET, but still (even after writing this .dll) don't understand why anyone would do so much extra work using VB.NET when anything I've ever wanted to do I was able to do just fine (and do it so much quicker) in Access.  So far my take on VB.net is that it is "brilliantly over-complicated".  It's very well thought out, I'm fascinated, but why write programs in a way that has so much more overhead and takes so much more time when Access does the job and does it well?

With that being said, I've been spending an enormous amount of time lately battling corruption issues with Access.  I've used it for 20 years and never had this much of a problem with corruption.  The data is in SQL Server, so the corruption issues are with forms and reports, not data.  

I have a form in an Access application that has a tab control and 12 subforms in total dispersed amongst the tabs.  There is a lot of functionality all built into this one form (and it's subforms and all the forms and reports and queries that are used by this form), and it's very useful and works really well for the users to have all of this functionality on the one form.  I kept developing the form more and more and Access kept handling it just fine.  I couldn't believe how well it performed and continued to perform as I added more and more functionality to it.

Unfortunately the corruption is with this form.  I've tried so many things to fix this corruption and none have worked.  This leads me to believe that the solution is to completely re-write the form, which is a big job.  I could re-write this form in its own .mdb file and make it its own Access application, but now I'm beginning to question the stability of Access.  This is why I'm learning VB.NET.  I'm considering re-writing the form in VB.NET, and I have to work with it and learn about it more before I could make that decision.  I'm weighing the stability of VB.NET against the simplicity of Access.  If I re-wrote the entire form in VB.net it would probably take me until the end of the year because 1.) I'm not familiar with it, 2.) it takes longer to develop in it, and 3.) I wouldn't be able re-write it the same way (I'd have to redesign it).  I thought that I could create a "hybrid" application, such that I would re-write part of it in VB.NET dll's, part of it in Access, and call the upon the dll's functionality from Access.  This way I would learn VB.NET so I could better decide how far to go with it, yet I would re-write some of the program in Access so it doesn't take too long.  If I decide that VB.NET is the way to go, I could gradually re-write more and more of the form as VB.net dll's and eventually turn it into a purely VB.NET application.   I decided to write this one particular dll because it contains functionality that other Access and VB.NET programs (written by both me and eventually others as well) can utilize.

Sorry that I wrote so much but you were questioning what I'm doing so I wanted to give you the background.  It takes so much longer to write an application in VB.NET, why do you say that you would program in it anytime over VBA?  What ways is VB.NET superior to VBA?  Given the background, do you still think in my particular case that referencing VB.NET dll's from VBA is a bad idea?  I thought it would be a good way to learn VB.NET, provide common functionality (in terms of code reuse) to multiple Access and VB.NET applications, and eventually transition to it if I decide it's the way to go.
Jacques Bourgeois (James Burger)PresidentCommented:
Hi.

Sorry for  the delay, I was pretty busy today, and am going out for a couple of days on my on and off vacations. That is how it is when you are self employed.

I don't have time to answer you right now, but should be able to give it a good look and comment Thursday or Friday, Montreal time.
Declan_BasileITAuthor Commented:
A combination of getting rid of the parameterized constructor and adding attributes before the class declaration solved my problem.  Thanks again Jacques.  You not only helped me through this project but also helped me learn a lot along the way!  I had to close out the question because it required my attention and it is answered.  If you ever find the time to comment about my Access vs. VB.NET inquiry I'd be interested to hear what you have to say, for I'm still deciding when it's best (in my circumstance) to use each application.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.