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

VBA - Using WorksheetFunction in code doesn't have to throw an error

So one can use worksheet function from code from two different points in the object model (i) Application (ii) Application.WorksheetFunction. If you use the latter any errors will throw an error which you must catch with On Error. If you want to run with the error in the same way that the worksheet does then use the former and test with IsError. Here is some code which illustrates

Option Explicit

Sub Test_Application_WorksheetFunction_VLookup()
    Dim rng As Excel.Range
    Set rng = ThisWorkbook.Sheets.Item(1).Range("A1:b3")
    
    rng.Value = Application.Evaluate("{""Red"",1;""Green"",2;""Blue"",4}")


    Debug.Assert Application.WorksheetFunction.VLookup("Red", rng.Value, 2, False) = 1
    Debug.Assert Application.WorksheetFunction.VLookup("Green", rng.Value, 2, False) = 2
    Debug.Assert Application.WorksheetFunction.VLookup("Blue", rng.Value, 2, False) = 4
    
    Dim lSavedError As Long, sSavedError As String
    On Error Resume Next
    Dim vLU As Variant
    vLU = Application.WorksheetFunction.VLookup("Yellow", rng.Value, 2, False)
    sSavedError = Err.Description
    lSavedError = Err.Number
    On Error GoTo 0

    Debug.Assert lSavedError = 1004
    Debug.Assert sSavedError = "Unable to get the VLookup property of the WorksheetFunction class"
    Debug.Assert IsEmpty(vLU)

End Sub


Sub Test_Application_VLookup()
    Dim rng As Excel.Range
    Set rng = ThisWorkbook.Sheets.Item(1).Range("A1:b3")
    
    rng.Value = Application.Evaluate("{""Red"",1;""Green"",2;""Blue"",4}")


    Debug.Assert Application.WorksheetFunction.VLookup("Red", rng.Value, 2, False) = 1
    Debug.Assert Application.WorksheetFunction.VLookup("Green", rng.Value, 2, False) = 2
    Debug.Assert Application.WorksheetFunction.VLookup("Blue", rng.Value, 2, False) = 4
    
    Dim vLU As Variant
    vLU = Application.VLookup("Yellow", rng.Value, 2, False)

    Debug.Assert Not IsEmpty(vLU)
    Debug.Assert IsError(vLU)

End Sub