I am currently in the process of extending my Excel VBA project using VB.Net and Visual Studio. I’ve created a working DLL, made it COM visible and registered, and referenced it from my Excel file.
In my DLL, I have created a few classes (each with its own interface and explicit GUID to prevent errors caused by adding/removing methods/properties). I have successfully used these classes in VBA in this way:
Dim abc As New MyClass
The problem is that I have a few subs that I am trying to use that aren’t part of any class. These are in a module call “Misc”, as shown below:
Public Module Misc
Public Sub MySub()
I want to be able to call these from VBA, either by just saying:
But when I try to call the sub, it can’t find it and I get “sub or function not defined”.
The only way I’ve been able to access the sub is to write a class and interface that simply calls that sub, but that seems like a lot of work just to call one sub, plus I also have to create a new instance of the class in VBA before I can call the sub, adding an extra line of code.
Is there any way around this?