Exposing .Net Methods To VBA (COM)
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:
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:
I want to be able to call these from VBA, either by just saying:
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?
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
abc.MyMethodThe 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()
…
End Sub
End ModuleI want to be able to call these from VBA, either by just saying:
Call MySubCall MyNamespace.MySubThe 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?
There is an option. Change the module to NonInheritable (static) class and method as Shared (static) method. So that it could be called as Module.Method without more code changes.
http://stackoverflow.com/questions/436605/vb-net-how-to-reference-vb-net-module
HTH.
http://stackoverflow.com/questions/436605/vb-net-how-to-reference-vb-net-module
HTH.
ASKER
I tried both your suggestions. I can't believe I didn't think of CodeCruiser's suggestion, but just as well I guess, 'cause unfortunately it doesn't work. As for EaswaranP's suggestion, I changed:
Unfortunately this didn't work either. I could not call the sub directly, no mater how explicitly I wrote it or how I used the "Call" statement. I could get the module (or class as it now is) to show up by saying:
So no solution yet, but hopefully we're getting close.
Public Module Misc
Public Sub MySub
...Public NotInheritable Class Misc
Public Shared Sub MySub
...Unfortunately this didn't work either. I could not call the sub directly, no mater how explicitly I wrote it or how I used the "Call" statement. I could get the module (or class as it now is) to show up by saying:
Dim abc as MyNamespace.MiscDim abc as MyNamespace.Misc
abc.MySubSo no solution yet, but hopefully we're getting close.
You no need to declare using Dim. Just call the method as MyNamespace.Misc whenever you required. Thats it.
ASKER
I tried that, as I said in my last reply,
I tried all of the below:
I could not call the sub directly, no mater how explicitly I wrote it or how I used the "Call" statement.
I tried all of the below:
MySub
Misc.MySub
MyNamespace.Misc.MySubASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I figured out my own solution as explained in the answer
Call Misc.MySub