xlfregister c++/Excel add-in array registration problem
lonbendler
C++/Excel add-in implementation with no prior knowledge of either. I have
managed to test the add-in successfully using the VB interface. I have
(almost) successfully registered the add-in using the xlfregister function.
Using the Insert Function/Function Arguments box, I have been able to specify
a source cell/value for every needed variable except for the paymatrixFP
array. As soon as I type in the name of the range, I get the message box
“Microsoft Office Excel has encountered a problem and needs to close.” It
provides no real information regarding the actual error encountered.
Having muddled my way through this far, I feel really frustated to be this
close to completion with no idea how to attack this particular problem.
I appreciate any suggestions as to how to proceed.
I post below the c++ registration code (~70 lines) and for comparison the VB
code(~40 lines).
Thank you,
Lon Bendler
******************c ++ registration code
// Registration routine
BOOL __stdcall xlAutoOpen(void) {
AFX_MANAGE_STATE(AfxGetStaticModuleState( ));
// Get XLL file name
XLOPER xDll;
int iOutput = Excel4(xlGetName,&xDll,0);
XLOPER xInt;
xInt.xltype = xltypeInt;
xInt.val.w = 1;
XLOPER xName;
xName.xltype = xltypeStr;
xName.val.str = (LPSTR)"♂CMSUValue";
/* ---------------------------------------------------------------------------
string lengths for each of the LPSTR's below are entered in the zeroeth
position of the string in OCTAL by using the right Alt key and the numeric
keypad (no leading zeroes)
------------------------------------------------------------------------------
*/
XLOPER xTypes;
xTypes.xltype = xltypeStr;
// xTypes.val.str = (LPSTR)"►BBBBBBJJJBKJJJ"; //error unpredictable
// xTypes.val.str = (LPSTR)"►BBBBBBJJJBRJJJ"; //error unpredictable
// xTypes.val.str = (LPSTR)"►EEEEEENNNEREEE"; //error on load
// xTypes.val.str = (LPSTR)"►EEEEEENNNEPEEE"; //error on load
// xTypes.val.str = (LPSTR)"►EEEEEENNNEQEEE"; //error on load
// xTypes.val.str = (LPSTR)"►EEEEEENNNEKEEE"; //error on 1st ref to
paymatrix
xTypes.val.str = (LPSTR)"►EEEEEENNNEKEEE"; //error on 1st ref to
paymatrix
XLOPER xArgs;
xArgs.xltype = xltypeStr;
xArgs.val.str = (LPSTR)"ÜSbeg, Sval, v, r, q, divprotected, tsrtarget,
window, T, paymatrixFP, Payout_Rows, Iterations, Interpolation";
XLOPER xCat;
xCat.xltype = xltypeStr;
xCat.val.str = (LPSTR)"♠MSUcat";
XLOPER xNull;
xNull.xltype = xltypeNil;
XLOPER xFuncName;
xFuncName.xltype = xltypeStr;
xFuncName.val.str = (LPSTR)"♫MSU Function ";
unsigned y, z; y=MB_OK; z=0; // set up for messagebox
iOutput = Excel4(
xlfRegister,
0,
10,
(LPXLOPER)&xDll,
(LPXLOPER)&xName,
(LPXLOPER)&xTypes,
(LPXLOPER)&xName,
(LPXLOPER)&xArgs,
&xInt,
(LPXLOPER)&xCat,
(LPXLOPER)&xNull,
(LPXLOPER)&xNull,
(LPXLOPER)&xFuncName
);
if(iOutput != xlretSuccess) {
AfxMessageBox(_T("xlfRegister failed"),y,z);
}
else {
AfxMessageBox(_T("xlfRegister succeeded"),y,z);
}
AfxMessageBox(_T("after xlfRegister"),y,z);
// Free XLL file name from the xlGetName call made earlier
Excel4(xlFree, 0, 1, (LPXLOPER)&xDll);
AfxMessageBox(_T("xlFree completed"),y,z);
return 1;
}
***************************************VB declaration code
Declare Function CMSUValue Lib "P:\FWC MSU\release\EMPERFADDIN.dll" _
( _
ByVal Sbeg As Double, _
ByVal Sval As Double, _
ByVal v As Double, _
ByVal r As Double, _
ByVal q As Double, _
ByVal divprotected As Long, _
ByVal tsrtarget As Long, _
ByVal window As Long, _
ByVal T As Double, _
ByRef paymatrixFP() As Double, _
ByVal Payout_Rows As Long, _
ByVal Iterations As Long, _
ByVal Interpolation As Long _
) _
As Double
Public Function CUV(Sbeg As Double, Sval As Double, v As Double, r As Double,
q As Double, divprotected As Long, tsrtarget As Long, window As Long, T As
Double, paymatrixFP As Range, Payout_Rows As Long, Iterations As Long,
Interpolation As Long) As Double
Dim inputArray As Variant
Dim a As Double, b As Double
Dim p() As Double
Dim i As Long, j As Long
inputArray = paymatrixFP.Value
a = UBound(inputArray, 1)
b = UBound(inputArray, 2)
ReDim p(1 To a, 1 To b)
For i = 1 To a
For j = 1 To b
p(i, j) = inputArray(i, j)
Next j
Next i
CUV = CMSUValue(Sbeg, Sval, v, r, q, divprotected, tsrtarget, window, T,
p, Payout_Rows, Iterations, Interpolation)
End Function
Ger Hobbelt
On Mar 26, 2:00 am, "lonbendler" <u58997@uwe> wrote:
> Using the Insert Function/Function Arguments box, I have been able to specify
> a source cell/value for every needed variable except for the paymatrixFP
> array. As soon as I type in the name of the range, I get the message box
> “Microsoft Office Excel has encountered a problem and needs to close.” It
> provides no real information regarding the actual error encountered.
See also http://msdn.microsoft.com/en-us/library/aa730920.aspx and in
there in particular the small item titled 'Floating-Point Matrix
Types'; also a previous question on this list at
http://groups.google.com/group/microsoft.public.excel.sdk/browse_thread/thread/af11236ae0d1f6cc?hl=en#
regarding this exact same problem:
the problem you encounter here is that a FP array has a particular
structure which does not match your VB test interface and the fact
that you say /that one/ works means you /probably/ are using a
SAFEARRAY type at the interface boundary, but that's guesswork from my
side.
See also http://support.microsoft.com/kb/207931
VB arrays are not C/C++ arrays (C arrays are /almost/ identical to
pointers and do not carry dimension info with them, unless you add
that info one way or another, e.g. through the xl_array type used by
the Excel XLL SDK. VB array != C array != Excel FP array. (VB and
Excel arrays are really 'struct'ures from the point of view of C/C++
> /* ---------------------------------------------------------------------------
>
> string lengths for each of the LPSTR's below are entered in the zeroeth
> position of the string in OCTAL by using the right Alt key and the numeric
> keypad (no leading zeroes)
> ------------------------------------------------------------------------------
> */
> xTypes.val.str = (LPSTR)"►EEEEEENNNEKEEE"; //error on 1st ref to
> paymatrix
Second, the comment is not correct, at least not for our systems, as
alt+num keypad delivers /decimal/ encoded characters. Besides, these
low value characters have character set mapping issues as soon as they
are copy&pasted into email or other communication media. It is Bad
Practice(tm) to encode these characters like this.
C and C++ provide a method to prevent this kind of issues as you can
encode characters in your source using \ escapes like these, which
incidentally /do/ happen to be in octal (or hexadecimal, see second
example)
xTypes.val.str = (LPSTR)"\016EEEEEENNNEKEEE"; //len=14d = 0x0E = 016o
xTypes.val.str = (LPSTR)"\x0EEEEEEENNNEKEEE"; //len=14d = 0x0E = 016o
> Dim p() As Double
> ReDim p(1 To a, 1 To b)
> CUV = CMSUValue(Sbeg, Sval, v, r, q, divprotected, tsrtarget, window, T,
> p, Payout_Rows, Iterations, Interpolation)
Like I said above 'guesswork by me': I'm /very/ rusty re VB types, but
my guess is this 'p' is encoded as a SAFEARRAY. Don't take my word for
it; better check and make sure. From the sound of it, you're okay
passing this VB array to your C++ code but not sure: do the floating
point values in the array exist at their expected index positions?
(maybe superfluous reminder: C/C++ uses 0-based array indexing so 1st
element is arr[0])
Regards,
Ger
lonbendler via OfficeKB.com
>
>Regards,
>
>Ger
Ger,
Thank you for your response. I agree with your analysis that I am attempting
to xlfregister a SAFEARRAY, for which there is no matching code available in
the xlfregister function. I have spent a couple of days "trying different
things" to no avail.
My plan at this point is to step back understand what I need and clearly
state these needs in a future posting.
Thank you again for your efforts on my behalf.
Lon
--
Message posted via http://www.officekb.com