Excel 2000 calling functions in C++

[um]

Hello,

I am looking for a way to call a function from within Excel, like

@myfunc(B110, E1)

which executes a C++-based function (potentially dll or COM) which passes
the cell range i.e B110, as well as the location where to store the result
of myfunc, i.e. E1. Subsequently the myfunc in the dll does it's math magic
and passes the result back to the spreadsheet into cell E1.

This can't be too difficult, except all the samples I see are VBA based. I
also downloaded the COMAddin sample which is C++-based, but I can't seem to
get the pointer to a range of cells which I marked prior to clicking the
MyCommandBar button in the spreadsheet.

I have spent a lot of time in searches and I am getting frustrated about
this.

Does anyone have a good pointer to a sample or a sample which I could
dissect and use as a basis to start my project from? No VBA please!!!

Thank you much in advance!

um

 

[Rob van Gelder]

Take a look at: support.microsoft.com/support/ kb/articles/Q152/1/52.asp

 

[um]

Thank you Rob,

there seems to be a problem with the url you posted... "Q152/1/52.asp"
sounds wrong for a KB article ..??

Can you point me to the correct one?

Thanks again,

um

 

[um]

Ok Rob,

it's article Q152152, found it.

Thanks again!

um

 

[um]

Rob,

do you have anything else in terms of samples? When I compile the one from
Q152152, it will only get recognized by Excel, if I compile to Release of
VC6.0. The Debug version does not get recognized, and either Debug or
Release of VC7.0 do not get recognized either. Kinda hard to do, if you
can't use Debug code..

Thanks a mille,

Uwe

 

[Rob Bovey]

Hi Uwe,

<<When I compile the one from Q152152, it will only get recognized by Excel,
if I compile to Release of VC6.0. The Debug version does not get
recognized>>

Under the Project/Settings/C/C++ menu make sure that the Debug Info
setting is Program Database and not Program Database for Edit and Continue.
The latter setting adds tokens to the debug code that cause Excel not to
recognize it.

<<and either Debug or Release of VC7.0 do not get recognized either>>

Same as above for the debug version, except the menu is Project/<Project
Name> Properties/C/C++/General. Also make sure that the .def file has been
properly associated with the project. Under the menu Project/<Project Name>
Properties/Linker/Input the .def file should be listed in the Module
Definition File setting.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[um]

Hey Rob,

who thought this was easy?

Now Excel recognizes the generic.xll when created in VC7 Debug mode. -
Release mode still doesn't work (no /ZI or /Zi switches there, and DEF file
is properly set in linker properties) .

The other thing is the handling of XL strings, i.e. in XLauto.cpp in the
function GetXLVersion:

--------- snipet--------
lstrcpyn(szXLVersion, xVersion.val.str + 1, *((BYTE *) xVersion.val.str) +
1);
szXLVersion[*((BYTE *) xVersion.val.str)] = NULL;
--------- end snipet--------

the code crashes on the 2nd line, I see nothing wrong. szXLVersion looks
like it's properly allocated, so what's wrong with placing a terminating
zero at the end of the string??? Very strange.. Same crash happens (access
violation) if somewhere in the sample an XL string is created and the first
byte of the string is set to the string's length.....

Can you help again?

Thanks a mille,

Uwe

 

[Rob Bovey]

Hi Uwe,

Now Excel recognizes the generic.xll when created in VC7 Debug mode. -
Release mode still doesn't work (no /ZI or /Zi switches there, and DEF file
is properly set in linker properties) .

Not really sure what else is going on here. Are all your functions
exported as extern "C" __declspec(dllexport)?

The other thing is the handling of XL strings, i.e. in XLauto.cpp in the
function GetXLVersion:

I think the problem here is that you're not allowed to modify a string
litteral. VC6 let you get away with it but VC7+ doesn't.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Hey Rob,

who thought this was easy?

Now Excel recognizes the generic.xll when created in VC7 Debug mode. -
Release mode still doesn't work (no /ZI or /Zi switches there, and DEF file
is properly set in linker properties) .

