Download (direct link):
The command increment_counter () increments the value held in a named worksheet range in the active workbook, Counter, and then sets up the next call to itself using
10 The author has also experienced Excel behaving in an unusual or unexpected way when using this function to set up a command to be run every n seconds, say. For this reason, this book recommends using the C API function where robustness is proving hard to achieve.
Excel Add-in Development in C/C++
the xlcOnTime command. Note that both commands need to be registered with Excel using the xlfRegister command, and that increment_counter needs to be registered with the 4th argument as "IncrementCounter" in order for Excel to be able to call the command properly.
#define SECS_PER_DAY (60.0 * 60.0 * 24.0)
bool on_time_example_running = false;
int stdcall increment_counter(void)
if(!on_time_example_running) return 0;
++Counter; // Does nothing if Counter not defined
// Schedule the next call to this command in 10 seconds' time cpp_xloper Now;
Excel4(xlfNow, &Now, 0);
cpp_xloper ExecTime((double)Now + 10.0 / SECS_PER_DAY); cpp_xloper CmdName("IncrementCounter"); cpp_xloper RetVal;
int xl4 = Excel4(xlcOnTime, &RetVal, 2, &ExecTime, &CmdName); return 1;
int stdcall on_time_example_cmd(void)
// Toggle the module-scope Boolean flag and, if now true, start the // first of the repeated calls to increment_counter()
if(on_time_example_running = !on_time_example_running) increment_counter();
cpp_xloper BarNum(10); // the worksheet menu bar cpp_xloper Menu("&XLL Example"); cpp_xloper Cmd("OnT&ime example"); cpp_xloper Status(on_time_example_running);
Excel4(xlfCheckCommand, 0, 4, &BarNum, &Menu, &Cmd, &Status); return 1;
Note: When Excel executes the timed command it will clear the cut or copy mode state if set. It can be very frustrating for a user if they only have a few seconds to complete a cut and paste within the spreadsheet. Making the enabling/disabling of such repeated calls easily accessible is therefore critically important. This means adding a menu item or toolbar button, or at the very least, a keyboard short-cut, with which to run the equivalent of the on_t ime_example_cmd () command above.
9.9.2 Starting and stopping threads from within a DLL
Setting up threads to perform tasks in the background is straightforward. The following example code contains a few module-scope variables used to store a handle for the background thread and for communication between the thread and a function that would be called by Excel. The function thread_exampl e () when called with a non-zero argument
Miscellaneous Topics 319
from an Excel spreadsheet for the first time, starts up a thread that executes the function thread_main (). This example function simply increments a counter with a frequency of the argument in milliseconds. The function thread_example () when called subsequently with a non-zero argument returns the incremented counter value. If called with a zero argument, thread_example () terminates the thread and deletes the thread object.
bool keep_thread_running = false; long thread_counter;
HANDLE thread_handle = 0;
// Thread is defined using a pointer to this function. Thread // executes this function and terminates automatically when this // functions returns. The void * pointer is interpreted as a pointer // to long containing the number of milliseconds the thread should // sleep in each loop in this example.
DWORD WINAPI thread_main(void *vp)
// Do whatever work the thread needs to do here: thread_counter++;
Sleep(*(long *)vp); else
Sleep(100); // Make life easy for the OS
This function thread_example () either kills the background thread, sets up or gets the value of thread_counter, depending on the value of activate_ms and the
current state of the thread. It is declared as stdcall so that it can be accessed as a
long stdcall thread_example(long activate_ms)
// Address of thread_param is passed to OS, so needs to persist static long thread_param;
// Not used, but pointer to this needs to be passed to CreateThread() DWORD thread_ID;
if(thread_handle == 0)
thread_counter = 0; keep_thread_running = true; thread_param = activate_ms;
thread_handle = CreateThread(NULL, 0, thread_main,
(void *)& thread_param, 0, &thread_ID);
Excel Add-in Development in C/C++
// Set flag to tell thread to exit keep_thread_running = false;
// Wait for the thread to terminate.
&& code == STILL_ACTIVE; Sleep(10));
// Delete the thread object by releasing the handle CloseHandle(thread_handle); thread_handle = 0;