Download (direct link):
char *p = R.GetName(); cpp_xloper RetVal(p); free(p);
The following section provides other examples of the use of this class as well as listings of some of the code.
9.8 KEEPING TRACK OF THE CALLING CELL OF A DLL
Consider a worksheet function, call it CreateOne, which creates a data structure that is unique to the cell from which the function is called. There are a number of things that have to be considered:
• What happens if the user moves the calling cell and Excel recalculates the function? How will the function know that the thing originally created is still to be associated with the cell in its new position, instead of creating a new one for the new cell location?
• What happens if the user clears the formula from the cell? What happens if the user deletes the cell with a column or row deletion or by pasting another cell over it? What happens if the worksheet is deleted or the workbook closed? How will the DLL know how to clean up the resources that the thing was using?
If these questions cannot be addressed properly in your DLL, then you will spring memory leaks (at the very least). The same questions arise where a function is sending some request to a remote process or placing a task on a background thread. The answer to these issues all revolve around an ability to keep track of the calling cell that created the internal object, or remote request, or background task. In general, this needs to be done when:
• The DLL is maintaining large data structures in the DLL (see above section).
• A background thread is used to perform lengthy computations. The DLL needs to know how to return the result to the right cell when next called, bearing in mind the cell may have been moved in the meantime.
• The cell is being used as a means of contributing data, that is only allowed to have one source of updates, to a remote application.
• The cell is being used to create a request for data from a remote application.
Finding out which cell called a worksheet function is done using the C API function xlfCaller. However, given that the user can move/delete/overwrite a cell, the cell reference itself cannot be relied upon to be constant from one call to the next. The solution is to name the calling cell, that is, define a name whose definition is the range reference of the calling cell. For a worksheet function to name the calling cell, the name can only be an
Excel Add-in Development in C/C++
internal DLL name created using xlfSetName. (Worksheet names can only be created from commands.) The xlfSetName function is used to define a hidden DLL name. As with regular worksheet names, Excel takes care of altering the definition of the name whenever the corresponding cell is moved. Also, the DLL can very straightforwardly check that the definition is still valid (for example, that the cell has not been deleted in a row or column delete) and that it still contains the function for which the name was originally created.
The class discussed in section 9.7 A C++ Excel name class example, xlName, on page 307, contains a member function that initialises a class instance to the internal name that corresponds to the calling cell, if it exists, or names it otherwise. Many of the code examples that follow use this class which is provided in the example project on the CD ROM. The sections that immediately follow use the class’ member function code to demonstrate the handling of internal names, etc.
9.8.1 Generating a unique name
Generating a valid and unique name for a cell is not too complex and various methods can be devised that will do this. Here’s an example:
1. Get the current time as an integer in the form of seconds from some base time.
2. Increment a counter for the number of names created within this second.
3. Create a name that incorporates text representations these two numbers.9 (This could be a simple 0-9 representation or something more compact if storage space and string comparison speed are concerns.)
The following code shows an example of just such a method:
static long name_count = 0;
static unsigned long T_last = 0;
tm tm_T = *localtime(&time_t_T);
// Need an unsigned long to contain max possible value unsigned long T = tm_T.tm_sec + 60 * (tm_T.tm_min + 60 * (tm_T.tm_hour +24 * (tm_T.tm_yday + 366 * tm_T.tm_year % 100)));
if(T != T_last)
T_last = T; name_count = 0;
char buffer; // More than enough space
9 The name created must conform to the rules described in section 8.10 Working with Excel names on page 239.
Miscellaneous Topics 311
// Increment name_count so that names created in the current // second are still unique. The name_count forms the first // part of the name.
int ch_count = sprintf(buffer, "x%ld.", ++name_count);
// Represent the time number in base 62 using 0-9, A-Z, a-z. // Puts the characters most likely to differ at the front // of the name to optimise name searches and comparisons for(;T; T / = 62)