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

Question

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)}

Effusion

Just out of curiosity: Why did you post a new question with the same title instead of editing your first? – Insidious

@Filburt: I couldn't find my old one! – Effusion

Looks like this could do with a bounty ... not that I have ambitions but it would make it easier to find for you ;-) – Insidious

Answer 1

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

3. Add ATL Control class using wizard

3.1. Make sure to fill ProgID field

3.2. Add IPersistStreamInit on the Interfaces page

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

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

6. Insert it and have fun from there

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.

Supereminent

I was absolutely convinced that I'd tried these steps already, but evidently I must have changed something else along the way. This works perfectly—thank you so much! – Effusion

@eggyal: a really confusing thing is the *.exd files. Excel caches control information and might ignore some of your code changes and attempts to fix the situation. It could be the reason why your good code was still rejected by Excel at some point. – Supereminent

Actually I found out quite early on that Excel would cache type libraries in EXDs, and was aware that could cause problems in some cases—but I was finding EXDs weren't even being written out in my case. My digging through assembly has revealed that the presence of any non-dual interface in the type library causes the entire library to be rejected: so my attempts to try multiple different control configurations in a single project was being thwarted by a single failing case. This was undoubtedly a source of considerable confusion! – Effusion

Do you happen to know whether Excel supports windowless activation? I had assumed it would, but poking around now I can't get it to work... happy to post another q (with another bounty) if you want! ;-) – Effusion

I am not sure, but I suppose Excel implements whatever is necessary to host windowless controls. In particular in my code of this sample control if I set a break point in OnDraw I see that hosted control is visible and does its thing without window creation (m_hWnd == NULL). – Supereminent

Okay, thanks. It's curious that whilst m_hWnd == NULL, m_bWndLess == false... I'll keep prodding (this is my first foray into ATL, let alone COM!). – Effusion

I stepped inside a bit and here is what I see: during windowed/windowless negotation (atlctl.h - CComControlBase::InPlaceActivate) windowless mode is available when container implements IOleInPlaceSiteWindowless. Excel does not do it, and it changes my mind regarding the windowless support. OnDraw calls without activation cause paining of inactive control (such as static banner). Once you click it and it's activated, the control creates a window even though control itself does have support for windowless. That is, control is okay for windowless, Excel is not and does windowed only. – Supereminent

Hm. Are you sure? Seems to contradict stackoverflow.com/a/9617750 — and I can't seem to get a hWnd for my control, whether I click and activate it or not. – Effusion

Not necessarily a contradiction, the referred question is about UI elements, which are not ActiveX controls. These elements might be windowless, why not. – Supereminent

Ah, true enough. I thought that q was referring to MS Forms 2.0 ActiveX controls. In any event I still can't seem to get a handle for my control's window! Don't worry, I've troubled you quite enough. Thank you for all your help. – Effusion

It's up to Excel to decide whether it provides a host implementing windowless or not. They might use windowless host for specific controls (like their own, or those under "Form Controls" as opposed to "ActiveX Controls") and windowed for generic ActiveX (e.g. thinking of ending up in better compatibility and robust operation). From what I remember and see in code, ATL AX control would prefer windowless if container offers respective interfaces. We see here that it is not the case. Anyway, see my update in the body of the answer. – Supereminent

Answer 2

I have some experience with ActiveX and COM technology, but haven't digged up any deeper into Excel's world. To my best understanding all COM components always smells with overcomplexity unless you know exactly what you're doing, but it's not always possible to find what needs to be done.

Anyway - I've quickly scanned / googled - these are links I was able to find:

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

Excel 97 & 2000 Issues Excel 97 & 2000 Issues Q171280 Q171280, , Error Message "Cannot insert object" in Excel97.

ActiveX controls must support aggregation to be inserted ActiveX controls must support aggregation to be inserted into a spreadsheet. If they do not support being into a spreadsheet. If they do not support being aggregated, Excel will not allow them to be inserted. aggregated, Excel will not allow them to be inserted. More information on this is provided in More information on this is provided in Q143432 Q143432 at the at the bottom of the article. bottom of the article.

Any by trying lookup of article Q143432 quite often ended up with non existing page - but was able to find out this article:

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

and some problems related to it's usage is reported here:

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

My recommendation is however is to avoid ActiveX technologies if possible, if not possible - try links above - or may be find some similar open source code example.

Here are some examples which I've managed to find:

This one is in russian: https://habrahabr.ru/post/149277/

This is apparently somehow google translated version of same page: http://developers-club.com/posts/149277/

And it's source code - I think: https://github.com/Lovesan/MyActiveX

I hope that this will help you, however - not sure - haven't tried by myself.

Infrasonic