getting a cell's comment in XLL

[xll_student]

I am new to XLL development. I am studying Steve Dalton's book,
Financial Applications Using Excel Add-in Development in C/C++ (2nd edition).
I am trying to use Steve's function from page 298 in my code. The XLL gets
created but when I call the function from Excel 2007 it gives me #VALUE!
error.

Could somebody take a look and let me know what's wrong. The code is pasted
below. The argument string I used is "RJJ". I call it the function Excel
using the following syntax: =Z_GetNote(2,2).

Thank you.

//=============
__declspec(dllexport) XLOPER * _stdcall Z_GetNote(long p_row, long p_col)
{
static XLOPER ret_xloper;
XLOPER arg;

//create a single-cell reference to cell on the current sheet
arg.xltype = xltypeSRef;
arg.val.sref.count = 1;

//values:
arg.val.sref.ref.rwFirst = arg.val.sref.ref.rwLast = (RW) p_row;
arg.val.sref.ref.colFirst = arg.val.sref.ref.colLast = (COL) p_col;

Excel4(xlfGetNote, &ret_xloper, 1, &arg);

//Free up memory allocated for the return value:
ret_xloper.xltype |= xlbitXLFree;
return &ret_xloper;
}
//=============

[lab27]

Replied to this earlier but looks like it's been dropped so
reposting... probably end up with both posts, oh well...

Post by xll_student
I am new to XLL development. I am studying Steve Dalton's book,
Financial Applications Using Excel Add-in Development in C/C++ (2nd edition).
I am trying to use Steve's function from page 298 in my code. The XLL gets
created  but when I call the function from Excel 2007 it gives me #VALUE!
error.

Have a look at http://msdn.microsoft.com/en-us/library/bb687835.aspx -
specifically the bit about Different types of functions, and what they
can call.

Post by xll_student
Could somebody take a look and let me know what's wrong. The code is pasted
below. The argument string I used is "RJJ". I call it the function Excel
using the following syntax: =Z_GetNote(2,2).

The function below will only work in a macro mode context - you're
calling it in function mode. (class 1).

If it DID work, then how would excel track that the (2,2) you use as
an argument refer to B1? If you update the comment in B1, how would
the calculation engine know, on the next recalculation, to recalc
=Z_GetNote(2,2)? It wouldn't, so you'd end up with a sheet state
which was incorrect.

You can indicate to excel that an XLL function is allowed to make use
of macro mode calls by postfixing the prototype with '#'. In this
case, excel won't use dependency information to call your function, so
inputs which are passed in via the calc tree are NOT guaranteed to be
correct.

As a general rule of thumb (these apply to VBA too):

* in a "function mode" call (i.e. from a cell) don't make use of any
information which has not been directly passed in to you as an
argument.[*]
* further to the above, never use global state in a function mode call
- the end result of this is a spreadsheet which will not work properly
if called in the order the calculation engine decides, but requires a
button and a macro to calculate!!
* restating pt2 a little, never make a function mode function which
can change its outputs depending on the order its PRECEDENTS were
called.

if I have A1, A2, B1, B2, where a2 = f(a1), b1 = f(a1), and b2 =
f(a2,b2), this can be legitimately calculated as a1,a2,b1,b2, or
a1,b1,a2,b2. The calculation engine /could/ legitimately choose
either path. The result cannot differ, unless you want some very
confused users. Respect the calculation tree!

Rgds,

Lee.

[*] xlfCaller is ok, but really only for logging/debug purposes.

Post by xll_student
Thank you.
//=============
__declspec(dllexport) XLOPER * _stdcall Z_GetNote(long p_row, long p_col)
{
        static XLOPER ret_xloper;
        XLOPER arg;
        //create a single-cell reference to cell on the current sheet
        arg.xltype = xltypeSRef;
        arg.val.sref.count = 1;
        arg.val.sref.ref.rwFirst = arg.val.sref.ref.rwLast = (RW) p_row;
        arg.val.sref.ref.colFirst = arg.val.sref.ref.colLast = (COL) p_col;
        Excel4(xlfGetNote, &ret_xloper, 1, &arg);
        ret_xloper.xltype |= xlbitXLFree;
        return &ret_xloper;}
//=============

[lab27]

And the errata ;)

Post by lab27
If it DID work, then how would excel track that the (2,2) you use as
an argument refer to B1?  If you update the comment in B1, how would
the calculation engine know, on the next recalculation, to recalc
=Z_GetNote(2,2)?  It wouldn't, so you'd end up with a sheet state
which was incorrect.

err, b2.

Post by lab27
if I have A1, A2, B1, B2, where a2 = f(a1), b1 = f(a1), and b2 =
f(a2,b2), this can be legitimately calculated as a1,a2,b1,b2, or
a1,b1,a2,b2.  The calculation engine /could/ legitimately choose
either path.  The result cannot differ, unless you want some very
confused users.  Respect the calculation tree!

b2 = f(a2,b1). Doh! :)

[xll_student]

Thank you very much. I have got it up and running now.

Post by lab27
And the errata ;)

Post by lab27
If it DID work, then how would excel track that the (2,2) you use as
an argument refer to B1? If you update the comment in B1, how would
the calculation engine know, on the next recalculation, to recalc
=Z_GetNote(2,2)? It wouldn't, so you'd end up with a sheet state
which was incorrect.

err, b2.

Post by lab27
if I have A1, A2, B1, B2, where a2 = f(a1), b1 = f(a1), and b2 =
f(a2,b2), this can be legitimately calculated as a1,a2,b1,b2, or
a1,b1,a2,b2. The calculation engine /could/ legitimately choose
either path. The result cannot differ, unless you want some very
confused users. Respect the calculation tree!

b2 = f(a2,b1). Doh! :)
.