The other thing is the handling of XL strings, i.e. in XLauto.cpp in the
function GetXLVersion:

--------- snipet--------
lstrcpyn(szXLVersion, xVersion.val.str + 1, *((BYTE *) xVersion.val.str) +
1);
szXLVersion[*((BYTE *) xVersion.val.str)] = NULL;
--------- end snipet--------

the code crashes on the 2nd line, I see nothing wrong. szXLVersion looks
like it's properly allocated, so what's wrong with placing a terminating
zero at the end of the string??? Very strange.. Same crash happens (access
violation) if somewhere in the sample an XL string is created and the first
byte of the string is set to the string's length.....

Can you help again?

Thanks a mille,

Uwe

Hi Uwe,

<<When I compile the one from Q152152, it will only get recognized by Excel,
if I compile to Release of VC6.0. The Debug version does not get
recognized>>

Under the Project/Settings/C/C++ menu make sure that the Debug Info
setting is Program Database and not Program Database for Edit and Continue.
The latter setting adds tokens to the debug code that cause Excel not to
recognize it.

<<and either Debug or Release of VC7.0 do not get recognized either>>

Same as above for the debug version, except the menu is Project/<Project
Name> Properties/C/C++/General. Also make sure that the .def file has been
properly associated with the project. Under the menu Project/<Project Name>
Properties/Linker/Input the .def file should be listed in the Module
Definition File setting.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[um]

Hello Rob,

all is fine. You were right, I had to perform major surgery on the sample,
so that VC7 would run this in Debug and Release mode. The literal string
which you can't modify, was to blame. If you'd like a copy of the modified
sample that has all that "static LPSTR" business removed, let me know and
I'll zip it up for you.

Now the next question is, the code returns one numeric value and properly
places it into the cell, where you place the formula. All good, except, now
I want to fill a range with results of my magic math. I have made the
"result range" - the place where multiple results are to be stored - part of
the function call syntax. How do I get a series of values, stored in a C++
array in the XLL, back to the spreadsheet, into the cell range which I have
passed to the C++ XLL.....???

Thanks a mille,

Uwe

Hi Uwe,

Now Excel recognizes the generic.xll when created in VC7 Debug mode. -
Release mode still doesn't work (no /ZI or /Zi switches there, and DEF file
is properly set in linker properties) .

Not really sure what else is going on here. Are all your functions
exported as extern "C" __declspec(dllexport)?

The other thing is the handling of XL strings, i.e. in XLauto.cpp in the
function GetXLVersion:

I think the problem here is that you're not allowed to modify a string
litteral. VC6 let you get away with it but VC7+ doesn't.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Hey Rob,

who thought this was easy?

Now Excel recognizes the generic.xll when created in VC7 Debug mode. -
Release mode still doesn't work (no /ZI or /Zi switches there, and DEF file
is properly set in linker properties) .

The other thing is the handling of XL strings, i.e. in XLauto.cpp in the
function GetXLVersion:

--------- snipet--------
lstrcpyn(szXLVersion, xVersion.val.str + 1, *((BYTE *) xVersion.val.str) +
1);
szXLVersion[*((BYTE *) xVersion.val.str)] = NULL;
--------- end snipet--------

the code crashes on the 2nd line, I see nothing wrong. szXLVersion looks
like it's properly allocated, so what's wrong with placing a terminating
zero at the end of the string??? Very strange.. Same crash happens (access
violation) if somewhere in the sample an XL string is created and the first
byte of the string is set to the string's length.....

Can you help again?

Thanks a mille,

Uwe

Hi Uwe,

<<When I compile the one from Q152152, it will only get recognized by Excel,
if I compile to Release of VC6.0. The Debug version does not get
recognized>>

Under the Project/Settings/C/C++ menu make sure that the Debug Info
setting is Program Database and not Program Database for Edit and Continue.
The latter setting adds tokens to the debug code that cause Excel not to
recognize it.

<<and either Debug or Release of VC7.0 do not get recognized either>>

