What requirements are necessary and sufficient for using an ActiveX control directly on an Excel worksheet?

Question

Microsoft Office Support Article " Add or Register an ActiveX Control ":

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

However, I cannot find documented requirements that are necessary and sufficient for the direct use of the control on the sheet.

I created a new C ++ / ATL project, to which I added an ATL control that accepts default values. After compiling, creating and registering the control, it appears in the Excel list of "Additional controls" (access to them is in the section "Developer"> "Insert"> "ActiveX Controls"> "Additional Controls"), but when you try to paste on the worksheet it sees "Unable to insert object."

What changes should I make to fix this?

OR

Where are the Excel requirements for ActiveX controls documented?

---

For what it's worth, I checked that the control created by the wizard works fine otherwise (tested with the ActiveX Control Test Container , which I built from the Visual C ++ 2008 sample pack ).

In addition, I know that the documentation for the Appearance tab of the ATL Management Wizard describes the Plugin option as follows:

Select this option to have your control appear in the Insert Object dialog box for applications such as Word and Excel. You can then insert your control with any application that supports embedded objects through this dialog box.

However, this checkbox (which simply adds the "Insertable" subkey to the registry) causes the control to appear in the "Insert"> "Text"> "Object" dialog box - to avoid doubt, I tried with this as well as without it, the same error is created anyway.

I am currently comparing traces of Excel execution paths when trying to insert my control into this while trying to insert a working (Forms 2.0) control. The main difference, apparently, is VBE7.dll when loading the type library (which the OLE / COM Object Viewer can load correctly from my dll -yet after Excel has completed all the same reading from it, it is interrupted before write EXD) ... I'm digging some kind of assembly now in the vain hope that I will find out, but for sure, the one who built the working control for Excel and knows what I'm missing can save 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)}

Answer 1

To implement an ActiveX ATL control that can be inserted into an MS Excel worksheet, follow these steps:

• Make sure that C: \ Users \ $ (UserName) \ AppData \ Local \ Temp \ Excel8.0 does not have cached ActiveX * .exd control data, which may be an unobvious obstacle

• Create an ATL DLL Project with All Defaults

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

3. Add ATL Control class using wizard

3.1. Be sure to fill in the ProgID field

3.2. Add the IPersistStreamInit page on the Interfaces page

4. Create a DLL and register (regsvr32)

5. In Excel, a new control appears in the Developer, ..., More Controls menu

6. Insert it and have fun from there

Source Code: Subversion / Trac

UPDATE : question from comments below:

... does Excel support windowless activation?

To see the control operation in action, add the code 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 allows you to print controls that help identify the window / window mode. Output (in the end, after activating the object or from the very beginning):

 ... 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 window and window windows (unless m_bWindowOnly is set to true, in this case the window mode is forced). Tracing shows that the control, however, is performed in windowed mode, and this container does not have IOleInPlaceSiteWindowless , which is mandatory for a IOleInPlaceSiteWindowless window.

Answer 2

I have some experience with ActiveX and COM technologies, but I did not go deep into the world of Excel. For a better understanding of all components, COM always smells like over complexity if you don’t know exactly what you are doing, but you can’t always find what needs to be done.

Anyway - I quickly scanned / googled - these are the links I could find:

From here: http://itdocument.com/6551512544/

Excel 97 and 2000 Excel 97 and 2000 problems Q171280 Q171280, "Unable to insert object" error message in Excel97.

ActiveX controls must support the aggregation that must be inserted. ActiveX controls must support the aggregation that must be inserted into the table. If they do not support getting into the table. If they do not support aggregated, Excel will not allow them to be inserted. aggregated, Excel will not allow them to be inserted. For more information about this, see the additional information about this in Q143432 Q143432 on the bottom of the article page. bottom of the article.

Anyone trying to find article Q143432 quite often ended up with a non-existent page, but was able to find out this article:

https://support.microsoft.com/en-us/kb/143432

and some problems associated with this use are reported here:

http://www.verycomputer.com/418_e81ed24b6ac0cb79_1.htm

However, my recommendation is to avoid the use of ActiveX technology if it is possible, if it is not possible - try the links above - or you can find a similar open source example.

Here are some examples that I managed to find:

This publication in Russian: https://habrahabr.ru/post/149277/

This is apparently a somehow translated version of Google on the same page: http://developers-club.com/posts/149277/

And this is the source code - I think: https://github.com/Lovesan/MyActiveX

I hope this helps you, however - not sure - have not tried it myself.