Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Exposing .Net Methods To VBA (COM)

Posted on 2013-06-29
7
Medium Priority
?
405 Views
Last Modified: 2013-09-10
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
abc.MyMethod

Open in new window


      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()

	End Sub
End Module

Open in new window


      I want to be able to call these from VBA, either by just saying:
Call MySub

Open in new window

Or
Call MyNamespace.MySub

Open in new window

     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?
0
Comment
Question by:BROOKLYN1950
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39287280
Does following work?

Call Misc.MySub
0
 
LVL 16

Expert Comment

by:Easwaran Paramasivam
ID: 39287853
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.
0
 

Author Comment

by:BROOKLYN1950
ID: 39288943
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:

Public Module Misc

      Public Sub MySub

      ...

Open in new window

to

Public NotInheritable Class Misc

      Public Shared Sub MySub

      ...

Open in new window


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

Open in new window

But when I tried:
Dim abc as MyNamespace.Misc
abc.MySub

Open in new window

I got an "object reference not set to instance of an object" error.

So no solution yet, but hopefully we're getting close.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 16

Expert Comment

by:Easwaran Paramasivam
ID: 39289127
You no need to declare using Dim. Just call the method as MyNamespace.Misc whenever you required. Thats it.
0
 

Author Comment

by:BROOKLYN1950
ID: 39290704
I tried that, as I said in my last reply,
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.MySub

Open in new window

And I also tried each of those prefixed with the "Call" statement (though I don't  think that would make a difference).
0
 

Accepted Solution

by:
BROOKLYN1950 earned 0 total points
ID: 39467540
I pretty much forgot about this question until I stumbled on it today. I have figured out my own solution and though its far from optimal it was the only thing I found that worked the way I needed it to.

I created a standard class with an explicit GUID and an explicit interface with en explicit GUID. This class has members named after the the ones in my module (static class). The members in the normal class call the members in the module.

The non-static class is exposed to COM the same was as my other non-static classes and I can call the members of my module through this class. At the same time, my VB.Net code can call the members straight from the module without need to create an instance of a class to use.

So in short, COM sees a non-static class, and .Net sees both a static call and a non-static class which has calls to the static class.

The downside to this is that I have to declare each of my members three times, which can get annoying, but at least it works!
0
 

Author Closing Comment

by:BROOKLYN1950
ID: 39479072
I figured out my own solution as explained in the answer
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question