Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 calling functions in C++
Hello,
I am looking for a way to call a function from within Excel, like @myfunc(B1:D10, E1) which executes a C++-based function (potentially dll or COM) which passes the cell range i.e B1:D10, 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 calling functions in C++
Take a look at: support.microsoft.com/support/ kb/articles/Q152/1/52.asp
-- Rob van Gelder - http://www.vangelder.co.nz/excel "um" wrote in message ... Hello, I am looking for a way to call a function from within Excel, like @myfunc(B1:D10, E1) which executes a C++-based function (potentially dll or COM) which passes the cell range i.e B1:D10, 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 calling functions in C++
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 "Rob van Gelder" wrote in message ... Take a look at: support.microsoft.com/support/ kb/articles/Q152/1/52.asp -- Rob van Gelder - http://www.vangelder.co.nz/excel "um" wrote in message ... Hello, I am looking for a way to call a function from within Excel, like @myfunc(B1:D10, E1) which executes a C++-based function (potentially dll or COM) which passes the cell range i.e B1:D10, 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 calling functions in C++
Ok Rob,
it's article Q152152, found it. Thanks again! um "um" wrote in message ... 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 "Rob van Gelder" wrote in message ... Take a look at: support.microsoft.com/support/ kb/articles/Q152/1/52.asp -- Rob van Gelder - http://www.vangelder.co.nz/excel "um" wrote in message ... Hello, I am looking for a way to call a function from within Excel, like @myfunc(B1:D10, E1) which executes a C++-based function (potentially dll or COM) which passes the cell range i.e B1:D10, 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 calling functions in C++
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" wrote in message ... Ok Rob, it's article Q152152, found it. Thanks again! um "um" wrote in message ... 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 "Rob van Gelder" wrote in message ... Take a look at: support.microsoft.com/support/ kb/articles/Q152/1/52.asp -- Rob van Gelder - http://www.vangelder.co.nz/excel "um" wrote in message ... Hello, I am looking for a way to call a function from within Excel, like @myfunc(B1:D10, E1) which executes a C++-based function (potentially dll or COM) which passes the cell range i.e B1:D10, 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 calling functions in C++
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" wrote in message .. . 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 calling functions in C++
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" wrote in message ... 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" wrote in message .. . 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 calling functions in C++
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 * "um" wrote in message ... 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" wrote in message ... 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" wrote in message .. . 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 calling functions in C++
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 "Rob Bovey" wrote in message ... 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 * "um" wrote in message ... 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" wrote in message ... 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" wrote in message .. . 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 calling functions in C++
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 * "um" wrote in message ... 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 "Rob Bovey" wrote in message ... 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 * "um" wrote in message ... 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" wrote in message ... 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" wrote in message .. . 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 calling functions in C++
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 "Rob Bovey" wrote in message ... 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 * "um" wrote in message ... 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 "Rob Bovey" wrote in message ... 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 * "um" wrote in message ... 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" wrote in message ... 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" wrote in message .. . 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 calling functions in C++
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][i].xltype = xltypeNum; xlValues[0][i].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 * "um" wrote in message ... 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 "Rob Bovey" wrote in message ... 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 * "um" wrote in message ... 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 "Rob Bovey" wrote in message ... 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 * "um" wrote in message ... 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" wrote in message ... 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" wrote in message .. . 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 calling functions in C++
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 "Rob Bovey" wrote in message ... 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[i] 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][i].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 * "um" wrote in message ... 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 "Rob Bovey" wrote in message ... 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 * "um" wrote in message ... 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 "Rob Bovey" wrote in message ... 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 * "um" wrote in message ... 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" wrote in message ... 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" wrote in message .. . 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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2000 calling functions in C++
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 * "um" wrote in message ... 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 "Rob Bovey" wrote in message ... 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[i][i] 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 * "um" wrote in message ... 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 "Rob Bovey" wrote in message ... 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 * "um" wrote in message ... 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 "Rob Bovey" wrote in message ... 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 * "um" wrote in message ... 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" wrote in message ... 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" wrote in message .. . 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calling a C++ program from Excel 2000 and passing a result from C++ back to an excel spreadsheet | Excel Discussion (Misc queries) | |||
calling a stored procedure on MS SQL Server within MS Excel 2000 | Excel Programming | |||
Calling Excel array functions in VBA | Excel Programming | |||
Calling functions from Excel worksheets and the Find function in XL '97 | Excel Programming | |||
Calling certain functions within VBA | Excel Programming |