C++ code to debug/investigate COM class creation problems

With technology sometimes debugging and diagnostics are required, COM is no exception. Presently, I have been debugging a Python COM class that was not instantiating in VBA using the New keyword. That code works now, so watch out for a post on that soon. The following code was to be an appendix to that post but is useful in its own right so I am depositing it here.

Low level C++ Code to instantiate a COM Component

This code is to probe error messages of a COM component instantiation. VBA gives some error codes which are not necessarily useful. In VBA, when an object is created with the New keyword, 'under-the-hood' a call to CoCreateInstance is made, which in turn is made up of calls to CoGetClassObject to get a class factory and then a call to CreateInstance is called on the class factory. These steps which are implicit to VBA are given explicitly below in C++ so one can step through and better diagnose any errors.

In you want to use this code you will no doubt have to change CLSID_FooBar and IID_FooBar for your specific case.

Problems that can be examined with this technique include but are not limited to (a) registration issues, (b) path problems, (c) 32bit/64 bit mismatch problems.

The code is for a C++ console application.

#include <iostream>
#include "objbase.h"
#include <combaseapi.h>
#include <assert.h>

int main()
{
 ::CoInitialize(0);
 HRESULT hr = S_OK;

 GUID CLSID_FooBar;
 CLSIDFromString(L"{25F9C67B-8DBB-4787-AA84-D3D667ED0457}", &CLSID_FooBar);

 GUID IID_FooBar;
 CLSIDFromString(L"{B8FFDEFA-3EFB-4725-8CDD-1F6A9E35DD7C}", &IID_FooBar);

 GUID IID_IUnknown;
 CLSIDFromString(L"{00000000-0000-0000-c000-000000000046", &IID_IUnknown);

 GUID IID_IDispatch;
 CLSIDFromString(L"{00020400-0000-0000-c000-000000000046", &IID_IDispatch);

 GUID IID_IClassFactory;
 CLSIDFromString(L"{00000001-0000-0000-c000-000000000046", &IID_IClassFactory);

 IClassFactory *pFactoryFooBar;
 // CLSCTX_INPROC_SERVER |  CLSCTX_LOCAL_SERVER

 { // Test 1 Create the FooBar class via class factory requesting IUnknown 
  hr = ::CoGetClassObject(CLSID_FooBar, CLSCTX_INPROC_SERVER, NULL, IID_IClassFactory, reinterpret_cast<void**>(&pFactoryFooBar));
  assert(S_OK == hr);

  IUnknown *pUnkFooBar;
  hr = pFactoryFooBar->CreateInstance(NULL, IID_IUnknown, reinterpret_cast<void**>(&pUnkFooBar));
  assert(S_OK == hr);

  IUnknown *pFooBar;
  hr = pUnkFooBar->QueryInterface(IID_FooBar, reinterpret_cast<void**>(&pUnkFooBar));
  assert(S_OK == hr);

 }

 IDispatch *pDispFooBar;
 hr = CoCreateInstance(CLSID_FooBar, NULL, CLSCTX_INPROC_SERVER, IID_IDispatch,
  reinterpret_cast<void**>(&pDispFooBar));
 assert(S_OK == hr);

 // get disp id
 DISPID id = -1; //default
 LPOLESTR string = const_cast <LPOLESTR>(L"Sum");
 hr = pDispFooBar->GetIDsOfNames(IID_NULL, &string, DISPATCH_METHOD, LOCALE_USER_DEFAULT, &id);
 assert(S_OK == hr);

 UINT ctinfo = -1;
 hr = pDispFooBar->GetTypeInfoCount(&ctinfo);
 assert(S_OK == hr);

 ::CoUninitialize();

 
}

