XLL+ Class Library

MtBackground.cpp

Initialisation: OnXllOpenEx()

OnXllOpenEx() is called once in the lifetime of the add-in, when it is opened. It must call the parent class method first, before any other initialisation. It should then initialize any application resources. If any of these initializations fail, it should show an error message and return FALSE.

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

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

    // Set push properties
    SetFormatChangedCells(TRUE);

    // Register functions which will be pushed
    AddFunction("MtbGet");
    AddFunction("MtbGetStats");

    return TRUE;
}

Examine each of the sections of code in the function.

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

Calling the parent class is essential, and must precede any other initialization.

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

Here we initialize the application's resources; in this case, by starting the background thread described above. Note that if initialization fails, an error message is shown and the function returns FALSE.

    // Set push properties
    SetFormatChangedCells(TRUE);

Here we instruct the push engine to change the format of cells which are updated by asynchronous functions. The default formatting sets the text color to blue if a number goes up, and red if it goes down. The virtual function OnCellUpdate() can be overridden to change this behaviour.

    // Register functions which will be pushed
    AddFunction("MtbGet");
    AddFunction("MtbGetStats");

Next, we instruct the engine to treat the two named functions as asynchronous. This step is also essential. The engine does a lot of work in the background to ensure that cells containing asynchronous functions are updated as they should be, even if the user has moved them within or between spreadsheets, and uses the names of the async functions in order to keep track of such cells.

    return TRUE;

Finally, the function returns TRUE to indicate successful completion.

Termination: OnXllClose()

OnXllClose() is called once in the lifetime of the add-in, just before it is closed. It should terminate any application resources.

void CMtBackgroundApp::OnXllClose() 
{
    // Stop the worker thread
    if (m_thread) {
        ::CloseHandle(m_thread);
        m_thread = 0;
    }
}

In this case, it simply kills the worker thread.

ProcessAsyncMessage

CMtBackgroundApp::ProcessAsyncMessage() is called every time a message is received from the background thread. (The message will have been posted using CMtBackgroundApp::PostMessage().)

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

    // If value has changed ...
    double dOldValue;
    if (!m_cache.Lookup(myMsg->m_strTopic, dOldValue)
     || dOldValue != myMsg->m_dValue)
    {
        // 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;
}

This implementation has the usual four steps:

  1. Downcast the message to our message class.
  2. Put the new data in the data cache.
  3. Update any cells connected to the topic.
  4. Delete the message.

In our example, we have optimised the process, by skipping steps 2 and 3 for messages which are duplicates of previous messages.

Asynchronous add-in function

Next, examine the add-in function itself, MtbGet().

This is a typical async add-in function, with the usual steps:

  1. Get a pointer the application object.
  2. Look for the answer in the data cache. Set the return value in xloResult if it is found, or set an error message if not found.
  3. Register the connection between the topic and the calling cell, by calling AddConnection().
// Function:    MtbGet
// Returns:     LPXLOPER
// Description: Get data and link to asynchronous updates

//{{XLP_SRC(MtbGet)
    // NOTE - the FunctionWizard will add and remove mapping code here.
    //    DO NOT EDIT what you see in these blocks of generated code!
IMPLEMENT_XLLFN2(MtbGet, "RC", "MtbGet", "Topic", "Multi-threaded"
    " Demo 1", "Get data and link to asynchronous updates", 
    "Key to data", "\0", 1)

extern "C" __declspec( dllexport )
LPXLOPER MtbGet(const char* pszTopic)
{
    CXlOper xloResult;
//}}XLP_SRC

    // Get a pointer to the application
    CMtBackgroundApp* addin = (CMtBackgroundApp*)XllGetApp();

    // Look up the value in the cache, or return #N/A
    // if not found
    double dValue;
    if (addin->m_cache.Lookup(pszTopic, dValue))
        xloResult = dValue;
    else
        xloResult = xlerrNA;

    // Register the connection between the topic and 
    // the cell(s) that called the function
    addin->AddConnection(pszTopic);

    // Return the result
    return xloResult.Ret();
}

It is essential to call AddConnection(), even if the value is already in the cache, in order to instruct the push engine to keep monitoring the connection.

(This is always the pattern of add-in functions, except for one-hit functions, where the result is found asynchronously, but is never updated thereafter; this pattern is demonstrated in the MtCalc sample.)

A second async function is provided, MtbGetStats(), which returns a two-column array of cells. This demonstrates how async functions can handle array formulae as well as single cell formulae.

Macro functions

Two macro add-in functions are included, which will be called by the GUI. These are the accessor functions for the background thread's TickCount property. They will let the user change the period of the background thread from a dialog.

extern "C" __declspec( dllexport )
BOOL MtBackgroundSetPeriod(long MilliSecs)
{
    if (MilliSecs <= 0)
        return FALSE;
    CMtBackgroundApp* addin = (CMtBackgroundApp*)XllGetApp();
    addin->SetTickPeriod(MilliSecs);
    return TRUE;
}

extern "C" __declspec( dllexport )
long MtBackgroundGetPeriod()
{
    CMtBackgroundApp* addin = (CMtBackgroundApp*)XllGetApp();
    return addin->GetTickPeriod();
}

Next: MtBackgroundGui.xla >>