xlfregister c++ add-in/ range specification problem
LeonBendler
from a spreadsheet to be read into the dll(xll) function being invoked is due
to the range being specified as a SAFEARRAY, which is suitable for VB but not
as an xl_array(FP) for c++.
Below are what I believe to be the significant snippets of code showing the
current definition of the range as a SAFEARRAY which works with VB. I need
help to define this range such that it can be used with the dll(xll) facility
(xl_array(FP)?).
Thank you for any help you can provide.
Please understand I am an experienced programmer, but not with c++,
object-oriented, or Excel.
---------------------------------------------------------------
p:\FWC MSU \Source\MSUAward.cpp – CMSValue is the function being registered
with Excel, paymatrix is the problem array…
double _stdcall CMSUValue(double Sbeg, double Sval, double v, double r,
double q,
long divprotected, long tsrtarget, long window,
double T, LPSAFEARRAY FAR *paymatrixFP, long
Payout_Rows,
long Iterations, long Interpolation)
{
matrix paymatrix;
double value;
try {
paymatrix = SAFETOMATRIX(paymatrixFP, Payout_Rows, 2);
value = MSU(Sbeg, Sval, v, r, q, divprotected == 1, tsrtarget ==
1, window, T, paymatrix,
Iterations, Interpolation == 1);
}
catch(...) {
value = -99.0;
}
return value;
}
p:\FWC MSU \Source\MVMontecarlo.cpp – code where SAFETOMATRIX is defined?
#include "afxwin.h"
#include "jmatrix.hpp"
#include "xlcall.h"
#include "framewrk.h"
using namespace jmatrix;
matrix SAFETOMATRIX(LPSAFEARRAY FAR *fp, int rows, int cols)
{
matrix temp;
temp = matrix(rows, cols, (double *)(*fp)->pvData );
return temp;
}
---------------------------------------------------------------
p:\FWC MSU \Source\MVMontecarlo.h – code where SAFETOMATRIX is defined?
#pragma once
#include "jmatrix.hpp"
#include <windows.h>
using namespace jmatrix;
matrix RandMVNormal(const matrix &mean, const matrix &var);
matrix priceT(matrix& S1o, const matrix &S0, const matrix &mur, const matrix
&muo,
const matrix &corr, const matrix &v, double T, bool
antithetic=false);
matrix priceT(const matrix &S0, const matrix &mur,
const matrix &corr, const matrix &v, double T, bool
antithetic=false);
matrix SAFETOMATRIX(LPSAFEARRAY FAR *fp, int rows, int cols);
matrix UVPriceT(const matrix &S0, const matrix &mur, const matrix &v, double
T);
matrix UVPriceSeq(const matrix &S0, const matrix &mur, const matrix &v,
double dt, long N);
---------------------------------------------------------------
P:\FWC MSU\Source\Utilitiiies.cpp – code using paymatrix
double searchtable(const matrix &paymatrix, double ranking, bool interpolate)
// PURPOSE: Search a 2x2 table (paymatrix). The value in the second column
// of paymatrix that corresponds to ranking is returned (i.e., the
// first column in paymatrix is used to find a match). The table is
// assumed to be sorted in decreasing value by the first column of
paymatrix.
// If interpolate == false then the the value matched is the value
in the second
// column that corresponds to the
// value in the first column of paymatrix that is smaller than or
equal to
// ranking. If interpolate == true, linear interpolation is used.
{
int n, i, start;
bool bracket;
double pay;
n = nrow(paymatrix);
// cap the payoff at the highest level
if(ranking > paymatrix(1,1))
return paymatrix(1,2);
// if ranking is less than minimum level make it 0
if(ranking < paymatrix(n,1))
return double(0.0);
bracket = false;
for(i=1; i <= n && bracket ==false ; i++) {
if(ranking > paymatrix(i,1)) {
bracket = true;
start = i;
}
}
if(bracket == false)
pay = 0;
else {
if(start == 1)
pay = paymatrix(1, 2);
else {
if(interpolate)
pay = interpol(paymatrix(start,1),
paymatrix(start-1,1), ranking, paymatrix(start,2), paymatrix(start-1,2));
else
pay = paymatrix(start, 2);
}
}
return(pay);
}
---------------------------------------------------------------
lab27
On Apr 6, 12:01 am, LeonBendler
<LeonBend...@discussions.microsoft.com> wrote:
> My research indicates the problem I am having with specifying a range of data
> from a spreadsheet to be read into the dll(xll) function being invoked is due
> to the range being specified as a SAFEARRAY, which is suitable for VB but not
> as an xl_array(FP) for c++.
>
> Below are what I believe to be the significant snippets of code showing the
> current definition of the range as a SAFEARRAY which works with VB. I need
> help to define this range such that it can be used with the dll(xll) facility
> (xl_array(FP)?).
>
> Thank you for any help you can provide.
>
> Please understand I am an experienced programmer, but not with c++,
> object-oriented, or Excel.
> ---------------------------------------------------------------
> p:\FWC MSU \Source\MSUAward.cpp – CMSValue is the function being registered
> with Excel, paymatrix is the problem array…
>
> double _stdcall CMSUValue(double Sbeg, double Sval, double v, double r,
> double q,
> long divprotected, long tsrtarget, long window,
> double T, LPSAFEARRAY FAR *paymatrixFP, long
> Payout_Rows,
> long Iterations, long Interpolation)
> {
> matrix paymatrix;
> double value;
Like Ger said in his response to your previous question, all you need
to do is use a K type (FP by reference). SAFEARRAYs cannot be passed
over the native XLL C api.
eg register as:
{" sumdblrange", " BK", " sumdblrange", " rangeofdouble"},
extern "C" double __declspec(dllexport) sumdblrange(FP *pdata)
{
FP & data = *pdata;
double result = 0.0;
double * pinnerdata = data.array;
for (int y=0;y<data.rows;++y)
{
for (int x=0;x<data.columns;++x)
{
result += *(pinnerdata++);
}
}
return result;
}
to sum a double range.
Rgds,
Lee.
Ger Hobbelt
> Like Ger said in his response to your previous question, all you need
> to do is use a K type (FP by reference). SAFEARRAYs cannot be passed
> over the native XLL C api.
>
> eg register as:
> {" sumdblrange", " BK", " sumdblrange", " rangeofdouble"},
>
> extern "C" double __declspec(dllexport) sumdblrange(FP *pdata)
> {
Spot on, I'd say!
One additional note; may be superfluous, but better safe than sorry:
Excel feeds the XLL function an FP struct, which includes column and
row count members next to the data itself.
I get the feeling you want your calculation core function(s)
accessible by both VB and Excel: when I guessed correct you might be
best off with creating two wrapper functions for the same core
functionality: one which accepts a SAFEARRAY and is for VB, while the
other is for Excel and accepts an FP struct pointer. As both functions
can have different names, both a VB and Excel interface function can
reside in the same DLL (XLL) when such a need exists.
And here's a reference to a book which I have used a lot when I was
coding XLLs for Excel 2003 and 2007; it's the only book that I could
find back then which doesn't cover XLL C/C++ development as an
afterthought in one of the last chapters only. Instead, this one is
almost entirely dedicated to C/C++ XLL dev work. Don't get confused by
the word 'financial' in the title: almost all content is about Excel;
when you don't know what derivatives, etc. are, you'll still do fine
with this one:
title: Financial applications using Excel add-in development in C/C++
author: Steve Dalton
(I have the second edition (2007); don't know if a third ed. is
available today; anyway, second ed. also covers Excel 2007 (beta))
ISBN 978-0-0470-02797-4
Be aware that this one assumes some C/C++ knowledge. As you mention
you don't have much experience there, you might seek a few books on
that subject to help. Personally I'm in favour of the ANSI version of
the Kernighan & Ritchie book for C (MSVC still isn't C99 so that's my
excuse for keeping an old book around :-) ) and Stroustrup for C++ but
comments from colleagues and friends have led me to believe that those
are not everyone's cup of tea. Nevertheless I like my prog.lang. books
to have a grammar definition as on their appendixes.
The Dalton book is very clear and thorough and has been written with a
subtle sense of humor. For example, a chapter titled 'How to crash
Excel' points out a series of pitfalls that one can stumble into while
developing XLLs. It is not merely a list of things not to do, but it
also tells you why things go wrong and what to do instead.
Highly recommended. Particularly because it includes a lot of detail
regarding differences between VB(A) and C/C++ and Excel.
Take care,
Ger
Ger Hobbelt
On Apr 6, 1:01 am, LeonBendler <LeonBend...@discussions.microsoft.com>
wrote:
[...]
> double _stdcall CMSUValue(double Sbeg, double Sval, double v, double r,
> double q,
> long divprotected, long tsrtarget, long window,
> double T, LPSAFEARRAY FAR *paymatrixFP, long
> Payout_Rows,
> long Iterations, long Interpolation)
> {
[...]
> paymatrix = SAFETOMATRIX(paymatrixFP, Payout_Rows, 2);
> value = MSU(Sbeg, Sval, v, r, q, divprotected == 1, tsrtarget ==
[...]
> matrix SAFETOMATRIX(LPSAFEARRAY FAR *fp, int rows, int cols)
> {
> matrix temp;
>
> temp = matrix(rows, cols, (double *)(*fp)->pvData );
> return temp;}
Win32 documentation says SAFEARRAY is defined as:
typedef struct tagSAFEARRAYBOUND
\{
ULONG cElements;
LONG lLbound;
\} SAFEARRAYBOUND;
typedef struct tagSAFEARRAY
\{
USHORT cDims;
USHORT fFeatures;
ULONG cbElements;
ULONG cLocks;
PVOID pvData;
SAFEARRAYBOUND rgsabound[1];
\} SAFEARRAY;
while LPSAFEARRAY in Microsoft parlance means LPSAFEARRAY is a pointer
type for pointers to SAFEARRAYs:
typedef SAFEARRAY *LPSAFEARRAY;
The fact that you pass a pointer to a pointer to a SAFEARRAY as a
function argument either means you have a very particular usage in
mind or (more likely?) you didn't realize the LPSAFEARRAY is itself a
pointer type, so passing structures by reference doesn't really need
this in the prototypes
> double T, LPSAFEARRAY FAR *paymatrixFP, long
but instead this should suffice
> double T, LPSAFEARRAY paymatrixFP, long
Of course, your calling code and the function innards should be
corrected for such a change in prototype, e.g.
> matrix SAFETOMATRIX(LPSAFEARRAY FAR *fp, int rows, int cols)
> {
> matrix temp;
>
> temp = matrix(rows, cols, (double *)(*fp)->pvData );
> return temp;}
-->
> matrix SAFETOMATRIX(LPSAFEARRAY fp, int rows, int cols)
> {
> matrix temp;
>
> temp = matrix(rows, cols, (double *)fp->pvData );
> return temp;}
Also note that the SAFEARRAY, by design, has members which tell the
callee what the dimensions of the array really are. Hence passing
around a separate rows and cols count is 'suspect' and may be a side-
effect of a misunderstanding about SAFEARRAYs. The result can be
crashes or incorrect addressing of the data in the SAFEARRAY. (Same
argument would go for an FP struct: your code shows a line where '2
columns' is a hardcoded width, but a user can use the 'K'-typed Excel
XLL function while feeding it a wider (or thinner!) array of cells
from the spreadsheet!)
The line that started me to suspect:
> paymatrix = SAFETOMATRIX(paymatrixFP, Payout_Rows, 2);
because this would have been the 'logical way of doing it':
> paymatrix = SAFETOMATRIX(paymatrixFP);
+
> matrix SAFETOMATRIX(LPSAFEARRAY fp)
> {
> matrix temp;
/* This is NOT production code, and has not been tested nor
compiled */
/* Be aware that this assumes the SAFEARRAY is a two-dimensional
array of double's
which are encoded as a VT_DOUBLE (or what was it again; I
forgot. Too long since I've done VB-C interfacing :-( )
and the SAFEARRAY has flag FADF_HAVEVARTYPE set.
Coredumps and crashes galore when these assumptions are off the
mark, one way or another.
See also:
http://msdn.microsoft.com/en-us/library/ms221482(v=VS.90).aspx
*/
assert(((unsigned char *)fp->pvData)[-4] == VT_R8);
// ^^^ the [-4] is gleaned from the doc url above
assert(fp->cDims == 2);
// array encoded as [row][col] 2-dim item
size_t rows = fp->rgsabound[1].cElements;
// ^^^ be nasty, forget about .lLbound
size_t cols = fp->rgsabound[0].cElements;
// ^^^ notice [0] and [1] dim: right-to-left dimensioning
temp = matrix(rows, cols, (double *)fp->pvData);
return temp;
}
Note that this bit of code has more caveats than functionality:
- first, it assumes a very specific type of safearray being passed:
one which contains floating point numbers in C 'double' format, i.e.
VT_R8 'real' values. (VT_R4 would be C 'float's).
Anything else will make this thing go b0rk b0rk b0rk.
For VT_ codes, see for example:
http://www.marin.clara.net/COM/variant_type_definitions.htm
- second, it assumes such a 'matrix' of floating point values is
indeed stored as a 2-dimensional array. And we didn't even bother with
those 'lower bound' index values per dimension so sparse arrays won't
be seen as such.
- third, we did a bit of direct safearray access which /might/ just
work given plenty joss but MS provides a set of safearray access
functions to shield the developer from at least /part/ of the trouble
lurking under the grass.
http://msdn.microsoft.com/en-us/library/ms221145(v=VS.90).aspx
Not exactly your 'home free card', but it might add a wee bit of
robustness to the code.
APIs that would be of interest:
SafeArrayGetDim()
SafeArrayGetLBound()
SafeArrayGetUBound()
and then
SafeArrayGetVartype()
which may or may not cough up that desirable VT_R8, but when it
doesn't, you might need to 'coerce' the type to VT_R8 using some
VARIANT APIs, I guess.
then there's
SafeArrayGetElement()
and
SafeArrayGetElemsize()
alternatively, there's
SafeArrayPtrOfIndex()
or for those who know what they're doing
SafeArrayAccessData()
all are described here, some with a few (rough) code examples:
http://msdn.microsoft.com/en-us/library/ms221145(v=VS.90).aspx
which brings me to the last bit of comment I'm going to write
today ;-) :
given your 'matrix' constructor
temp = matrix(rows, cols, (double *)fp->pvData);
and the SAFEARRAY versus FP struct from Excel, /plus/ assuming you'd
like to have both (for VB access and Excel access to the same C/C++
written functionality using two wrapper functions, one for VB and one
for Excel as mentioned at the top of this epistle, then:
you will need to change your 'matrix' type instantiation. Either by
filling it an element at a time (slow: much call overhead) or by
creating two constructors: one which understands a SAFEARRAY (and thus
accepts a LPSAFEARRAY argument) and another one which knows all about
Excel FP structure and hence accepts a FP*.
class matrix
{
public:
matrix(LPSAFEARRAY *src); // constructor for VB source type
matrix(FP *src); // Excel 'K' type, i.e. FP* support.
... // other constructors, e.g. ones which accept Excel XLOPERs?
.... // methods, data
};
Oh, and you pass a 'matrix' as a return type in your functions; as
matrix is a class (which is somewhat akin to a 'struct' in C but with
extras) that's okay, in a way, but there will happen a large amount of
copying matrix instances as a simple statement like
matrix m = SAFETOMATRIX(x,y,z);
will have invoked the matrix copy constructor at least once (if not
twice, thanks to SAFETOMATRIX internals).
It's faster to pass around references (or pointers) to classes and
structs, but you'll need to mind about 'lifetime' a.k.a. 'variable
scope' a bit more then. This is very wrong:
matrix &AltSafeArrToMatrix(...)
{
matrix m(... /* init matrix 'm' */ );
return m;
}
// 'm' is as dead as a doornail as soon as you get beyond the return
statement. Bad Form.
Equally disastrous is this:
matrix *AltSafeArrToMatrix(...)
{
matrix m(... /* init matrix 'm' */ );
return &m;
}
// same thing: 'm' is as dead as a doornail as soon as you get beyond
the return statement so any pointer to such 'out of scope' var is dead
meat as well. Bad Form.
while this should work:
matrix *AltSafeArrToMatrix(...)
{
matrix *m = new matrix(... /* init matrix 'm' */ );
return m;
}
// 'm' is a pointer to a class instance 'm' which is allocated in the
heap.
// must later be deleted using
// delete m;
// so you get the added 'bonus' of heap allocation agony.
// Several (partial) solutions have been created for that, including
'smart pointers' and
// a few types of 'garbage collecting heap managers' for C++.
// Sharks swim in those waters, though.
Then there's the fourth option, which simply instantiates a matrix at
caller level on stack, so no heap hassle, and a reference to the
instance to set up is passed to other functions. The constructor will
be a rather empty shell for these as the real setup happens in other
member function called, for example, init().
Which has the added benefit that exceptions thrown in such a 'init'
function can be handled very nicely by the callers while exceptions
thrown in a constructor always are a bit of a headache:
void AltSafeArrToMatrix(matrix &dst, ...)
{
dst.init(... /* init matrix 'm' */ );
}
where some caller has this:
{
matrix m;
....
AltSafeArrToMatrix(m, ......);
....
}
// from here. 'm' is automatically gone 'out of scope' and hence gone
from the system.
while the matrix class now has something like this:
class matrix
{
public:
matrix() { ... /* bit of basic stuff, nothing much */ }
/* initializers, one for VB-type source data, another for Excel
data. Thank you, polymorphism! */
void init(LPSAFEARRAY vb_src);
void init(FP *excel_K_src);
....
....
};
Hope that helps,
Take care,
Ger
lab27
On 8 Apr, 15:29, Ger Hobbelt <g...@hobbelt.com> wrote:
> Couple of generic notes; few to do with Excel:
>
> On Apr 6, 1:01 am, LeonBendler <LeonBend...@discussions.microsoft.com>
> wrote:
> [...]
>
> Win32 documentation says SAFEARRAY is defined as:
>
> typedef struct tagSAFEARRAYBOUND
> \{
> ULONG cElements;
> LONG lLbound;
> \} SAFEARRAYBOUND;
>
> typedef struct tagSAFEARRAY
> \{
> USHORT cDims;
> USHORT fFeatures;
> ULONG cbElements;
> ULONG cLocks;
> PVOID pvData;
> SAFEARRAYBOUND rgsabound[1];
> \} SAFEARRAY;
>
On the (very slight, but worth mentioning ;) ) offchance you're
tempted to take advantage of existing code by constructing a synthetic
SAFEARRAY with the contents of the FP and passing that around - the
above definitions are NOT complete. For example, if fFeatures
contains FADF_HAVEVARTYPE, then the OLE safearray functions will
assume that the vartype is present at offset -4. (yep, minus.)
Lee.
LeonBendler
in my call for help. I will share your observations with someone who has the
requisite experience to attack this problem, and you will have hopefully made
their job an easy one.
Thank you again,
Leon