Same as above for the debug version, except the menu is Project/<Project
Name> Properties/C/C++/General. Also make sure that the .def file has been
properly associated with the project. Under the menu Project/<Project Name>
Properties/Linker/Input the .def file should be listed in the Module
Definition File setting.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


Rob,

do you have anything else in terms of samples? When I compile the

one
from

Q152152, it will only get recognized by Excel, if I compile to

Release
of

VC6.0. The Debug version does not get recognized, and either Debug or
Release of VC7.0 do not get recognized either. Kinda hard to do, if you
can't use Debug code..

Thanks a mille,

Uwe

 

[Rob Bovey]

Hi Uwe,

I want to fill a range with results of my magic math. I have made the
"result range" - the place where multiple results are to be stored - part of
the function call syntax. How do I get a series of values, stored in a C++
array in the XLL, back to the spreadsheet, into the cell range which I have
passed to the C++ XLL.....???

A worksheet function can only modify the cell into which it has been
entered. It sounds like what you want is an array function. In that case you
need to return a type xltypeMulti with the same number of rows and columns
as your data. You would then array-enter this function into your destination
range.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Hello Rob,

all is fine. You were right, I had to perform major surgery on the sample,
so that VC7 would run this in Debug and Release mode. The literal string
which you can't modify, was to blame. If you'd like a copy of the modified
sample that has all that "static LPSTR" business removed, let me know and
I'll zip it up for you.

Now the next question is, the code returns one numeric value and properly
places it into the cell, where you place the formula. All good, except, now
I want to fill a range with results of my magic math. I have made the
"result range" - the place where multiple results are to be stored - part of
the function call syntax. How do I get a series of values, stored in a C++
array in the XLL, back to the spreadsheet, into the cell range which I have
passed to the C++ XLL.....???

Thanks a mille,

Uwe

Hi Uwe,


Not really sure what else is going on here. Are all your functions
exported as extern "C" __declspec(dllexport)?


I think the problem here is that you're not allowed to modify a string
litteral. VC6 let you get away with it but VC7+ doesn't.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

xVersion.val.str)

+

1);
szXLVersion[*((BYTE *) xVersion.val.str)] = NULL;
--------- end snipet--------

the code crashes on the 2nd line, I see nothing wrong. szXLVersion looks
like it's properly allocated, so what's wrong with placing a terminating
zero at the end of the string??? Very strange.. Same crash happens (access
violation) if somewhere in the sample an XL string is created and the first
byte of the string is set to the string's length.....

Can you help again?

Thanks a mille,

Uwe



Hi Uwe,

<<When I compile the one from Q152152, it will only get recognized by
Excel,
if I compile to Release of VC6.0. The Debug version does not get
recognized>>

Under the Project/Settings/C/C++ menu make sure that the Debug Info
setting is Program Database and not Program Database for Edit and
Continue.
The latter setting adds tokens to the debug code that cause Excel

not

to has
been if
you

 

[um]

Thank you Rob!

Is there a description somewhere as to the transfer syntax for the different
xl types, like xltypeMulti? I have no clue where to start when you say "You
would then array-enter this function into your destination range".

Thank you soo much!

Uwe

Hi Uwe,

I want to fill a range with results of my magic math. I have made the
"result range" - the place where multiple results are to be stored -

part

of
the function call syntax. How do I get a series of values, stored in a C++
array in the XLL, back to the spreadsheet, into the cell range which I have
passed to the C++ XLL.....???

A worksheet function can only modify the cell into which it has been
entered. It sounds like what you want is an array function. In that case you
need to return a type xltypeMulti with the same number of rows and columns
as your data. You would then array-enter this function into your destination
range.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Hello Rob,

all is fine. You were right, I had to perform major surgery on the sample,
so that VC7 would run this in Debug and Release mode. The literal string
which you can't modify, was to blame. If you'd like a copy of the modified
sample that has all that "static LPSTR" business removed, let me know and
I'll zip it up for you.

Now the next question is, the code returns one numeric value and properly
places it into the cell, where you place the formula. All good, except, now
I want to fill a range with results of my magic math. I have made the
"result range" - the place where multiple results are to be stored -

