Steps to create an asynchronous add-in

This topic describes each of the steps involved in creating an add-in with asynchronous functions. It does not contain a complete project (see the sample projects for complete, working code), but you will find it useful to check your progress as you create an asynchronous add-in.

Here is a summary of the steps:

Create a new XLL+ add-in project
Add the XllPush header files
Change the parent class of your application class, from CXllApp to CXllPushApp
Create a new class descended from CXllMtMsg
Add a data cache to your application class
Implement OnXllOpenEx()
Implement OnXllClose()
Implement ProcessAsyncMessage()
Write your add-in functions
Create a GUI workbook

These steps are described in more detail below. You can also find further explanations in the source code of the sample projects.

Create a new XLL+ add-in project

Using the XLL+ AppWizard, create a new XLL+ add-in project, using either the STL libraries or MFC, depending on your requirements. For the purpose of this summary, we will assume it that the new project is named MyProject.

Add the XllPush header files

In DevStudio, add the following line to your header file, MyProject.h.

#include "MtBackground_rc.h"   // main symbols  
#include <xllplus.h>           // See below      
#include <XllPushApp.h>

Note: You don't need to worry about adding the library file, XllPush.lib, to the project; there are library inclusion records in XllPush.h which handle this for you.

Change the parent class of your application class, from CXllApp to CXllPushApp

The easiest way to do this is to use Find/Replace to change all instances of CXllApp to CXllPushApp in the two main source files, e.g. MyProject.cpp and MyProject.h.

You should also declare the virtual functions OnXllOpen(), OnXllClose() and ProcessAsyncMessage(). The class declaration should now resemble the following:

class CMyProjectApp : public CXllPushApp {

    ...

// Overrides
    virtual BOOL OnXllOpenEx();                          
    virtual void OnXllClose();                           
    virtual void ProcessAsyncMessage(CXllMtMsg* msg);    
    // ClassWizard generated virtual function overrides
    //{{AFX_VIRTUAL(CMtBackgroundApp)
    public:
    virtual int ExitInstance();
    virtual BOOL InitInstance();
    //}}AFX_VIRTUAL

    ...

};

Create a new class descended from CXllMtMsg

Any data you pass from background threads to the main thread should be packaged up in a class descended from CXllMtMsg.

For example, if you are passing a string stock code and a numeric price, your message class might look like this:

class CMyMsg : public CXllMtMsg {
public:
    CMyMsg(LPCSTR pszTopic, double dValue)
        : m_strTopic(pszTopic), m_dValue(dValue) {}
    virtual ~CMyMsg() {}
// Data
    CString m_strTopic;
    double m_dValue;
};

Add a data cache to your application class

It is essential that all data used to respond to add-in functions be kept in a cache. You cannot be sure when the add-in function will be called, or how often.

The cache simply maps topics to data. In our simple example, the data is just a number.

#include <map>
class MtMyCache {
public:
    void Set(LPCSTR pszTopic, double dValue) {
        m_map[pszTopic] = dValue;
    }
    BOOL Lookup(LPCSTR pszTopic, double& value) const {
        map_type::const_iterator itF = m_map.find(pszTopic);
        if (itF == m_map.end()) 
            return FALSE;
        value = itF->second;
        return TRUE;
    }
protected:
    typedef std::map<CString, double> map_type;
    map_type m_map;
};

Implement OnXllOpenEx()

Implement the virtual function CMyProjectApp::OnXllOpenEx(). This event-handler is called once in the lifetime of the add-in, when it is first opened. All significant and failure-prone initialization takes place here.

The implementation should first call CXllPushApp::OnXllOpenEx() and return FALSE if it fails.

Then you should initialize any application-level objects and services. For example, you might open a database connection, initialize a data feed client, or start one or more background threads.

This is also the place where your you should also register your asynchronous functions by name, by calling CXllPushApp::AddFunction().

If any critical initialization fails, then your function should show an error message and return FALSE.

The following example illustrates the responsibilities of OnXllOpenEx().

