How do I use a .NET class in VBA? Syntax help!

Question

ok I have couple of .NET classes that I want to use in VBA. So I must register them through COM and all that. I think I have the COM registration figured out (finally) but now I need help with the syntax of how to create the objects. Here is some pseudo code showing what I am trying to do.

EDIT: Changed Attached Objects to return an ArrayList instead of a List

The .NET classes look like this...

public class ResourceManagment
{
    public ResourceManagment()
    {
        // Default Constructor
    }

    public static List<RandomObject> AttachedObjects()
    {
        ArrayList list = new ArrayList();
        return list;
    }
}

public class RandomObject
{
    // 
    public RandomObject(int someParam)
    {

    }

}

OK, so this is what I would like to do in VBA (demonstrated in C#) but I don't know how...

public class VBAClass
{
    public void main()
    {
        ArrayList myList = ResourceManagment.AttachedObjects();
        foreach(RandomObject x in myList)
        {
            // Do something with RandomObject x like list them in a Combobox
        }
    }
}

One thing to note is that RandomObject does not have a public default constructor. So I can not create an instance of it like Dim x As New RandomObject. MSDN says that you can not instantiate an object that doesn't have a default constructor through COM but you can still use the object type if it is returned by another method... Types must have a public default constructor to be instantiated through COM. Managed, public types are visible to COM. However, without a public default constructor (a constructor without arguments), COM clients cannot create an instance of the type. COM clients can still use the type if the type is instantiated in another way and the instance is returned to the COM client. You may include overloaded constructors that accept varying arguments for these types. However, constructors that accept arguments may only be called from managed (.NET) code.

Added: Here is my attempt in VB:

Dim count As Integer
count = 0
Dim myObj As New ResourceManagment
For Each RandomObject In myObj.AttachedObjects
    count = count + 1
Next RandomObject

Answer 1

Your problem is that the AttachedObjects() method is static. COM cannot do static methods. In COM, the only "static" operation you can do is "instantiate a class".

Therefore, in order to call the AttachedObjects method from VBA, simply make it non-static (i.e. remove the static keyword in its definition). Other than that, the method may remain exactly as it is now. And your VBA code seems OK too - should work once you modify the method.

And just in case it doesn't work, here's the next question: what error do you get exactly, and at what point?

Answer 2

to get around this you need to create a RandomObjectFactory in your .net code and have this create the RandomObject instance to use in VBA

so something like this:

public class RandomObjectFactory
{
     public static Create(int someParam)
     {
          return new RandomObject(someParam);
     } 
}

you can extend this class to have more overloads of create methods to create different overloads of RandomObjects

UPDATE:

based on the correct understanding of the question this question and this one will probably be useful