part

of
the function call syntax. How do I get a series of values, stored in a C++
array in the XLL, back to the spreadsheet, into the cell range which I have
passed to the C++ XLL.....???

Thanks a mille,

Uwe

Hi Uwe,

Now Excel recognizes the generic.xll when created in VC7 Debug mode. -
Release mode still doesn't work (no /ZI or /Zi switches there, and DEF
file
is properly set in linker properties) .

Not really sure what else is going on here. Are all your functions
exported as extern "C" __declspec(dllexport)?

The other thing is the handling of XL strings, i.e. in XLauto.cpp in the
function GetXLVersion:

I think the problem here is that you're not allowed to modify a string
litteral. VC6 let you get away with it but VC7+ doesn't.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


Hey Rob,

who thought this was easy?

Now Excel recognizes the generic.xll when created in VC7 Debug mode. -
Release mode still doesn't work (no /ZI or /Zi switches there, and DEF
file
is properly set in linker properties) .

The other thing is the handling of XL strings, i.e. in XLauto.cpp in the
function GetXLVersion:

--------- snipet--------
lstrcpyn(szXLVersion, xVersion.val.str + 1, *((BYTE *)

xVersion.val.str)

+
1);
szXLVersion[*((BYTE *) xVersion.val.str)] = NULL;
--------- end snipet--------

the code crashes on the 2nd line, I see nothing wrong. szXLVersion looks
like it's properly allocated, so what's wrong with placing a terminating
zero at the end of the string??? Very strange.. Same crash happens (access
violation) if somewhere in the sample an XL string is created and the
first
byte of the string is set to the string's length.....

Can you help again?

Thanks a mille,

Uwe



Hi Uwe,

<<When I compile the one from Q152152, it will only get recognized by
Excel,
if I compile to Release of VC6.0. The Debug version does not get
recognized>>

Under the Project/Settings/C/C++ menu make sure that the Debug Info
setting is Program Database and not Program Database for Edit and
Continue.
The latter setting adds tokens to the debug code that cause Excel

not

to
recognize it.

<<and either Debug or Release of VC7.0 do not get recognized either>>

Same as above for the debug version, except the menu is
Project/<Project
Name> Properties/C/C++/General. Also make sure that the .def file has
been
properly associated with the project. Under the menu Project/<Project
Name>
Properties/Linker/Input the .def file should be listed in the Module
Definition File setting.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


Rob,

do you have anything else in terms of samples? When I compile

the
one

from
Q152152, it will only get recognized by Excel, if I compile to Release
of
VC6.0. The Debug version does not get recognized, and either

Debug
or

Release of VC7.0 do not get recognized either. Kinda hard to do, if
you
can't use Debug code..

Thanks a mille,

Uwe

 

[Rob Bovey]

Hi Uwe,

In very simplistic terms, an array function is a worksheet function that
returns an array of values instead of a single value. (I know it's a lot
more complicated than that, so don't all you worksheet function mavens start
jumping on me. <g>) An array function is entered on a worksheet using
Ctrl+Shift+Enter instead of just the Enter key.

I've thrown together a very simple demo array function below that just
enters the numbers 1 through 4 into four worksheet cells in a row:

EXPORT LPXLOPER DemoArray(void)
{
static XLOPER xlArray, xlValues[1][4];
int i;
for (i = 0; i < 4; ++i)
{
xlValues[0].xltype = xltypeNum;
xlValues[0].val.num = i + 1;
}
xlArray.xltype = xltypeMulti;
xlArray.val.array.lparray = &xlValues[0][0];
xlArray.val.array.rows = 4;
xlArray.val.array.columns = 1;
return &xlArray;
}

Compile this function into your XLL, open it in Excel, select range
A1:A4, enter =DemoArray() and press Ctrl+Shift+Enter (you only need to enter
it in the first cell, Excel will fill all the selected cells automatically).
You should see the numbers 1 through 4 in cells A1 through A4 respectively.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Thank you Rob!

Is there a description somewhere as to the transfer syntax for the different
xl types, like xltypeMulti? I have no clue where to start when you say "You
would then array-enter this function into your destination range".

