CODEWITHSUNDEEP
c#vbaexcel
nsg
nsg

Reputation: 539

How to use c# class in Excel VBA?

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;
            }
        }
    }

Upvotes: 4

Views: 6488

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149325

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

Upvotes: 4

StephenH
StephenH

Reputation: 1126

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.

Upvotes: 0

Related Questions