How to add Excel LinkedCell support to your ATL ActiveX control

An ActiveX control in Microsoft Excel may have a LinkedCell property that allows the control to interact with data in the workbook. This article describes how to build a sample ActiveX control that exposes this functionality.

The LinkedCell property is actually implemented by Excel and not by your control. Excel checks to see if your control provides a bindable property named Value and, if it exists, provides the property to the user. The following steps demonstrate how to build and use a control with this feature.

Steps to build the sample

1. In Visual C++ 6.0, create a new ATL COM AppWizard project called AtlLinkedCell. Select DLL as the Server Type, and then click Finish.
2. On the Insert menu, select New ATL Object.
3. On the Controls category, select the Full Control, and then click Next.
4. Type in LinkedCellCtl as the Short Name. Select the Attributes tab, select Supports Connection Points, and then click OK.
   
   Note Adding support for connection points adds IPropertyNotifySink support to the project.
   
   
5. In ClassView, right-click ILinkedCellCtl, and then choose Add Property.
6. Set the Property Type to VARIANT, set the Property Name to Value, and then click Attributes.
7. In the Edit Attributes dialog box, select the defaultbind attribute in the list, and then click OK to dismiss the dialog box. Click OK in the Add Property to Interface dialog box to save the new property.
8. Open LinkedCellCtl.cpp and replace the get_Value and put_Value implementations with the following code:

   STDMETHODIMP CLinkedCellCtl::get_Value(VARIANT *pVal)
   {
       if(pVal) {
           pVal->vt = VT_I4;
           pVal->lVal = m_Value.lVal;
       }
       return S_OK;
   }
   
   STDMETHODIMP CLinkedCellCtl::put_Value(VARIANT newVal)
   {
       VariantChangeType(&m_Value, &newVal, 0, VT_I4);
       
       // Tell container this property changed. This calls
       // IPropertyNotifySink::OnChanged()
       FireOnChanged(1);
       // You are now dirty and should be saved...
       SetDirty(TRUE);
       // Notify container our data has changed...
       SendOnDataChange();
       // Notify container our view has changed...
       FireViewChange();
   
       return S_OK;
   }
   					

9. Open LinkedCellCtl.h, add the m_Value variable to your class, and the appropriate code to initialize it as follows:

   public:
       VARIANT m_Value;
       CLinkedCellCtl()
       {
           m_Value.vt = VT_I4;
           m_Value.lVal = 123456789;
       }
   					

10. Replace the OnDraw function in LinkedCellCtl.h with the following code:

        HRESULT OnDraw(ATL_DRAWINFO& di)
        {
            RECT& rc = *(RECT*)di.prcBounds;
            Rectangle(di.hdcDraw, rc.left, rc.top, rc.right, rc.bottom);
            SetTextAlign(di.hdcDraw, TA_CENTER|TA_BASELINE);
    
            char buf[80];
            wsprintf(buf, "Val=%d (0x%x)", m_Value.lVal, m_Value.lVal);
            TextOut(di.hdcDraw, (rc.left+rc.right)/2, (rc.top+rc.bottom)/2,
                buf, lstrlen(buf));
    
            return S_OK;
        }
    
        // Support Saving & Loading of your property...
        STDMETHOD(Save)(LPSTREAM pStm, BOOL fClearDirty)
        {
            if(pStm) {
                DWORD dwWrite;
                pStm->Write(&m_Value.lVal, sizeof(long), &dwWrite);
                pStm->Write(&m_sizeExtent.cx, sizeof(long), &dwWrite);
                pStm->Write(&m_sizeExtent.cy, sizeof(long), &dwWrite);
                SetDirty(FALSE);
            }
            return S_OK;
        }
    
        STDMETHOD(Load)(LPSTREAM pStm) 
        {
            if(pStm) {
                DWORD dwRead;
                pStm->Read(&m_Value.lVal, sizeof(long), &dwRead);
                pStm->Read(&m_sizeExtent.cx, sizeof(long), &dwRead);
                pStm->Read(&m_sizeExtent.cy, sizeof(long), &dwRead);
            }
            return S_OK;
        }
    					

11. Build the project.

Steps to test the sample

Microsoft Office Excel 2007

1. Start Office Excel 2007.
2. Click the Developer tab. If the Developer tab does not appear, follow these steps:
   1. Click the Microsoft Office Button, and then click Excel Options.
   2. Click Popular.
   3. Click to select the Show Developer Tab in the Ribbon check box.
   4. Click OK.
3. In the Controls group, click Insert.
4. Under ActiveX Controls, click More Controls.
5. In the More Controls dialog box, click LinkedCellCtl Class. Then, draw the control on the worksheet.
6. Right-click the control, and then click Properties.
7. In the LinkedCell property, type B2, and then press ENTER.
8. Change the contents of cell B2 on the worksheet to various numeric values. Notice that each time that you change cell B2 , the control updates automatically.
9. Save the workbook, and then close it. Open the saved workbook, and note that the control provides persistence support so that dimensions and property values of the control are saved along with the workbook.

Microsoft Office Excel 2003 and earlier versions of Excel

1. Start Microsoft Excel.
2. On the View menu, select Toolbars, and then choose Control Toolbox.
3. On the Control Toolbox, click More Controls, select LinkedCellCtl class in the list, and then draw the control on the worksheet.
4. Right-click the control and choose Properties.
5. Type B2 in the LinkedCell property, and then press the Enter key.
6. Change the contents of cell B2 on the worksheet to various numeric values, and note that each time you change cell B2, the control updates automatically.
7. Save the workbook and close it. Open the saved workbook, and note that the control provides persistence support so that dimensions and property values of the control are saved along with the workbook.