HOW TO: How do I get or set a comment on an Excel cell?

HOW TO: How do I get or set a comment on an Excel cell?

Question

I have tried to use CXlMacros::Note(...) to set a note on a cell, but it fails. How can I attach a note to a cell, and how can I get the text of any existing note?

Answer

The NOTE() and GET.NOTE() XLM macros are not supported in recent versions of Excel. You should instead use Excel's COM API to manipulate the Comment object. The code below demonstrates how to use it.

Note that the methods below will always fail if you have switched off the add-ins's access to the COM API (by setting CXllApp::m_bUseAutomation to FALSE).

Note also that these methods can only be used within a macro function, not from within a worksheet function.

#include <xlpcom.h>
#include <xlpcomhelpers.h>

BOOL GetRange(const CXlOper& xloRef, CDispatch& disp)
{
    CXllCom* c = XllGetTypedApp()->GetCom();
    if (c == NULL)
        return FALSE;
    CString strBook, strSheet;
    CString strBookSheet = xloRef.GetSheetName();
    CXllApp::SplitSheetName(strBookSheet, strBook, strSheet);
    HRESULT hr = c->OleGetRange(strBook, strSheet, xloRef.GetRef(), &disp);
    return (SUCCEEDED(hr));
}

BOOL GetComment(const CXlOper& xloRef, CString& text)
{
    CDispatch dispRange;
    if (!GetRange(xloRef, dispRange))
        return FALSE;

    CDispatch dispComment;
    CVariant res;
    HRESULT hr = ::OleGetProperty(dispRange, L"Comment", &dispComment, 0, 0);
    if (!SUCCEEDED(hr))
        return FALSE;

    hr = ::OleMethod(dispComment, L"Text", &res, 0, 0);
    if (!SUCCEEDED(hr))
        return FALSE;
    text = CString(V_BSTR(&res));
    return TRUE;
}

BOOL SetComment(const CXlOper& xloRef, const char* text)
{
    CDispatch dispRange;
    if (!GetRange(xloRef, dispRange))
        return FALSE;
    
    CDispatch dispComment;
    CVariant res;
    HRESULT hr = ::OleGetProperty(dispRange, L"Comment", &dispComment, 0, 0);
    if (SUCCEEDED(hr))
    {
        // Already has a comment - overwrite it
        hr = ::OleMethod(dispComment, L"Text", &res, 0, 1, &CVariant(text));
    }
    else
    {
        // Use AddComment
        hr = ::OleMethod(dispRange, L"AddComment", &res, 0, 1, &CVariant(text));
    }
    return SUCCEEDED(hr);
}

// Function:    MyAddinFunction
// Purpose:     No description provided

//{{XLP_SRC(MyAddinFunction)
    // NOTE - the FunctionWizard will add and remove mapping code here.
    //    DO NOT EDIT what you see in these blocks of generated code!
IMPLEMENT_XLLFN2(MyAddinFunction, "R#", "MyAddinFunction", "", 
    "User Defined", "No description provided", "", "appscope=1\0"
    , 2)

extern "C" __declspec( dllexport )
LPXLOPER MyAddinFunction()
{
    XLL_FIX_STATE;
    CXlOper xloResult;
//}}XLP_SRC

    CXlOper xloRef;
    LPCSTR comment = "Cheese";
    if (xloRef.GetActiveCell())
    {
        CString oldComment;
        if (GetComment(xloRef, oldComment))
            CXllApp::XlMessageBox("Comment is: " + oldComment, XlMessageBoxTypeInformation);
        if (SetComment(xloRef, comment))
            CXllApp::XlMessageBox("Comment was set", XlMessageBoxTypeInformation);
    }
    return xloResult.Ret();
}