We help IT Professionals succeed at work.
Get Started

Call C# function in Excel VBA; not working

1,139 Views
Last Modified: 2013-11-17
Hi,
I am implementing a COM interface in C#.NET to be used in Excel.  My C# class inherits from two different interfaces, both of which have Guid attributes as shown below (i have simplified the classes):

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

namespace Test
{
    [Guid("92AF729B-299C-4d1b-928F-F148E43C061C")]
    public interface ITrade
    {
        void AddTradeCharge(TradeCharge tradeCharge);
        bool ValidateTrade();
    }

   [Guid("DF8B55FE-FFCF-402e-ADEE-9298E3B0CBE4")]
    public interface IDataContainer
    {
        bool Load();
    }
   
    [Guid("1D67F24F-7B62-42d1-86C3-BF92587E3FA9")]
    [ClassInterface(ClassInterfaceType.None)]
    [ComSourceInterfaces(typeof(IDataContainerEvents))]
    public class Trade : DataObject, IDataContainer, ITrade
    {
        public Trade() {}    
        private TradeSet mcharges;  
        public TradeSet TradeCharges
       {
             get{ return mcharges;}
             set(mcharges = value; }
       }

        public bool ValidateTrade()
        {
            return ExecuteCommand("InsertTradeOnly", true, true, false);
        }
        public void AddTradeCharge(TradeCharge tradeCharge)
        {
            TradeCharges.Add(tradeCharge);
        }
        public void Load()
        {
             //some code
        }
    }
}

The problem that i have is when i call ValidateTrade from Excel VBA, it doesn't actually run the function and i get the following error: Object doesn't support this property or method.

The other problem is that when i call .AddTradeCharge (dtTradeCharge) in VBA, i have a compile error which says "argument not optional".  I have been working on this for absolute AGES!  I have tried recompiling the C# code, re-registering the assembly and adding the reference to the tlb in Excel again and restarting my machine, but to no avail.

Also, I am not seeing all of my exposed methods from both interfaces in intellisense.  If i remove ITrade interface from the Trade class, then in Excel the intellisense shows IDataContainers methods and if i leave remove IDataContainer and leave ITrade then intellisense shows ITrade methods.  Is there a way to
around this problem?

I have run these methods using NUnit to test that they do work and I get the correct outputs.

Please help!  It is very frustrating!

Many Thanks
Comment
Watch Question
Managing Director
Commented:
This problem has been solved!
Unlock 1 Answer and 7 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE