CalcEvt Sample

Overview

This sample add-in shows how to trap the OnCalculate event, which occurs just after a sheet has finished calculating.

It also demonstrates the use of CXlOper to manipulate cell references.

Features

The sample displays the following features:

The add-in function PutFile(FileName, Destination) returns the timestamp of the named file. It also puts the contents of the file, word by word, into the cells referred to by the Destination argument.
Because a worksheet function cannot change the values of cells directly, PutFile notes the file name and the destination range for later processing.
When the OnCalculate event occurs, each of the files noted for later processing is inspected. If the content of the file differs from the contents of the destination range, then the cells are populated. If the cells are up to date, they are left unchanged.
The sample also contains a pair of functions for manipulating file paths, GetDir(path) and MakePath(path1, path2), which are used to calculate the path of a file in the same directory as the example workbook.

Notes

The processing at the end of the function CTextFileTask::PopulateDestination is very important.

    // Get the current contents of the destination range
    xloOldValue.Coerce(m_xloDest);

    // Compare the current value with the contents of the file
    // and update the destination range ONLY if they are different.
    if (xloOldValue != xloValue)
        m_xloDest.SetValue(xloValue);

We look at the current contents of the destination range, and compare them to the values we intend to put there. If they are identical then we do not update the range.

Every time we update a cell, we can cause the sheet to be calculated. Since this method is called during OnCalculate, we might go into an endless loop, which is to be avoided.

By making sure that we only update the cells when necessary, we only cause a finite number of cascading calculations.

Functions used

CXllApp::SetComEventHandler

CXllApp::GetSinkEvents

CXllApp::SetSinkEvents

CXlOper::ChangeType

CXlOper::GetCaller

CXlOper::IsRef

CXlOper::GetSheetName

Project files

If you are using MS Developer Studio 6, then you should open the project file CalcEvt.dsp.

If you are using MS Visual Studio .NET 2002, then you should open the solution file CalcEvt.sln or the project file CalcEvt.vcproj.

If you are using MS Visual Studio .NET 2003, then you should open the solution file CalcEvt71.sln or the project file CalcEvt71.vcproj.