Calling a .net library method from vba

Question

I have developed a web service in ASP.net, c#, and hosted on IIS, which is to be consumed by a vba client. Having downloaded the Office 2003 Web Services 2.01 Toolkit, I encountered a problem in successfully creating the proxy classes required (as documented by many users online), and decided to create a .net dll library instead. I have created the library, which references the web service and exposes one of its methods to a public function in c#.

I now have three questions:

1. How do I reference the dll class in VBA? I tried to go to Tools->References and browsed to the dll location, but I get the error "can't add reference to the file specified". Is there a specific location on the disk I have to have the .dll copied?

2. Can I also copy the dll.config file next to the dll file, so as to have the endpoint url there?

3. Since the method to call is accepting an object (consisting of various members and a couple of List<> members, how are these to be implemented in VBA code?

Answer 1

You will need to make a COM-callable wrapper (CCW) for your assembly (DLL). .NET interoperability is a fairly in-depth topic, but it's relatively easy to get something off the ground.

First of all, you need to make sure your entire assembly is registered for COM interop. You can do this on the "Build" tab in Visual Studio by checking "Register for COM Interop". Secondly, you should include the System.Runtime.InteropServices in all your classes:

using System.Runtime.InteropServices; 

Next, you should decorate all the classes you want to be exposed with the [Serializable(), ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)] attributes. This will make it so you can access the class members properly and using intellisense from within the VBA editor.

You need to have an entry point -- i.e. a main class, and that class should have a public constructor with no arguments. From that class, you can call methods which return instances of your other classes. Here is a simple example:

using System; using System.Collections.Generic; using System.Runtime.InteropServices; using System.Linq; using System.Text; using System.Windows.Forms;  namespace MyCCWTest {     [Serializable(),  ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)]     public class Main     {         public Widget GetWidget()         {             return new Widget();         }     }      [Serializable(), ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)]     public class Widget     {         public void SayMyName()         {             MessageBox.Show("Widget 123");         }     } } 

Once you compile your assembly, you should be able to include a reference to it within VBA by going to "Tools > References":

Then you should be able to access your main class and any other classes like this:

Sub Test()     Dim main As MyCCWTest.main     Set main = New MyCCWTest.main     Dim myWidget As MyCCWTest.Widget     Set myWidget = main.GetWidget     myWidget.SayMyName End Sub 

To answer your question about List<>: COM doesn't know anything about generics, so they're not supported. In fact, using arrays in CCW's is even a tricky subject. In my experience, I've found the easiest thing to do is to create my own collection classes. Using the example above, I could create a WidgetCollection class. Here is a slightly-modified project with the WidgetCollection class included:

using System; using System.Collections; using System.Collections.Generic; using System.Runtime.InteropServices; using System.Linq; using System.Text; using System.Windows.Forms;  namespace MyCCWTest {     [Serializable(),  ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)]     public class Main     {         private WidgetCollection myWidgets = new WidgetCollection();          public Main()         {             myWidgets.Add(new Widget("Bob"));             myWidgets.Add(new Widget("John"));             myWidgets.Add(new Widget("Mary"));         }          public WidgetCollection MyWidgets         {             get             {                 return myWidgets;             }         }     }      [Serializable(), ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)]     public class Widget     {         private string myName;          public Widget(string myName)         {             this.myName = myName;         }          public void SayMyName()         {             MessageBox.Show(myName);         }     }      [Serializable(), ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)]     public class WidgetCollection : IEnumerable     {         private List<Widget> widgets = new List<Widget>();          public IEnumerator GetEnumerator()         {             return widgets.GetEnumerator();         }          public Widget this[int index]         {             get             {                 return widgets[index];             }         }          public int Count         {             get             {                 return widgets.Count;             }         }          public void Add(Widget item)         {             widgets.Add(item);         }          public void Remove(Widget item)         {             widgets.Remove(item);         }     } } 

And you can use it like this in VBA:

Sub Test()     Dim main As MyCCWTest.main     Set main = New MyCCWTest.main     Dim singleWidget As MyCCWTest.Widget      For Each singleWidget In main.myWidgets        singleWidget.SayMyName     Next End Sub 

NOTE: I have included System.Collections; in the new project so my WidgetCollection class can implement IEnumerable.