Thank you soo much!

Uwe

Hi Uwe,

I want to fill a range with results of my magic math. I have made the
"result range" - the place where multiple results are to be stored -

part

of
the function call syntax. How do I get a series of values, stored in a C++
array in the XLL, back to the spreadsheet, into the cell range which I have
passed to the C++ XLL.....???

A worksheet function can only modify the cell into which it has been
entered. It sounds like what you want is an array function. In that case you
need to return a type xltypeMulti with the same number of rows and columns
as your data. You would then array-enter this function into your destination
range.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Hello Rob,

all is fine. You were right, I had to perform major surgery on the sample,
so that VC7 would run this in Debug and Release mode. The literal string
which you can't modify, was to blame. If you'd like a copy of the modified
sample that has all that "static LPSTR" business removed, let me know and
I'll zip it up for you.

Now the next question is, the code returns one numeric value and properly
places it into the cell, where you place the formula. All good,

except,
now

I want to fill a range with results of my magic math. I have made the
"result range" - the place where multiple results are to be stored -

part

of
the function call syntax. How do I get a series of values, stored in a C++
array in the XLL, back to the spreadsheet, into the cell range which I have
passed to the C++ XLL.....???

Thanks a mille,

Uwe






Hi Uwe,

Now Excel recognizes the generic.xll when created in VC7 Debug mode. -
Release mode still doesn't work (no /ZI or /Zi switches there, and DEF
file
is properly set in linker properties) .

Not really sure what else is going on here. Are all your functions
exported as extern "C" __declspec(dllexport)?

The other thing is the handling of XL strings, i.e. in XLauto.cpp

in
the

function GetXLVersion:

I think the problem here is that you're not allowed to modify a string
litteral. VC6 let you get away with it but VC7+ doesn't.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


Hey Rob,

who thought this was easy?

Now Excel recognizes the generic.xll when created in VC7 Debug mode. -
Release mode still doesn't work (no /ZI or /Zi switches there, and DEF
file
is properly set in linker properties) .

The other thing is the handling of XL strings, i.e. in XLauto.cpp

in
the

function GetXLVersion:

--------- snipet--------
lstrcpyn(szXLVersion, xVersion.val.str + 1, *((BYTE *) xVersion.val.str)
+
1);
szXLVersion[*((BYTE *) xVersion.val.str)] = NULL;
--------- end snipet--------

the code crashes on the 2nd line, I see nothing wrong. szXLVersion looks
like it's properly allocated, so what's wrong with placing a terminating
zero at the end of the string??? Very strange.. Same crash happens
(access
violation) if somewhere in the sample an XL string is created and the
first
byte of the string is set to the string's length.....

Can you help again?

Thanks a mille,

Uwe



Hi Uwe,

<<When I compile the one from Q152152, it will only get

recognized
by

Excel,
if I compile to Release of VC6.0. The Debug version does not get
recognized>>

Under the Project/Settings/C/C++ menu make sure that the Debug
Info
setting is Program Database and not Program Database for Edit and
Continue.
The latter setting adds tokens to the debug code that cause

Excel
not

to
recognize it.

<<and either Debug or Release of VC7.0 do not get recognized either>>

Same as above for the debug version, except the menu is
Project/<Project
Name> Properties/C/C++/General. Also make sure that the .def

file
has

been
properly associated with the project. Under the menu Project/<Project
Name>
Properties/Linker/Input the .def file should be listed in the Module
Definition File setting.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


Rob,

do you have anything else in terms of samples? When I compile the
one
from
Q152152, it will only get recognized by Excel, if I compile to
Release
of
VC6.0. The Debug version does not get recognized, and either Debug
or
Release of VC7.0 do not get recognized either. Kinda hard to

do,
if

you
can't use Debug code..

Thanks a mille,

Uwe

 

[um]

Rob, you are gold!

I guess the only remaining question now is.. How can I fill the spreadsheet
cell range automatically with my array results?

For instance, if my func looks like this:

