ATL C++ Automation Add-in gives another way to call C++ from worksheet

I discovered this by accident. I was attempting to get a C# Automation Add-in working (conceptually a very easy task but I keep hitting a barrier) and I got frustrated so I dropped down into C++ to try to get a feel for what is going on. I was surprised to find a simple ATL C++ COM component callable from the worksheet simply by adding the registry key "Programmable" to the registry entries for the COM class.

Automation Add-ins

So there have been various Office addins over the years. Microsoft keeps moving the goalposts as to what it wants developers to build to augment Excel's functionality. As I write, the latest Microsoft is pushing is JavaScript based. But years ago, Automation Add-Ins and COM Add-Ins were trendy.

An Automation Add-in allows developers to create COM components and make them callable from an Excel worksheet function with the addition of the registry key "Programmable" to the registry entries for the COM class. I must confess to associating this primarily with the .NET languages, C# and VB.Net. C++ programmers have always has the C++ Excel Software Development Kit, so I thought Automation Add-ins were pretty much was ignored by C++ developers. Certainly amongst my C++ colleagues they were overlooked.

So it was to my surprise that an ATL project is callable from the worksheet. I may very well post more examples on this. Below you will some simple code but also a video of a part of my investigations.

Incidentally, a COM Add-in goes one better than an Automation Add-in in that the developer can acquire the Application object and thus can script against the Excel COM object library. But that is not discussed in this blog post.

Simple ATL Object

So I added a single Simple ATL Object to a brand new ATL project. And then I declared the interface to have one single method called DivideBy2 for experimentation. The IDL is given here

ATLProject5.idl

1. // ATLProject5.idl : IDL source for ATLProject5
2. // Brought to you by the Excel Development Blog https://exceldevelopmentplatform.blogspot.com/2018/11/
3.  
4. // This file will be processed by the MIDL tool to
5. // produce the type library (ATLProject5.tlb) and marshalling code.
6.  
7. import "oaidl.idl";
8. import "ocidl.idl";
9. import "shobjidl.idl";
10.  
11. [
12.     uuid(B4644DBF-2A3D-4CE7-8A01-B83AAFEBA1F2),
13.     version(1.0)
14. ]
15. library ATLProject5Lib
16. {
17.     importlib("stdole2.tlb");
18.  
19.     // Forward declare all types defined in this typelib
20.     interface IATLSimpleObject;
21.  
22.     [
23.         uuid(1a4a53f8-5323-418f-8975-d05f47c1dceb),
24.         version(1.0),
25.     ]
26.     interface IATLSimpleObject : IDispatch
27.     {
28.         HRESULT DivideBy2([in]double dIn, [out,retval] double* dOut);
29.     };
30.  
31.     [
32.         uuid(1ABD0403-A8D5-40F6-8D9E-E0343999CD65),
33.         version(1.0)
34.     ]
35.     coclass ATLSimpleObject
36.     {
37.         [default] interface IATLSimpleObject;
38.     };
39. };

ATLSimpleObject.h

The edited class declaration is given here, in the video below I put a breakpoint in the interface map to see what gets interfaces get queried for.

1. // ATLSimpleObject.h : Declaration of the CATLSimpleObject
2. // Brought to you by the Excel Development Blog https://exceldevelopmentplatform.blogspot.com/2018/11/
3.  
4.  
5. using namespace ATL;
6.  
7.  
8. // CATLSimpleObject
9.  
10. class ATL_NO_VTABLE CATLSimpleObject :
11.     public CComObjectRootEx<CComSingleThreadModel>,
12.     public CComCoClass<CATLSimpleObject, &CLSID_ATLSimpleObject>,
13.     public IDispatchImpl<IATLSimpleObject, &IID_IATLSimpleObject, &LIBID_ATLProject5Lib, /*wMajor =*/ 1, /*wMinor =*/ 0>
14. {
15. public:
16.     CATLSimpleObject()
17.     {
18.     }
19.  
20. BEGIN_COM_MAP(CATLSimpleObject)
21.     COM_INTERFACE_ENTRY(IATLSimpleObject)
22.     COM_INTERFACE_ENTRY(IDispatch)
23. END_COM_MAP()
24.  
25.  
26.     DECLARE_PROTECT_FINAL_CONSTRUCT()
27.  
28.     HRESULT FinalConstruct()
29.     {
30.         return S_OK;
31.     }
32.  
33.     void FinalRelease()
34.     {
35.     }
36.  
37. public:
38.  
39.     STDMETHOD(DivideBy2)(double dIn, double* dOut);
40.  
41. };
42.  
43. OBJECT_ENTRY_AUTO(__uuidof(ATLSimpleObject), CATLSimpleObject)

ATLSimpleObject.cpp

The class implementation is trivial

1. // ATLSimpleObject.cpp : Implementation of CATLSimpleObject
2. // Brought to you by the Excel Development Blog https://exceldevelopmentplatform.blogspot.com/2018/11/
3.  
4. #include "stdafx.h"
5. #include "ATLSimpleObject.h"
6.  
7. // CATLSimpleObject
8.  
9. STDMETHODIMP CATLSimpleObject::DivideBy2(double dIn, double* dOut)
10. {
11.     *dOut = dIn / 2;
12.     return S_OK;
13. }

Video of QueryInterface Investigation

So I discovered ATL being callable from the worksheet as I was investigating what interfaces neeed to be implemeneted. I made a video of my investigations where I set a breakpoint here...

Video Links

So there were some links in the video regarding the Sharepoint interface and the PowerBasic forum and these are given here

• ISharePoint - Custom Document Parsers in Share Point
• Excel com automation addin - PowerBASIC Peer Support Community