Download (direct link):
(c) is chosen here. Whilst not having all the capabilities of (d), it still touches on all the important issues. It also requires that the code is flexible enough to handle many different functions taking different numbers and types of arguments and returning different values, both intermediate and final. This satisfies requirements (3) and (4) above.
9.10.2 Communication between Excel and a background thread
There are a number of reasons why the foreground thread (Excel, essentially) and the background thread need to communicate with each other. Firstly, there is contention for resources, typically both threads trying to access the same block of memory at the same time. This is addressed with the use of Critical Sections. Secondly, the worksheet functions need to tell the background thread about a new task, or a change to an outstanding task. Getting the worksheet to communicate with the background thread is simple, requiring only that memory contention is handled well. Two flags are used in the example class below that enable the user, via a custom command, to request that the background thread
1. stops processing the current task.
2. stops processing all tasks.
Lastly, the background thread needs to be able to tell Excel that new information is available to the worksheet, in response to which Excel needs to recalculate those functions so that this new information can be acquired. Getting the background thread to tell Excel that something needs to happen requires that Excel polls to see if something needs to be done, say, every n seconds. (Remember that background threads cannot safely call directly into Excel via the C API or COM.) This is achieved here with the use of xlcOnTime embedded in a command associated with the background thread. This command is referred to below as the polling command. (See also section 9.9.1 Setting up timed calls to DLL commands: xlcOnTime on page 316.)
9.10.3 The software components needed
The list of components required is as follows:
Table 9.7 Software components for a background thread strategy
TaskList class • Creates, deletes, suspends and resumes the background thread and the polling command (in foreground) • Handles memory contention between threads using critical sections • Creates and deletes DLL-internal Excel names associated with each caller of a long task function (in foreground). Names are mapped 1-1 to tasks.
Miscellaneous Topics 323
Table 9.7 (continued)
• Maintains a list of tasks and manages the following: o Addition of new tasks (in foreground) o Modification of existing tasks (in foreground) o Deletion of orphaned tasks (in foreground) o Execution of a task, and the associated state changes (in background) • Provides an interface for information about current tasks and access to configuration parameters
Polling command • Associated with a given instance of a TaskList class • Registered with Excel so that it can be executed via the xlcOnTime command • Deletes any invalid names in the list • Initiates Excel recalculation • After recalculation initiates cleaning up of orphaned tasks • Schedules the next call to itself
Control/configuration command(s) • Accessible to the user via custom menu or toolbar • Provides enable/disable thread function • Provides some task execution information • Provides ability to configure thread settings
Long task interface function • Registered with Excel as a volatile macro sheet function • Takes oper arguments, not xlopers11 • Returns immediately if called from the Function Wizard • Responsible for verification of inputs • Returns immediately if inputs invalid or task list thread is deactivated
Long task main function • Takes a pointer to a task object/structure and returns a Boolean • Makes no calls, directly or indirectly, to Excel via the C API or COM • Periodically checks the break task flag within the task object/structure while performing its task
One reason for registering a long task interface function as a macro sheet function is to give it the ability to read and return the current value of the calling cell. This may be the required behaviour if the task has not been completed.
9.10.4 Imposing restrictions on the worksheet function
One potential complication is the possibility that a user might enter a number of long task function calls into a single cell. For example, a user might enter the following formula
11 This is a simplifying restriction that ensures that tasks are driven by values not ranges, and simplifies the handling of different functions that take different numbers of arguments of different types.
Excel Add-in Development in C/C++
into a cell:
Excel’s recalculation logic would attempt to recalculate both calls to the function LONG_TASK(). (In this example the user should enter =L0NG_TASK(IF(A1,B1,B2)) instead.) In any case, it is not too burdensome to restrict the user to only entering a single long task in a single cell, say. Should you wish to do so, such rules are easily implemented using xlfGetFormula described in section 8.9.7 on page 221. This is one of the things that should be taken care of in the long task interface function. The fact that you might need to do this is one of the reasons for registering it as a macro sheet function.