Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Excel add in development in C++ Aplications in finance - Dalton S.

Dalton S. Excel add in development in C++ Aplications in finance - Wiley publishing , 2005. - 425 p.
ISBN 0-470-02469-0
Download (direct link): exceladdindevelopmentincand2005.pdf
Previous << 1 .. 123 124 125 126 127 128 < 129 > 130 131 132 133 134 135 .. 168 >> Next

m_Worksheet = false; // This will be an internal name
//-----------------------------------------------------------------
// Get a reference to the calling cell
//-----------------------------------------------------------------
cpp_xloper Caller;
int xl4 = Excel4(xlfCaller, &Caller, 0); Caller.SetExceltoFree();
if(xl4) // if xlfCaller failed
return m_Defined = m_RefValid = false;
//-----------------------------------------------------------------
// Associate the new internal name with the calling cell(s)
//-----------------------------------------------------------------
cpp_xloper RetVal;
xl4 = Excel4(xlfSetName, &RetVal, 2, &m_RangeName, &Caller); RetVal.SetExceltoFree();
if(xl4 || RetVal.IsType(xltypeErr))
return m_Defined = m_RefValid = false;
//-----------------------------------------------------------------
// Add the new internal name to the list
//-----------------------------------------------------------------
m_Defined = m_RefValid = true; add_name_record(NULL, *this); return true;
}
The function add_name_record () adds this new internal name to a list that enables management of all such names. (See next section for details.) A simple example of how you would use xlName’s ability to do this is the following worksheet function name_me () that assigns an internal name to the calling cell (unless it already has one) and returns the name. (This function has no obvious use other than demonstration.)
xloper * stdcall name_me(int create)
{
if(called_from_paste_fn_dlg()) return p_xlErrValue;
// Set the xlName to refer to the calling cell. xlName Caller;
bool name_exists = Caller.SetToCallersName();
if(create)
{
if(!name_exists)
Caller.NameCaller(NULL);
// Get the defined name. Need to free this string. char *name = Caller.GetName(); cpp_xloper Name(name); free(name);
return Name.ExtractXloper();
}
Miscellaneous Topics 315
// Not creating, so deleting if(iname_exists)
return p_xlFalse;
// Delete from Excel's own list of defined names Caller.Delete();
// Delete from DLL's list of internal names. This is a // slightly inefficient method, especially if a large // number of internal names are in the list. A more // specific method of deleting from list could easily // be coded.
clean_xll_name_list(); return p_xlTrue;
}
9.8.4 Internal XLL name housekeeping
The reference associated with an internal XLL name can, for a number of reasons, become invalid or no longer refer to an open workbook. The user may have deleted a row or column containing the original caller, or cut and pasted another cell on top of it. The sheet it was on could have been deleted, or the workbook could have been deleted without ever being saved.
In general Excel is very good at changing the reference when cells are moved, the range expands or contracts, the sheet is renamed or moved, the workbook is saved under a different name, etc. This is one of the main reasons for defining an internal name within the XLL, of course, as the events through which a user can do these things are not easily trapped. Being able to clean up unused or invalid internal names, and associated resources, is clearly very important.
The C API function xlfNames returns an array of worksheet names, but not, unfortunately, internal DLL names. Therefore, it is necessary for the DLL to maintain some kind of container for the internal names it has created, through which it can iterate to perform this housekeeping. For C++ programmers, the most sensible way to do this is using a Standard Template Library (STL) container. (The source file XllNames.cpp in the example project on the CD ROM contains an implementation of an STL map that is used by the xlName class for this purpose.)
The following two steps can be employed to identify whether an internal name is valid and associated reference with a valid:
• Attempt to get the definition reference for the name using the xlfGetName function. If this fails, the name is not valid or has not yet been defined.
• Attempt to convert the reference definition returned by xlfGetName (as text in the form [Book1.xls]Sheet1!R1C1) to a reference using the xlfTextref function. If this fails the reference is not valid.
The following code lists the xlName member function Refresh() that updates the current cell address of a named range and confirms that the name and the reference are (still) valid. This function is called whenever the class needs to be sure that the name still exists and the cell reference is up-to-date.
316
Excel Add-in Development in C/C++
bool xlName::Refresh(void)
{
m_RangeRef.Free();
//-----------------------------------------------------------
// First check that the name is defined
//-----------------------------------------------------------
cpp_xloper Defn;
int xl4 = Excel4(xlfGetName, &Defn, 1, &m_RangeName); Defn.SetExceltoFree();
if(xl4 || iDefn.IsType(xltypeStr))
return m_Defined = m_RefValid = false;
m_Defined = true;
//----------------------------------------------------------------
Previous << 1 .. 123 124 125 126 127 128 < 129 > 130 131 132 133 134 135 .. 168 >> Next