How to use c# class in Excel VBA?

Question

I have a standalone c# applications that does something specific (listens to TCP port and pronounces all strings that arrive to it via speech synthesizer). How can I make the c# class visible to a VBA program, same way other "References" are visible to it? I would appreciate short and clean example. I struggle to find one of those for some reason.

If there are some gotchas specific to c# <-> vba interaction, I would like to know about those too.

Here is a C# code. I build is as a class library with "Register for COM interop" setting. When I add the resulting .tlb file to VBA references list, I expect to see SayCom library that has SayCom class with 2 methods, square and getCount. I do not see that. What am I missing?

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Runtime.InteropServices;

    [assembly: CLSCompliant(true)]

    namespace SayCom
    {
        [CLSCompliant(true)]
        [ComVisible(true)]
        public class SayCom
        {
            int count;

            public SayCom()
            {
                count = 0;
            }

            [ComVisible(true)]
            public int square(int x)
            {
                ++count;
                return x * x;
            }

            [ComVisible(true)]
            public int getCount()
            {
                return count;
            }
        }
    }

Main issue is using C# class in VBA. I mentioned sending strings in TCP/IP to provide some context. I see now how that may be confusing. — nsg
– nsg, Commented Feb 19, 2014 at 21:58
I have voted this to be re-opened as it is now pretty clear as to what you want. Quick question. Did you follow this? Project settings | Application | Assembly Information | Make assembly COM visible (Set to ON) | Build | Register for COM interop (Set to ON) — Siddharth Rout
– Siddharth Rout, Commented Feb 19, 2014 at 22:08
I did not do the part Assembly Information|Make assembly COM visible. I did it now, but still not seeing the class. I see the library SayCom, but is has nothing in it. — nsg
– nsg, Commented Feb 19, 2014 at 22:20
Sorry to reply so late but I just woke up :p I have posted an answer. — Siddharth Rout
– Siddharth Rout, Commented Feb 20, 2014 at 7:17

Siddharth Rout · Accepted Answer · 2014-02-20 07:15:05Z

4

This works for me

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;

namespace ClassLibrary1
{
    [Guid("BBF87E31-77E2-46B6-8093-1689A144BFC6")]
    [ComVisible(true)]
    public interface MyMiniSubs
    {
        int square(int x);
        int getCount();
    }

    [Guid("BBF87E31-77E2-46B6-8093-1689A144BFC7")]
    [ClassInterface(ClassInterfaceType.None)]
    public class Class1 : MyMiniSubs
    {

        int count;

        public Class1()
            {
                count = 0;
            }

            [ComVisible(true)]
            public int square(int x)
            {
                ++count;
                return x * x;
            }

            [ComVisible(true)]
            public int getCount()
            {
                return count;
            }
    }
}

enter image description here

answered Feb 20, 2014 at 7:15

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nsg Over a year ago

It worked for me too, thank you. One glitch: I had to restart visual studio as adminitrator as it was not able to update registry during build.

StephenH · Accepted Answer · 2014-02-19 20:13:39Z

0

The only 'gotcha', as far as I know, is that your C# code needs to be CLS compliant. You should be able to follow the instructions here to import your C# code as a .dll into your VB application. Disclaimer: I have not tried this myself.

EDIT: Here's a more official MSDN reference that talks about calling functions from a .dll.

answered Feb 19, 2014 at 20:13

StephenH

1,12611 silver badges24 bronze badges

3 Comments

nsg Over a year ago

I have seen some of these pages before. I am not even sure they are related to my problem. It seems like they are talking about interoperability and sharing classes between different .NET languages. What I need, is being able to interface C# .NET with non .NET VBA.

StephenH Over a year ago

Right, so you're mainly concerned with sending strings via TCP/IP, right? I'm assuming your current application has a relatively top-level function that takes the string and transmits it. So you need to build your application as a .dll and then essentially 'import' it into your VB application. You can then look at the third link I posted in my answer to help you understand how to call your top-level function from the .dll and pass the appropriate string.

nsg Over a year ago

right, my question is how do I make a C# class that is visible to VBA. I would prefer it as a "type library", so the method names and type names are visible through the vba IDE, but if that fails or proves too difficult, I could settle for simple DLL and calling DLL using "Import" statement of VBA. So far I have zero progress on both fronts. No matter what settings I choose or code I write in my C# project, the result is not usable from VBA.

Collectives™ on Stack Overflow

How to use c# class in Excel VBA?

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related