Download (direct link):
// Now check if the definition is a valid reference
char *temp = (char *)Defn; // allocates some memory Defn = temp +1; // remove the leading '=' free(temp); // free the temporary memory
xl4 = Excel4(xlfTextref, &m_RangeRef, 2, &Defn, p_xlFalse); m_RangeRef.SetExceltoFree();
m_RefValid = ixl4 && m_RangeRef.IsType(xltypeSRef | xltypeRef); return m_RefValid;
As well as having a way of detecting whether a name is valid, it is necessary to have a strategy for when and/or how often the DLL checks the list of internally defined names. This depends largely on the application. There needs to be a balance between the overhead associated with frequent checking and the benefit of knowing that the list is good.
In some cases you may not be concerned if the list contains old and invalid names. In this case a clean-up function that is invoked (1) as a command, or (2) when a new name is being added or explicitly deleted, would do fine.
In other cases, for example, where you are using a function to contribute some piece of real-time data, it may be imperative that the application informs the recipient within a set time that the source cell has been deleted. In this case, it might be sufficient to set up a trap for a recalculation event using the xlcOnRecalc function that calls such a function. Or it may be necessary to create an automatically repeating command (see sections 9.9.1 and 9.10 for examples of this).
Finally, it is probably a good idea, depending on your application, to delete all the internal names when your XLL is unloaded: calling a function that iterates through the list to do this from xlAutoClose is the most convenient and reliable way. The function delete_all_xll_names () in the example project on the CD ROM does just this.
9.9 MULTI-TASKING, MULTI-THREADING AND ASYNCHRONOUS CALLS IN DLLS
9.9.1 Setting up timed calls to DLL commands: xlcOnTime
There are two readily accessible ways to execute a command at a given point in the future. One is to use VBA Application.OnTime method. The other is to use the C API
Miscellaneous Topics 317
command xlcOnTime whose enumeration value is 32916 (0x8094). (It is also possible to set up a Windows timed callback from a DLL command or a function. However, a function called back in this way cannot safely use the C API or the COM interface.)
The most accessible of the two is VBA’s Application.OnTime which sets up a scheduled call to a user-defined command. The method takes an absolute time argument, but in conjunction with the VB Now function, can be used to set up a relative-time call. Once the specified time is reached, VB uses COM to call the command function. This call will fail if Excel is not in a state where a command can be run.10
The C API function is analogous to the VBA method, and both are analogous to the XLM ON.TIME command which takes 4 parameters.
1. The time as a serial number at which the command is to be executed. If the integer (day) part is omitted, the command is run the next time that time occurs, which may be the next day.
2. The name of the command function, as set in the 4th argument to the xlfRegister function.
3. (Optional.) Another time, up until which you would like Excel to wait try executing the command again if it was unable the first time round. If omitted Excel will wait as long as it takes: until the state of Excel is such that it can run the command.
4. (Optional.) A Boolean value that if set to false will cancel a timed call that has not yet been executed.
One key difference between the C API and VBA versions is the third parameter, which tells Excel to execute a command as soon as it can after the specified time. (Excel cannot execute commands when, for example, a user is editing a cell.) Using xlcOnTime, it is Excel itself that calls the command directly. This avoids any subtle problems that VBA might encounter calling the command via COM. A further advantage is that Excel will not make more than one call to the DLL at a time. In other words, the DLL command will not be called at the same time as another command or a worksheet function. This makes the safe management of shared data in the DLL much easier.
The xlcOnTime call returns true if the call was scheduled successfully, otherwise false. (If an attempt was made to cancel a timed callback that did not exist or was already executed, it returns a #VALUE! error.)
Below is some example code showing two inter-dependant commands, on_time_example_cmd () and increment_counter (). Both examples rely heavily on the cpp_xloper class (see section 6.4 A C++ class wrapper for the xloper -cpp_xloper on page 118) and the xlName class (see section 9.7 A C++ Excel name class example, xlName on page 307).
The command on_time_example_cmd () toggles (enables/disables) repeated timed calls to increment_counter (). The command also toggles a check mark on a menu item associated with the OnTimeExample command in order to inform the user whether the timed calls are running or not.