BOOL CMyProjectApp::OnXllOpenEx() {
    // Call parent class
    if (!CXllPushApp::OnXllOpenEx())
        return FALSE;

    // Create an asynchronous worker thread
    m_thread = CreateThread(NULL, 0, WorkerThreadFn, this, 0, &m_threadid);
    if (m_thread == NULL) {
        MessageBox(NULL, "Failed to create worker thread", 
                   "Initialisation failed", MB_OK|MB_ICONEXCLAMATION);
        return FALSE;
    }

    // Register functions which will be pushed
    AddFunction("GetPrice");
    AddFunction("GetStats");

    return TRUE;
}

Implement OnXllClose()

Implement the virtual function CMyProjectApp::OnXllClose().This function is called once in the lifetime of the add-in, when it is finally released. All significant termination takes place here.

In this function you should reclaim any resources allocated in OnXllOpenEx(), e.g. close a database connection, terminate a data feed client, or kill background threads.

The following example illustrates the responsibilities of OnXllClose().

void CMyProjectApp::OnXllClose() {
    // Kill the worker thread
    if (m_thread != NULL) {
        ::CloseHandle(m_thread);
        m_thread = NULL;
    }
}

Implement ProcessAsyncMessage()

Implement the virtual function CMyProjectApp::ProcessAsyncMessage(). This event handler will be called in the main thread whenever a message arrives from a background thread.

Your implementation should carry out the following steps.

Extract any data contained in the message and store it in the data cache.
Call CXllPushApp::UpdateCells() to cause all affected cells to be recalculated.
Delete the message.

The following example illustrates the responsibilities of ProcessAsyncMessage().

void CMyProjectApp::ProcessAsyncMessage(CXllMtMsg* msg) {
    // Downcast the message
    CMyMsg* myMsg = static_cast<CMyMsg*>(msg);

    // Update the cache
    m_cache.Set(myMsg->m_strTopic, myMsg->m_dValue);

    // Update any affected cells
    UpdateCells(myMsg->m_strTopic);

    // Delete the message
    delete msg;
}

Write your add-in functions

Write each asynchronous function, making sure that you register all connections between cells and topics, using CXllPushApp::AddConnection(). The general shape of such functions is as follows:

Note the connection between topic and cells, by calling CXllPushApp::AddConnection(topic).
Check the data cache. If the result is already in the cache, return it to Excel.
If the result is not already in the data cache, send a request to the background thread to retrieve/calculate it, and return an error, e.g. "#WAIT!".

The following example illustrates a typical asynchronous add-in function.

extern "C" __declspec( dllexport )
LPXLOPER GetPrice(const char* StockCode)
{
    CXlOper xloResult;

    // Get a downcast pointer to the application object
    CMyProjectApp* addin = (CMyProjectApp*)XllGetApp();

    // Note the connection between this topic and the calling cell
    addin->AddConnection(StockCode);

    // Check the cache
    double dValue;
    if (addin->m_cache.Lookup(StockCode, dValue)) 
    {
        // Return the cached value
        xloResult = dValue;
    }

    // If the data is not in the cache
    else
    {
        // Request data from server
        addin->m_datafeed.Advise(StockCode);

        // Return an error as place-holder
        xloResult = "#WAIT!";
    }
    return xloResult.Ret();
}

Create a GUI workbook

In the add-in source directory, make a copy of one of the provided GUI workbooks (MtCalcGui.xla, MtFeedAddinGui.xla or MtBackgroundGui.xla) and rename it e.g. MyProjectGui.xla.

Open MyProjectGui.xla and switch to the VBA editor (Alt+F11) to make the following changes.

In modCommands, change the definitions of XLA_MENU, XLL_NAME, XLL_PATH and SUPPORT_MSG as required.
Also in modCommands, make changes to SetupMenu() as required, to add, remove or alter the commands that will appear on the add-in's menu bar.
Optionally, edit the UserForm ufSettings to suit the parameters of your particular add-in.
Save and close MyProjectGui.xla.

Next: Debugging asynchronous add-ins >>