P.S. It is possible to drill down even further because many COM servers are implemented as DLLs, we could write code to load the DLL into memory and then manually get the entry point DllGetClassFactory and call into it to get the class factory manually. One for another day perhaps because at the moment I am working with Python which does not use Dlls in that sense (at least I don't think so)

Sharepoint Excel Services for server-side Excel calculations

For many years Excel was limited to client-side, i.e. desktop installation, operation. However, I did work on many projects where we launched an instance of Excel on a server as a calculation agent despite Microsoft recommending against because this option is fraught with unforeseen consequences. For example, message boxes get thrown but on a server there is no user to see and dismiss them. Microsoft invented Sharepoint for clients who really want to run Excel on the server.

The downsides of Sharepoint are cost, it is not cheap in itself and also it requires a Windows Server licence. If your employer is pro-Unix then this will be a deal-breaker. The other downside of Sharepoint is that your VBA code will not run and will have to be converted to C# or some other managed .NET language.

Nevertheless, the Sharepoint 'market share' of the Excel solution space will grow and a blog on Excel Development should address it.

I've yet to reach recommendations for this technology so this first blog post will solely be a collection of links for more reading.

Wikipedia is always good place to start, here is article on Excel Services

Office Support: Getting Started with Excel Services and Excel Web Access

Book: Wrox - Professional Excel Services

Technet: Overview of Excel Services in SharePoint Server 2013

Technet: Administer Excel Services in SharePoint Server 2013

safaribooksonline: Chapter 1. An Introduction to Excel Services

Technet Forums: VBA won't work for workbooks rendered in a browser from a Sharepoint server (Excel Services)

Office Dev Center: Understanding Excel Services UDFs

Office Dev Center: Walkthrough: Developing a Managed-Code UDF

MSDN: Creating Custom Solutions with Excel Services

Some sample C# code for a server-side Excel Services UDF.

// From https://dev.office.com/sharepoint/docs/general-development/step-2-creating-a-managed-code-udf
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Office.Excel.Server.Udf;

namespace SampleUdf
{
    [UdfClass]
    public class Class1
    {
        [UdfMethod]
        public double MyDouble(double d)
        {
            return d * 9;
        }  

        [UdfMethod(IsVolatile = true)]
        public DateTime ReturnDateTimeToday()
        {
            return (DateTime.Today);
        }
    }
}

However, if you thought getting a UDF to run on both the client and the server in a unified code base would be a breeze then check out this web page which shows how to do it. It seems a major undertaking requiring C#, C++/CLI (formerly Managed C++), creating code to handle a managed Add-in and a custom shim. I expect many to be dissuaded from using SharePoint because of the complexity there but I also expect Microsoft to unify the client side and server interfaces in the future.

Whilst I have yet to reach recommendations on Sharepoint and Excel Services one has to wonder why rendering a workbook in a browser which requires a Windows Server, Sharepoint Server and Internet Information Server is better than keeping a workbook of a network file server (running on Linux) and opening in Excel. I guess the question boils down to client licences versus server licences.

ATL Notes 2 - Only Inherit from IDispatch Once

Continuing on ATL (Active Template Library) ...

So when writing an object with ATL for use in Excel VBA I recommend selecting dual interface from the Simple Object Wizard this gives both a binary vtable interface for early-binding clients as well as an IDispatch implementation for late-binding clients. Note, when I say 'late binding clients' I'm not talking about VBScript clients but VBA developers who'd prefer to call into a COM library with flexibility.

But there is a fly in the ointment here. COM is all about interface based programming where one is supposed to factor out the functionality of a class into separate interfaces, multiple dual interfaces will not compile in ATL because every Com class must only inherit from IDispatch once.

It isn't just the use case of factoring out functionality into separate interfaces but also the use case of shipping an additional interface to supplement, update or enhance the functionality of a class. In both use cases ATL will only allow one of these interfaces to be dual. To resolve the ATL compilation errors you will need designate one interface as being prime and only allow late-binding to that interface.

This pretty much scuppers the factoring out use-case. Developers coming from VBA will probably not grieve over this as they are used to using one interface only (VBA does have the Implements keyword but is rarely used). For the shipping updates use-case you should ship a new Com class with a new expanded all encompassing interface.

I could give some sample code but actually I have found some internet pages that illustrate the problem.

com problem inheriting twice from IDispatch
ATL inheritance mistake
They also give the solution which is to make one interface the goto interface when QueryInterface is called for IDispatch by using the COM_INTERFACE_ENTRY2 macro. So the following non-compiling code

BEGIN_COM_MAP(CAudioRecorder) 
        COM_INTERFACE_ENTRY(IAudioDevice) 
        COM_INTERFACE_ENTRY(IAudioRecorder)   <<<< ERROR 1 
        COM_INTERFACE_ENTRY(IDispatch)        <<<< ERROR 2 
END_COM_MAP()

should be changed to the following to direct QueryInterface calls for IDispatch to IAudioDevice

BEGIN_COM_MAP(CAudioRecorder) 
        COM_INTERFACE_ENTRY(IAudioDevice) 
        COM_INTERFACE_ENTRY(IAudioRecorder)   
        COM_INTERFACE_ENTRY2(IDispatch,IAudioDevice)        
END_COM_MAP()

When Is Late Binding Advantageous for VBA Developers?

What is Late Binding and when would you use it?

Syntax of Late Binding

In syntax terms, late binding is when you declare a variable with As Object instead of giving its Type. So for example scripting against the Excel object ...

Option Explicit

Sub Test()

    '* Early-bound - requires Type Library (Tools References)
    Dim ws As Excel.Worksheet
    
    '* Late-bound - uses IDispatch - slower but more flexible
    Dim objWs As Object
    
    Set ws = ThisWorkbook.Worksheets.Item(1)
    Set objWs = ThisWorkbook.Worksheets.Item(1)
    Debug.Print ws.Name
    Debug.Print objWs.Name
End Sub

In the above example objWs can access all of the methods of Worksheet without reference to a Type Library (Tools References) it does this via the IDispatch interface which is a flexible dynamic type discovery interface that interrogates an object at run-time instead of at compile time.

When would you use Late Binding?

Well the above example is not a good use case. There is no need to use late binding when the library you are scripting against is written and published by a proper software house such as Microsoft. Late-binding was invented for scripting clients like VBScript. However, VBA developers can find advantage from using late-binding when working in a large organisation and co-operation between development teams is not optimal.

At some point in your career, you may find your VBA code depends on a library from another team in your large organisation. The rules of COM interfaces are clear, don't break an interface once published, new behaviour needs to ship in a new interface or also in a new object that goes with the new interface. But sh*t happens, a team changes an interface which breaks your VBA code, how is the break handled?

If you are using early binding and type libraries then you will get a compile error. Imagine that scenario, you get a support call from an important user saying your spreadsheet broke along the lines of "Compile error in hidden module:foo"
You would have to visit (or remote into) their machine, unlock the code (assumes password protection) and go figure out what went wrong. This can be quite embarrassing.

On the other hand if you are using late-binding then your code will only break on a line of code directly affected with the error message

This kind of error is trappable with On Error Goto whilst a compile error is not!

What are downsides to Late-Binding?

Well, you lose Intellisense which can be a boon to writing code but there is nothing to stop you from writing code with early-binding and then changing to late binding when going to test and production.

Also, we have to address speed. There is no doubt that the extra calls into IDispatch carry a performance penalty though for in-process DLL calls I'd say this is minimal. When you have a remote component then I would suggest not using late-binding because network calls are very expensive. For components running locally on the computer but in another process, the call is 50/50.

Summary

Although IDispatch was invented for scripting clients like VBScript it also provides an option for VBA developers to defend against breaking changes in type libraries from other teams.

I'm not religious about this issue but the having the option to late-bind requires the developers of the component you rely on to ship an IDispatch implementation and sometimes this has its own consequences.

I've just rustled up this post because I want to address an ATL design dilemma and needed to sketch out the stakes.

DispCallFunc opens a new door to COM interfaces and VBA Function Pointers

So during my research on Type Information it seems that Dispatch carries with it some amazing type querying functionality. Not only that it seems IDispatch and Type Libraries interrogation interfaces are pretty much the same

I always knew IDispatch had to do some work but looking into it seems both amazing and fascinating. Also, it seems that there are some COM system functions that act as low level helpers, one such function is DispCallFunc found in oleaut32.dll.

A clever man called Krivous Anatolii Anatolevich has used this to allow VBA programmers call into IClassFactory::CreateInstance; a problem I solved only with resort to C++/VBA hybrid code. Lots of interesting stuff there at his GitHub page.

Another clever man Mikael Katajamäki has discovered how to use DispCallFunc to form part of a solution for a recursive mathematical root finder. His code is over at Windows API DispCallFunc as function pointer in VBA

Whilst VBA already has AddressOf and Application.OnTime, often it is required to pass parameters to a callback function. DispCallFunc may give the solution to that problem instance.

This idea is debated in an excellent thread discussion over at vbforums.com

As a footnote and coda and not related to DispCallFunc is an incredible project that writes assembly code into memory before calling it. This can be found at wonderful website called FreeVBcode.com to which I shall be returning. The said code is found at Call API functions by Name, without Declare, v 2.0