Link to home
Start Free TrialLog in
Avatar of Declan Basile
Declan BasileFlag for United States of America

asked on

Cannot see all classes from referenced Com-Visible DLL

I have a DLL 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?
Avatar of Jacques Bourgeois (James Burger)
Jacques Bourgeois (James Burger)
Flag of Canada image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Declan Basile


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 is analogous to the "long" data type in VBA?
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.
I tried using a private constructor and a shared function which works great, but apparently VBA doesn't recognize shared functions either.  Is that true?  The following code works in 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
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.
Thanks for questioning if 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) code:

Public Class Login

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

End Class
Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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 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 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 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 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.

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