Search code examples
c++excelcomactivexatl

What requirements are necessary and sufficient for an ActiveX control to be used directly on an Excel worksheet?

The Microsoft Office support article "Add or register an ActiveX control" says:

IMPORTANT: Not all ActiveX controls can be used directly on worksheets; some can be used only on Microsoft Visual Basic for Applications (VBA) UserForms. When you work with these controls, Excel displays the message Cannot insert object if you try to add them to a worksheet.

However, I cannot find documented anywhere the requirements that are necessary and sufficient for a control to be used directly on a worksheet.

I have created a new C++/ATL Project, to which I have added an ATL Control, accepting the defaults throughout. After compiling, building and registering the control, it appears in Excel's list of "More Controls" (accessed under Developer > Insert > ActiveX Controls > More Controls...) but upon attempting to insert into the worksheet one sees this "Cannot insert object" error.

What changes must I make to fix this?

OR

Where are Excel's requirements of ActiveX controls documented?

For what it's worth, I've verified that the control generated by the wizard does otherwise work fine (tested with ActiveX Control Test Container, which I built from the Visual C++ 2008 samples pack).

Furthermore, I'm aware that the documentation for the ATL Control wizard's "Appearance" tab describes the "Insertable" checkbox as follows:

Select this option to have your control appear in the Insert Object dialog box of applications such as Word and Excel. Your control can then be inserted by any application that supports embedded objects through this dialog box.

However, this checkbox (which simply adds the "Insertable" subkey to the registry), only causes the control to appear in the Insert > Text > Object dialog—for the avoidance of doubt, I have tried both with and without this checked and the same error is produced either way.

I'm currently comparing traces of Excel's execution paths when attempting to insert my control against that when attempting to insert a working (Forms 2.0) control. The key difference appears to lie in VBE7.dll whilst loading the type library (which the OLE/COM Object Viewer is able to load correctly from my DLL—yet after Excel has performed all the same reads therefrom, it aborts before writing out an EXD)... I'm digging through some assembly right now in the vain hope that I'll figure it out—but surely someone who has built a working control for Excel and knows what I'm missing can spare me this pain?!

Microsoft Windows 10 Pro v1511 (10.0.10586.164) 64-bit
Microsoft Excel 2016 MSO (16.0.4312.1000) 64-bit
Microsoft Visual Studio Community 2015 (14.0.24720.00 Update 1)

Solution

  • To implement an ATL ActiveX Control insertable into MS Excel Sheet, follow these steps:

    1. Make sure you don't have cached ActiveX control information *.exd files in C:\Users\$(UserName)\AppData\Local\Temp\Excel8.0 which might be an unobvious obstacle on the way

    2. Create an ATL DLL project with all defaults

    2.1. Add x64 configuration as a copy of already existing Win32 - for 64-bit Excel you will need 64-bit ActiveX control

    1. Add ATL Control class using wizard

    enter image description here

    3.1. Make sure to fill ProgID field

    enter image description here

    3.2. Add IPersistStreamInit on the Interfaces page

    enter image description here

    1. Build the DLL and have it registered (regsvr32)

    2. In Excel the new control is visible in menu Developer, ..., More Controls

    enter image description here

    enter image description here

    1. Insert it and have fun from there

    enter image description here

    Source Code: Subversion/Trac

    UPDATE: A question from comments below:

    ...whether Excel supports windowless activation?

    To see control operation in action let's add some code around there:

    CSample()
{
    CTrace::SetLevel(4);

    and 

    HRESULT OnDraw(ATL_DRAWINFO& di)
{
    const CComQIPtr<IOleInPlaceSiteWindowless> pOleInPlaceSiteWindowless = m_spClientSite;
    ATLTRACE(_T("m_spClientSite 0x%p, pOleInPlaceSiteWindowless 0x%p, m_hWnd 0x%08X\n"), m_spClientSite, pOleInPlaceSiteWindowless, m_hWnd);

    This going to print out the members of the control that help identification of windowed/windowless mode. The output is (eventually after activating the object or right from the start):

    ...
Sample.h(118) : atlTraceGeneral - m_spClientSite 0x0000027A9CA7B460, pOleInPlaceSiteWindowless 0x0000000000000000, m_hWnd 0x0105069C
...
Sample.h(118) : atlTraceGeneral - m_spClientSite 0x0000027A9CA7B460, pOleInPlaceSiteWindowless 0x0000000000000000, m_hWnd 0x0105069C

    The control can activate both windowed and windowless (unless m_bWindowOnly is set to true in which case windowed mode is forced). The trace shows that control is however in windowed mode, and that container does not have IOleInPlaceSiteWindowless, which is mandatory for windowless.