Download (direct link):
The above code makes assumptions that may not be thread-safe. In particular the system could be simultaneously reading (in thread_example ()) and writing (in thread_main ()) to the variable thread_counter. In practice, in a Win32 environment, the reading and writing of a 32-bit integer will not be split from one slice of execution to another on a single processor machine. Nevertheless, to be really safe, all instructions that read from or write to memory that can be accessed by multiple threads should be contained within Critical Sections.
Creating a thread from a worksheet function creates the possibility of leaving a thread running when it is no longer needed, simply by closing the worksheet that contained the formula that created it. A better solution is to create and destroy threads from, say, the xlAutoOpen() and xlAutoClose() XLL interface functions or some other user command. Section 9.10 A background task management class and strategy on page 320 and the associated code on the CD ROM, present a more robust and sophisticated example of managing and using background threads.
9.9.3 Calling the C API from a DLL-created thread
This is not permitted. Excel is not expecting such calls which will fail in a way which might destabilise or crash Excel. This is, of course, unfortunate. It would be nice to be able to access the C API in this way, say, to initiate a recalculation from a background thread when a background task has been completed. One way around this particular limitation is to have the background thread set a flag that a timed command can periodically check, triggering a recalculation, say, if the flag is set. (See section 9.9.1 Setting up timed calls to DLL commands: xlcOnTime on page 316.)
9.10 A BACKGROUND TASK MANAGEMENT CLASS AND STRATEGY
This section brings together a number of topics, discussed so far. It describes a strategy for managing a background thread, using the C API, that can be used for lengthy worksheet
Miscellaneous Topics 321
function recalculations. For brevity, worksheet functions that require this approach are referred to in this section as long tasks. The reason for wanting to assign long tasks to their own thread is so that the user is not locked-out of Excel while these cells recalculate. On a single-processor machine the total recalculation time will, in general, be worse, albeit imperceptibly, but the difference in usability will be enormous.
To make this work, the key sections that are relied on are:
• Registration custom commands and of volatile macro-sheet equivalent worksheet functions (section 8.5, page 182).
• The use of a repeated timed command call (section 9.9.1, page 316).
• Managing a background thread (section 9.9.2, page 318).
• Working with internal Excel names (section 8.10, page 239).
• Keeping track of the calling cell (section 9.8, page 309).
• Creating custom menu items (section 8.11, page 249).
• Creating a custom dialog box (section 8.13, page 273).
This section discusses the requirements, the design and the function of the various software components needed to make the strategy work.
Both the strategy and the class around which it is centred, are intended simply to illustrate the issues involved. They are not intended to represent the only or best way of achieving this goal. Whatever you do, you should satisfy yourself that your chosen approach is suitable and stable for your particular needs. More sophisticated solutions are certainly possible than that proposed here, but are beyond this book’s scope.
The high level requirements that drive this example strategy are these:
1. The user must be able to disable/re-enable the background thread from a command.
2. Long task worksheet functions should not, ideally, impose restrictions on the user that ordinary worksheet functions are not limited by.
3. Long task worksheet functions must be given the ability to return intermediate values.
4. A number of different long task functions should be supportable without extra coding other than of the function itself.
5. Changing input values for an in-progress task should cause the existing (old) task to be abandoned as soon as possible and the task to be re-queued with the new parameters.
6. There should be no hard limit to the number of worksheet functions that can be queued.
Other requirements could be envisaged, such as the prioritisation of certain tasks, but for simplicity the above requirements are all that are considered here.
When farming out tasks to threads there are a number of possible approaches:
(a) Create a thread for each task.
(b) Create a thread for each worksheet function.
(c) Create a single thread on which you execute all tasks for all functions.
(d) Create a pool of threads that have tasks assigned according to their availability.
Excel Add-in Development in C/C++
Strategy (a) could very quickly lead to the thread management overhead bringing your machine to a grinding halt, especially where each worksheet cell might get its own thread. Strategy (b) improves on this considerably unless there are, say, dozens of functions. Strategy (d) is perhaps the best approach, but for simplicity of the example strategy