Best Practise Advice Calling C# DLL from VBA

[Steve Le Monnier]

Can anybody give me a quick heads up on the best way to develop C# based
DLL's that are called via VBA.

I'm developing a pop-up window that will record information within SQL. I
need to launch this pop-up from an application that only supports VBA. So I
decided the best solution would be to create a DLL with C# that exposes the
methods needed to launch my pop-up window.

My first attempt didn't work and after spending several hours on the various
forums I now have a working solution, but still a little confused about best
practice.

The reason why it didn't work originally was because I forgot to click the
"Register for COM Interop". But other solutions talk about the GAC and
various un-friendly command line switches.

Once this "COM Interop" switch was checked I could see the DLL from Excel
VBA (used for testing solution) without doing anything else, however no
methods are exposed via intelesence... but it does work.

What is the best way to develop a small project using C# that can be called
via any VBA enabled application? I think the DLL approach is best but unsure
of what has to be done in the C# environment to get this to work the same
way VB6 based DLL's used to.

Any assistance gratefully received.

Steve Le Monnier

 

[Cindy M.]

Hi Steve,

Note that this isn't anything I've really tried, but I do read articles and
threads that discuss it and...

I think you also have to create and expose an INTERFACE with the methods you
want to call through COM. I'm looking at Andrew Whitechapel's ".NET development
for Microsoft Office", where there's a detailed list of steps. The bit of
sample code for the Interface looks like this
[GUID("number-thingy-here")]
[ComVisible(true)]
public interface IExtraStuff //same name as class library project
{
double F2C(double val); //the method to be exposed
}

the it says to implement the interface in a public class, also GUID attributed
AND attributed with ClassInterface(ClassInterfaceType.None). Put the methods in
this public class.

Can anybody give me a quick heads up on the best way to develop C# based
DLL's that are called via VBA.

I'm developing a pop-up window that will record information within SQL. I
need to launch this pop-up from an application that only supports VBA. So I
decided the best solution would be to create a DLL with C# that exposes the
methods needed to launch my pop-up window.

My first attempt didn't work and after spending several hours on the various
forums I now have a working solution, but still a little confused about best
practice.

The reason why it didn't work originally was because I forgot to click the
"Register for COM Interop". But other solutions talk about the GAC and
various un-friendly command line switches.

Once this "COM Interop" switch was checked I could see the DLL from Excel
VBA (used for testing solution) without doing anything else, however no
methods are exposed via intelesence... but it does work.

What is the best way to develop a small project using C# that can be called
via any VBA enabled application? I think the DLL approach is best but unsure
of what has to be done in the C# environment to get this to work the same
way VB6 based DLL's used to.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[Steve Le Monnier]

Many thanks Cindy, will check out that article and do a little more R&D.

Cheers

Hi Steve,

Note that this isn't anything I've really tried, but I do read articles
and
threads that discuss it and...

I think you also have to create and expose an INTERFACE with the methods
you
want to call through COM. I'm looking at Andrew Whitechapel's ".NET
development
for Microsoft Office", where there's a detailed list of steps. The bit of
sample code for the Interface looks like this
[GUID("number-thingy-here")]
[ComVisible(true)]
public interface IExtraStuff //same name as class library project
{
double F2C(double val); //the method to be exposed
}

the it says to implement the interface in a public class, also GUID
attributed
AND attributed with ClassInterface(ClassInterfaceType.None). Put the
methods in
this public class.

Can anybody give me a quick heads up on the best way to develop C# based
DLL's that are called via VBA.

I'm developing a pop-up window that will record information within SQL. I
need to launch this pop-up from an application that only supports VBA. So
I
decided the best solution would be to create a DLL with C# that exposes
the
methods needed to launch my pop-up window.

My first attempt didn't work and after spending several hours on the
various
forums I now have a working solution, but still a little confused about
best
practice.

The reason why it didn't work originally was because I forgot to click
the
"Register for COM Interop". But other solutions talk about the GAC and
various un-friendly command line switches.

Once this "COM Interop" switch was checked I could see the DLL from Excel
VBA (used for testing solution) without doing anything else, however no
methods are exposed via intelesence... but it does work.

What is the best way to develop a small project using C# that can be
called
via any VBA enabled application? I think the DLL approach is best but
unsure
of what has to be done in the C# environment to get this to work the same
way VB6 based DLL's used to.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)


This reply is posted in the Newsgroup; please post any follow question or
reply
in the newsgroup and not by e-mail