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 .. 44 45 46 47 48 49 < 50 > 51 52 53 54 55 56 .. 168 >> Next

WORD TOWS;
WORD columns;
double array[1]; // Start of array[rows * columns]
}
xl_array;
In some texts this structure is called FP or _FP, but since the name is private to the DLL (and the structure is not defined in the SDK header file) it is up to you. The above name is more descriptive, and this is how the rest of the book refers to this structure.
Warning: Excel expects this structure to be packed such that array [1] is eight bytes after the start of the structure. This is consistent with the default packing of Visual Studio (6.0 and .NET), so there’s no need to include #pragma pack() statements around its definition. You need to be careful when allocating memory, however, that you allocate 8 bytes plus the space for array[rows * columns]. Allocating 4 bytes plus the space for the array will lead to a block that is too small by 4 bytes. A too-small block will be overwritten when the last array element is assigned, leading to heap damage and destabilisation of Excel. (See the code for xl_array_example1() below).
Note: The array stores its elements row-by-row so should be read and written to accordingly. The element (r,c), where r and c count from zero, can be accessed by the expression array[r*rows + c]. The expression array[r][c] will produce a compiler error. A more efficient way of accessing the elements of such an array is to maintain a list of pointers to the beginning of each row and then access the elements by offsetting each start-of-row pointer. (Numerical Recipes in C, Chapter 1, contains very clear examples of this kind of thing.)
Later sections provide details of two (closely related) data structures, both capable of passing mixed-type arrays, the oper and the xloper. The xl_array structure has some advantages and some disadvantages relative to these.
Advantages:
• Memory management is easy, especially when returning an array via an argument modified in place. (See notes below.)
• Accessing the data is simple.
Disadvantages:
• xl_arrays can only contain numbers.
• If an input range contains something that Excel cannot convert to a number, Excel will not call the function, and will fail with a #VALUE! error. Excel will interpret empty cells as zero, and convert text that can be easily converted to a number. Excel will not convert Boolean or error values.
• Returning arrays via this type (other than via arguments modified in place) presents difficulties with the freeing of dynamically allocated memory. (See notes below.)
• This data type cannot be used for optional arguments. If an argument of this type is missing, Excel will not call the function, and will fail with a #VALUE! error.
108
Excel Add-in Development in C/C++
Note: It is possible to declare and register a DLL function so that it returns an array of this type as an argument modified-in-place. The size of the array cannot be increased, however. The shape of the array can be changed as long as the overall size is not increased - see xl_array_example3() below. The size can also be reduced - see xl_array_example4() below. Returning values in this way will not alter the value of the cells in the input range. The returned values will be deposited in the calling cells as if the array had been returned via a return statement. (See section 8.5 Registering and un-registering DLL (XLL) functions on page 182 for details of how to tell Excel that your DLL function uses this data structure.)
Note: Lreeing dynamic memory allocated by the DLL is a big problem when returning arrays using this type. You can declare a static pointer, initialise it to NULL and check it every time the function is called - see xl_array_example1() below. If it is not null, you can free the memory allocated during the last call before re-executing and reallocating. This ensures that the DLL doesn’t suffer from leakage, but it does suffer from retention. This might only be a problem for very large arrays. It is a problem that is solved with the use of xlopers. (See section 6.2.3 below and also Chapter 7 Memory Management on page 161 for more details.)
Examples
The following examples provide code for four exportable functions, one of which creates and returns an array of this type, the others returning an array via a passed-in array argument. Note the differences in memory management.
The first allocates memory for an array of the specified size, and assigns some simple values to it, and returns a pointer to it to Excel.
xl_array * stdcall xl_array_example1(int rows, int columns)
{
static xl_array *p_array = NULL;
if(p_array) // free memory allocated on last call {
free(p_array); p_array = NULL;
}
int size = rows * columns;
if(size <= 0) return NULL;
size_t mem_size = sizeof(xl_array) + (size-1) * sizeof(double);
if((p_array = (xl_array *)malloc(mem_size)))
{
p_array->rows = rows; p_array->columns = columns;
for(int i = 0; i < size; i++)
p_array->array[i] = i / 100.0;
}
return p_array;
}
Passing Data between Excel and the DLL
Previous << 1 .. 44 45 46 47 48 49 < 50 > 51 52 53 54 55 56 .. 168 >> Next