@SomeFuncX(A1:B7, C1:C7)

the first range being some input vals, the second range be the array where
to store the results, 7 in this sample. In effect, the program knows where
to store the results by feeding the "output array range" with the
function...

With your suggestion in your last post the above would look like this:

@SomeFuncY(A1:B7),

but the user would have to use that Array Function voodoo, not that
convenient...

Do you have any comments on that?

Thank you again and again!!!

Uwe

Hi Uwe,

In very simplistic terms, an array function is a worksheet function that
returns an array of values instead of a single value. (I know it's a lot
more complicated than that, so don't all you worksheet function mavens start
jumping on me. <g>) An array function is entered on a worksheet using
Ctrl+Shift+Enter instead of just the Enter key.

I've thrown together a very simple demo array function below that just
enters the numbers 1 through 4 into four worksheet cells in a row:

EXPORT LPXLOPER DemoArray(void)
{
static XLOPER xlArray, xlValues[1][4];
int i;
for (i = 0; i < 4; ++i)
{
xlValues[0].xltype = xltypeNum;
xlValues[0].val.num = i + 1;
}
xlArray.xltype = xltypeMulti;
xlArray.val.array.lparray = &xlValues[0][0];
xlArray.val.array.rows = 4;
xlArray.val.array.columns = 1;
return &xlArray;
}

Compile this function into your XLL, open it in Excel, select range
A1:A4, enter =DemoArray() and press Ctrl+Shift+Enter (you only need to enter
it in the first cell, Excel will fill all the selected cells automatically).
You should see the numbers 1 through 4 in cells A1 through A4 respectively.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Thank you Rob!

Is there a description somewhere as to the transfer syntax for the different
xl types, like xltypeMulti? I have no clue where to start when you say "You
would then array-enter this function into your destination range".

Thank you soo much!

Uwe


a
C++ case
you know
and a
C++ and
DEF

XLauto.cpp

in and
DEF

XLauto.cpp

in

the
function GetXLVersion:

--------- snipet--------
lstrcpyn(szXLVersion, xVersion.val.str + 1, *((BYTE *)
xVersion.val.str)
+
1);
szXLVersion[*((BYTE *) xVersion.val.str)] = NULL;
--------- end snipet--------

the code crashes on the 2nd line, I see nothing wrong. szXLVersion
looks
like it's properly allocated, so what's wrong with placing a
terminating
zero at the end of the string??? Very strange.. Same crash happens
(access
violation) if somewhere in the sample an XL string is created

and
the

first
byte of the string is set to the string's length.....

Can you help again?

Thanks a mille,

Uwe



Hi Uwe,

<<When I compile the one from Q152152, it will only get recognized
by
Excel,
if I compile to Release of VC6.0. The Debug version does not get
recognized>>

Under the Project/Settings/C/C++ menu make sure that the Debug
Info
setting is Program Database and not Program Database for Edit and
Continue.
The latter setting adds tokens to the debug code that cause Excel
not
to
recognize it.

<<and either Debug or Release of VC7.0 do not get recognized
either>>

Same as above for the debug version, except the menu is
Project/<Project
Name> Properties/C/C++/General. Also make sure that the .def file
has
been
properly associated with the project. Under the menu
Project/<Project
Name>
Properties/Linker/Input the .def file should be listed in the Module
Definition File setting.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


Rob,

do you have anything else in terms of samples? When I

compile
the

one
from
Q152152, it will only get recognized by Excel, if I compile to
Release
of
VC6.0. The Debug version does not get recognized, and either Debug
or
Release of VC7.0 do not get recognized either. Kinda hard to do,
if
you
can't use Debug code..

Thanks a mille,

Uwe

 

[Rob Bovey]

Hi Uwe,

I guess the only remaining question now is.. How can I fill the spreadsheet
cell range automatically with my array results?

Unfortunately, you can't. A worksheet function can only modify the cells
it's been entered in. There's no way around this that I'm aware of. The user
will have to array-enter your function in the correct output range.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Rob, you are gold!

I guess the only remaining question now is.. How can I fill the spreadsheet
cell range automatically with my array results?

