How to get my .net dll to be referenced in vba/com?

Question

hi guys, i have a .net dll which is written in c sharp, which uses linq to read data and return it to another calling vba app. i didn't plan for this so the initial project was written for it to be accessible by another .net app. so now i just found out that there's a lot of stuff about .net com interoperability and what not.

so i've learned that i need to user interface to make it work, and i need to regasm the dll to create a type library file which i can reference directly from the vba/vb6 app.

as of now i'm getting the problem of when i do this Dim obj As DtasApiTool.Program, it's fine but on the next line set obj = new DtasApiTool.Program will cause an error about New operator not being used properly. when i tested another .net dll from codeproject it works fine.

so my question is, what am i doing wrong here?

1. am i using too many references, as in the using system.xxx?
2. or is it due to some files that i have in the projects, i.e. app.config file, etc.
3. and how do i get the guid?

as i have very limited knowledge or experience in all of this ,i'm basing what is right or wrong from the example code in code project :( so feel free to comment on anything.

this is the code that i'm using:

using System;
using System.Collections.Generic;

using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.OleDb;
using System.Data;
using System.Xml;
using System.Xml.Linq;
using System.IO;
//using System.Security;
using System.Security.Cryptography;
using System.Runtime.InteropServices;
//using System.Windows.Forms;

namespace DtasApiTool
{
    [Guid("D6F88E95-8A27-4ae6-B6DE-0542A0FC7059")]
    [InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
    public interface _Program
    {
    [DispId(1)]
    string Get_All_Locales();

    [DispId(2)]
    string Get_All_Levels(string locale);

    [DispId(3)]
    string Get_Subjects_ByLocaleLevelId(string locale, int levelId);

    [DispId(4)]
    string Get_Topic_ByLevelIdLocaleSubjectId(int levelId, string locale, int subjectId);

    [DispId(5)]
    string Get_Subtopic_ByLevelIdLocaleSubjectIdTopicId(int levelId, string locale, int subjectId, int topicId);

    [DispId(6)]
    string Get_Skill_ByLevelIdLocaleSubjectIdTopicIdSubtopicId(int levelId, string locale, int subjectId, int topicId, int subtopicId);

    [DispId(7)]
    string Get_All_Subjects(string locale);

}

[Guid("09FE32AD-4BF8-495f-AB4D-6C61BD463EA4")]
[ClassInterface(ClassInterfaceType.None)]
[ProgId("DtasApiTool.Program")]
public class Program : _Program
{
...}
}

Answer 1

Hm - I'm using C# DLLs all the time with COM and never had problems. I'm not even using the interface-approach you have there. This is, for example, an anonymized part of a DLL I'm using in Microsoft Dynamics NAV via COM:

using ...;

namespace SomeCOMTool
{
    [ClassInterface(ClassInterfaceType.AutoDual)]
    [ProgId("MyCOMTool")]
    [ComVisible(true)]
    [Guid("your-guid-here without curly brackets!")]
    public class MyCOMTool
    {
        /// <summary>
        /// Empty constructor used by Navision to create a new instance of the control.
        /// </summary>
        public MyCOMTool()
        {
        }

        [DispId(101)]
        public bool DoSomething(string input, ref string output)
        {
        }
    }
}

Of course "COM visible" is set in the assembly options and there's also a GUID in the respective field.

Answer 2

see this --> http://stackoverflow.com/questions/1012655/automation-error-in-vb-6-0-from-a-c-class