Calling WCF Services from Excel VBA clients using the WCF Service Moniker
Did you know there is a way to invoke simple Windows Communication Foundation (WCF) Services from Excel VBA without installing anything other than the .NET Framework on the Excel machine?
There are many different kinds of monikers, but the one that interests us is the Service moniker that can be used to access WCF Services:
Set someObject = GetObject(“service:Binding Information”)
In this blog post I’m going to show how you how to go about using WCF Services from Excel VBA, step by step, from beginning to end, and explain how to debug the rather obtuse messages that Excel displays when you’ve misconfigured something.
The WCF Service
The WCF Interface you expose can not be particularly complex if you want to use the Service Moniker – I’ve found it best to keep top primitive types, and arrays of primitive types. Data Contracts seem to be a no-go.
Start by creating a new WCF Service using File|New|Project and selecting WCF Service Library:
Double-click on IService1.cs in the Solution Explorer, and replace the default contents with this simple interface:
using System; using System.ServiceModel; namespace WcfService1 { [ServiceContract] public interface IService1 { [OperationContract] string GetData(int value); [OperationContract] object[] GetSomeObjects(); } }
Next replace the contents of the service implementation, Service1.cs, with the following:
using System; namespace WcfService1 { public class Service1 : IService1 { public string GetData(int value) { return string.Format("You entered: {0}", value); } public object[] GetSomeObjects() { return new object[] { "String", 123, 44.55, DateTime.Now }; } } }
Configuring the WCF Service
By default the WCF Service is configured to use HTTP as the transport protocol. I generally switch it to use TCP, because I am operating within a corporate intranet, and HTTP seems like overkill.
You’ll also find that by default your WCF Service exposes two endpoints. The first exposes as you’d expect, the IService1 interface you defined above. The second exposes Metadata about your service, which the Service Moniker uses to know what operations are available on your service. You’ll need both.
Right-click on the App.config file in the Solution Explorer, and select Edit WCF Configuration:
Switch the first endpoint to use TCP:
Also change the second to use mexTcpBinding:
Change the base address that the service will use, to use a TCP address instead of a HTTP Address by selecting the Host node on the left hand tree, and then selecting the base address and clicking on Edit, and changing the text to be net.tcp://localhost:7891/Test/WcfService1/Service1/
Finally, because you are using TCP instead of HTTP, change the MetataData service to not expect to expose the metadata via HTTP, by changing HttpGetEnabled to False under Advanced|Service Behaviours…
Save the changes and exit the WCF Editor. Your App.config should look like this:
xml version="1.0" encoding="utf-8" ?>
<configuration>
<system.web>
<compilation debug="true" />
</system.web>
<system.serviceModel>
<services>
<service behaviorConfiguration="WcfService1.Service1Behavior"
name="WcfService1.Service1">
<endpoint address="" binding="netTcpBinding" bindingConfiguration=""
contract="WcfService1.IService1">
<identity>
<dns value="localhost" />
</identity>
</endpoint>
<endpoint address="mex" binding="mexTcpBinding" bindingConfiguration=""
contract="IMetadataExchange" />
<host>
<baseAddresses>
<add baseAddress="net.tcp://localhost:7891/Test/WcfService1/Service1/" />
</baseAddresses>
</host>
</service>
</services>
<behaviors>
<serviceBehaviors>
<behavior name="WcfService1.Service1Behavior">
<serviceMetadata httpGetEnabled="False"/>
<serviceDebug includeExceptionDetailInFaults="False" />
</behavior>
</serviceBehaviors>
</behaviors>
</system.serviceModel>
</configuration>
To start running your service you can hit Ctrl-F5 in Visual Studio. This will start a test service host, and a test client. We will ignore the client, but you can check that the service host has started by clicking on the message in the notification area:
Eventually you’ll want to host your service somewhere else, such as a Windows Service.
The Excel VBA Client
Start Excel, and then hit Alt-F11 to enter the VBA Code editor. We’ll invoke the WCF Service whenever someone clicks on a cell in the first sheet. Double-click on Sheet1 on the top left hand side, and then select Worksheet from the drop down of objects:
The Service Moniker needs to know the address of the MetataData Service, the address of the service itself, binding information, and information about the contract.
Dim addr As String addr = "service:mexAddress=""net.tcp://localhost:7891/Test/WcfService1/Service1/Mex""," addr = addr + "address=""net.tcp://localhost:7891/Test/WcfService1/Service1/""," addr = addr + "contract=""IService1"", contractNamespace=""http://tempuri.org/""," addr = addr + "binding=""NetTcpBinding_IService1"", bindingNamespace=""http://tempuri.org/"""
Once you’ve built up the Service Moniker string, you can use GetObject to resolve it, and then invoke a method on it:
Dim service1 As Object Set service1 = GetObject(addr) MsgBox service1.GetData(12)
The VBA Editor should look like this:
I’ve set a breakpoint on the first line of code by clicking where the red circle is above.
If you now click on a cell in your sheet, and let the VBA code run, you should see this:
The VBA code has called through to the WCF Service and invoked a method on it, and the service has returned a string.
Change the code to invoke the second method, and step through in the debugger, and add a Watch on the result:
Whats up with the Tempuri?
You’ll have noticed that the moniker string references a couple of weird Tempuri namespaces. These are the defaults, but you can alter them. This can be useful if you have a single service exposing a couple of interfaces and you only want to work with one of them. For example your service may expose a complex interface for WPF clients, and a simple interface for a Excel client. If you give the simple interface a different namespace than the complex interface, you’ll be able to access it from Excel VBA.
To change the contract namespace, edit your interface code:
using System; using System.ServiceModel; namespace WcfService1 { [ServiceContract(Namespace="/")] public interface IService1 { [OperationContract] string GetData(int value); [OperationContract] object[] GetSomeObjects(); } }
Then your service moniker can be changed accordingly:
addr = addr + "contract=""IService1"", contractNamespace=""/"","
When it doesn’t work
The Excel message you get when the GetObject doesn’t work properly is not exactly detailed:
There is however a way of getting more detailed information. You can attach your debugger to Excel, and then see the underlying managed .NET exception.
To attach your debugger to Excel in Visual Studio use Debug|Attach to Process and then select Excel, and click Attach:
Next, make sure the debugger stops as soon as an exception occurs by clicking on Debug|Exceptions and check the checkbox to be interrupted when an exception is thrown:
Don’t forget that you have done this. If you leave it checked then later on when debugging unrelated code you may get thoroughly confused to have the debugger break inside framework code that throws and then handles an exception which is normally invisible to you.
You’ll also need to tell the debugger to break when code other than your own code throws an exception. Do this by clicking on Tools|Options and then going to the Debugging section, and uncheck the Enable Just My Code checkbox:
Now run your VBA code as usual, and when you get the error the Visual Studio Debugger will break with more detailed error information. So if I change my moniker string to use an invalid interface name the Excel VBA error is:
Whereas Visual Studio actually tells you what the error is:
The maximum message size quote for incoming messages has been exceeded
You’ll hit this error if your .NET code returns too much data to the Excel client. You might be able to resolve it by setting the MaxRecivedMessageSize property in an Excel.exe.config file in the same folder as Excel.exe. They way I have handled it is to move from using the Service Moniker mechanism, and to create a managed Excel AddIn that sets the maximum message size programmatically.
Summary
The WCF Service Moniker offers an excellent way for you to invoke simple WCF Interfaces, where small quantities of data are exchanged, without installing anything at all on the Excel Client machine.
In the above examples the service moniker created by the VBA code used the “localhost” address for the service, but if your service is running on another machine you can specify that machine’s address: the service can run anywhere.