For instance, if my func looks like this:

@SomeFuncX(A1:B7, C1:C7)

the first range being some input vals, the second range be the array where
to store the results, 7 in this sample. In effect, the program knows where
to store the results by feeding the "output array range" with the
function...

With your suggestion in your last post the above would look like this:

@SomeFuncY(A1:B7),

but the user would have to use that Array Function voodoo, not that
convenient...

Do you have any comments on that?

Thank you again and again!!!

Uwe

Hi Uwe,

In very simplistic terms, an array function is a worksheet function that
returns an array of values instead of a single value. (I know it's a lot
more complicated than that, so don't all you worksheet function mavens start
jumping on me. <g>) An array function is entered on a worksheet using
Ctrl+Shift+Enter instead of just the Enter key.

I've thrown together a very simple demo array function below that just
enters the numbers 1 through 4 into four worksheet cells in a row:

EXPORT LPXLOPER DemoArray(void)
{
static XLOPER xlArray, xlValues[1][4];
int i;
for (i = 0; i < 4; ++i)
{
xlValues[0].xltype = xltypeNum;
xlValues[0].val.num = i + 1;
}
xlArray.xltype = xltypeMulti;
xlArray.val.array.lparray = &xlValues[0][0];
xlArray.val.array.rows = 4;
xlArray.val.array.columns = 1;
return &xlArray;
}

Compile this function into your XLL, open it in Excel, select range
A1:A4, enter =DemoArray() and press Ctrl+Shift+Enter (you only need to enter
it in the first cell, Excel will fill all the selected cells automatically).
You should see the numbers 1 through 4 in cells A1 through A4 respectively.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

Thank you Rob!

Is there a description somewhere as to the transfer syntax for the different
xl types, like xltypeMulti? I have no clue where to start when you say "You
would then array-enter this function into your destination range".

Thank you soo much!

Uwe


Hi Uwe,

I want to fill a range with results of my magic math. I have made the
"result range" - the place where multiple results are to be stored -
part
of
the function call syntax. How do I get a series of values, stored

in

a

which

I

in

a

which

I XLauto.cpp

modify

a

string
litteral. VC6 let you get away with it but VC7+ doesn't.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


Hey Rob,

who thought this was easy?

Now Excel recognizes the generic.xll when created in VC7 Debug
mode. -
Release mode still doesn't work (no /ZI or /Zi switches there, and
DEF
file
is properly set in linker properties) .

The other thing is the handling of XL strings, i.e. in

XLauto.cpp

in
the
function GetXLVersion:

--------- snipet--------
lstrcpyn(szXLVersion, xVersion.val.str + 1, *((BYTE *)
xVersion.val.str)
+
1);
szXLVersion[*((BYTE *) xVersion.val.str)] = NULL;
--------- end snipet--------

the code crashes on the 2nd line, I see nothing wrong. szXLVersion
looks
like it's properly allocated, so what's wrong with placing a
terminating
zero at the end of the string??? Very strange.. Same crash happens
(access
violation) if somewhere in the sample an XL string is created and
the
first
byte of the string is set to the string's length.....

Can you help again?

Thanks a mille,

Uwe



Hi Uwe,

<<When I compile the one from Q152152, it will only get recognized
by
Excel,
if I compile to Release of VC6.0. The Debug version does not get
recognized>>

Under the Project/Settings/C/C++ menu make sure that the Debug
Info
setting is Program Database and not Program Database for

Edit
and

Continue.
The latter setting adds tokens to the debug code that cause Excel
not
to
recognize it.

<<and either Debug or Release of VC7.0 do not get recognized
either>>

Same as above for the debug version, except the menu is
Project/<Project
Name> Properties/C/C++/General. Also make sure that the .def file
has
been
properly associated with the project. Under the menu
Project/<Project
Name>
Properties/Linker/Input the .def file should be listed in the
Module
Definition File setting.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


Rob,

do you have anything else in terms of samples? When I compile
the
one
from
Q152152, it will only get recognized by Excel, if I

compile

to to
do,