Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel "Add Ins"
Hi There
I need to add some User Defined Functions to Excel 2003 - the functions are CPU intensive. The existing spreadsheet has the functions written in Excel VBA (as an XLA add in), and they are very slow. What are my choices ? Is this correct: (1) Write an XLL in C++ (2) Write a COM Add in in C++ (3) Write a COM Add in in C# (4) Write an Automation Add In in C# Has anyone done any comparisons on the performance of XLL's in C++ versus Automation Add Ins in C# ? I suppose C# is a great deal slower because of the Interop ? TIA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel "Add Ins"
Hello Ben, The comparasions probably are available somewhere, but tracking the down may prove difficult. Since the functions are CPU intensive yo want threads that are aren't very far removed from machine code. A object oriented langauge like C++ offers that capability. The downsid is the creating objects that comply with the Component Object Mode (COM). COM has over 200 built-in interfaces. Unless you are ver familiar with constructing COM objects, don't attempt this as a firs time project. A XLL would be easier to create and the speed tradeof can be kept to a marginal level. C# would be a good choice if th workbook were to be web based, but would still be slow even if i isn't. Sincerely, Leith Ros -- Leith Ros ----------------------------------------------------------------------- Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846 View this thread: http://www.excelforum.com/showthread.php?threadid=48662 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel "Add Ins"
Putting the code in a VB6 ActiveX dll can make it faster as well and has the
benefit that it is very easy. The other thing is to look at are the functions themself and see if they can be speeded up. Maybe it is worth to post them here. RBS "Ben" wrote in message ... Hi There I need to add some User Defined Functions to Excel 2003 - the functions are CPU intensive. The existing spreadsheet has the functions written in Excel VBA (as an XLA add in), and they are very slow. What are my choices ? Is this correct: (1) Write an XLL in C++ (2) Write a COM Add in in C++ (3) Write a COM Add in in C# (4) Write an Automation Add In in C# Has anyone done any comparisons on the performance of XLL's in C++ versus Automation Add Ins in C# ? I suppose C# is a great deal slower because of the Interop ? TIA |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel "Add Ins"
I agree with Bart on the ActiveX DLL, it is the easiest to get up an
running, VB being similar enough to VBA to make it straightforward. As well as looking at the formulae, check whether any intermediate calculations can be used , rather than repeat long calculations many times. -- HTH RP (remove nothere from the email address if mailing direct) "RB Smissaert" wrote in message ... Putting the code in a VB6 ActiveX dll can make it faster as well and has the benefit that it is very easy. The other thing is to look at are the functions themself and see if they can be speeded up. Maybe it is worth to post them here. RBS "Ben" wrote in message ... Hi There I need to add some User Defined Functions to Excel 2003 - the functions are CPU intensive. The existing spreadsheet has the functions written in Excel VBA (as an XLA add in), and they are very slow. What are my choices ? Is this correct: (1) Write an XLL in C++ (2) Write a COM Add in in C++ (3) Write a COM Add in in C# (4) Write an Automation Add In in C# Has anyone done any comparisons on the performance of XLL's in C++ versus Automation Add Ins in C# ? I suppose C# is a great deal slower because of the Interop ? TIA |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel "Add Ins"
Hi Bob & Bart,
With my particular UDF's I only find a relatively small performance increase by processing in a VB6 ActiveX dll. Obviously this observation only applies to my own functions and my method of implementation. FWIW the dll only does calculations like x = y * 2, albeit fairly intensively. Not object model things like x = Range("A1") * 2. Possibly I'm not doing things as efficiently as I might, in particular my UDF's exist in the first place in a normal addin like this - UDF's in a normal addin module. First do a few pre-checks on inputs, eg check if a input is "acceptable", is it an array, etc. Pass inputs to my dll function to process, then the addin UDF returns result or an array of results. Could I do this more efficiently ? A bit more info - the dll itself is a full app. It uses for other purposes the same functions exposed to the UDFs. Instancing for dll class referenced by the addin UDF's is GlobalMultiUse, so the dll functions are called directly by the addin functions. Regards, Peter T "Bob Phillips" wrote in message ... I agree with Bart on the ActiveX DLL, it is the easiest to get up an running, VB being similar enough to VBA to make it straightforward. As well as looking at the formulae, check whether any intermediate calculations can be used , rather than repeat long calculations many times. -- HTH RP (remove nothere from the email address if mailing direct) "RB Smissaert" wrote in message ... Putting the code in a VB6 ActiveX dll can make it faster as well and has the benefit that it is very easy. The other thing is to look at are the functions themself and see if they can be speeded up. Maybe it is worth to post them here. RBS "Ben" wrote in message ... Hi There I need to add some User Defined Functions to Excel 2003 - the functions are CPU intensive. The existing spreadsheet has the functions written in Excel VBA (as an XLA add in), and they are very slow. What are my choices ? Is this correct: (1) Write an XLL in C++ (2) Write a COM Add in in C++ (3) Write a COM Add in in C# (4) Write an Automation Add In in C# Has anyone done any comparisons on the performance of XLL's in C++ versus Automation Add Ins in C# ? I suppose C# is a great deal slower because of the Interop ? TIA |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel "Add Ins"
Hi Peter,
Haven't looked carefully into what kind of code gets faster and what code not, but in general I find the speed gain is between very little to a bit over twice as fast. Another option to get faster code might be to make a true, normal Windows dll with PowerBasic. I got this, but I must admit I didn't find it as easy as it was supposed to be and I haven't used it yet. It has been claimed though that certain types of code can be speeded up enormously. Laurent Longre's .xll functions are good as well and I have used his array sorting functions, making array sorts about twice as fast. There was some drawback though, which I can't remember now and I am not currently using it in my commercial app. RBS "Peter T" <peter_t@discussions wrote in message ... Hi Bob & Bart, With my particular UDF's I only find a relatively small performance increase by processing in a VB6 ActiveX dll. Obviously this observation only applies to my own functions and my method of implementation. FWIW the dll only does calculations like x = y * 2, albeit fairly intensively. Not object model things like x = Range("A1") * 2. Possibly I'm not doing things as efficiently as I might, in particular my UDF's exist in the first place in a normal addin like this - UDF's in a normal addin module. First do a few pre-checks on inputs, eg check if a input is "acceptable", is it an array, etc. Pass inputs to my dll function to process, then the addin UDF returns result or an array of results. Could I do this more efficiently ? A bit more info - the dll itself is a full app. It uses for other purposes the same functions exposed to the UDFs. Instancing for dll class referenced by the addin UDF's is GlobalMultiUse, so the dll functions are called directly by the addin functions. Regards, Peter T "Bob Phillips" wrote in message ... I agree with Bart on the ActiveX DLL, it is the easiest to get up an running, VB being similar enough to VBA to make it straightforward. As well as looking at the formulae, check whether any intermediate calculations can be used , rather than repeat long calculations many times. -- HTH RP (remove nothere from the email address if mailing direct) "RB Smissaert" wrote in message ... Putting the code in a VB6 ActiveX dll can make it faster as well and has the benefit that it is very easy. The other thing is to look at are the functions themself and see if they can be speeded up. Maybe it is worth to post them here. RBS "Ben" wrote in message ... Hi There I need to add some User Defined Functions to Excel 2003 - the functions are CPU intensive. The existing spreadsheet has the functions written in Excel VBA (as an XLA add in), and they are very slow. What are my choices ? Is this correct: (1) Write an XLL in C++ (2) Write a COM Add in in C++ (3) Write a COM Add in in C# (4) Write an Automation Add In in C# Has anyone done any comparisons on the performance of XLL's in C++ versus Automation Add Ins in C# ? I suppose C# is a great deal slower because of the Interop ? TIA |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel "Add Ins"
Hi Bart,
Thanks for your comments. PowerBasic looks interesting but I note your comments about not so easy to learn & use, one day perhaps. Regards, Peter T "RB Smissaert" wrote in message ... Hi Peter, Haven't looked carefully into what kind of code gets faster and what code not, but in general I find the speed gain is between very little to a bit over twice as fast. Another option to get faster code might be to make a true, normal Windows dll with PowerBasic. I got this, but I must admit I didn't find it as easy as it was supposed to be and I haven't used it yet. It has been claimed though that certain types of code can be speeded up enormously. Laurent Longre's .xll functions are good as well and I have used his array sorting functions, making array sorts about twice as fast. There was some drawback though, which I can't remember now and I am not currently using it in my commercial app. RBS "Peter T" <peter_t@discussions wrote in message ... Hi Bob & Bart, With my particular UDF's I only find a relatively small performance increase by processing in a VB6 ActiveX dll. Obviously this observation only applies to my own functions and my method of implementation. FWIW the dll only does calculations like x = y * 2, albeit fairly intensively. Not object model things like x = Range("A1") * 2. Possibly I'm not doing things as efficiently as I might, in particular my UDF's exist in the first place in a normal addin like this - UDF's in a normal addin module. First do a few pre-checks on inputs, eg check if a input is "acceptable", is it an array, etc. Pass inputs to my dll function to process, then the addin UDF returns result or an array of results. Could I do this more efficiently ? A bit more info - the dll itself is a full app. It uses for other purposes the same functions exposed to the UDFs. Instancing for dll class referenced by the addin UDF's is GlobalMultiUse, so the dll functions are called directly by the addin functions. Regards, Peter T "Bob Phillips" wrote in message ... I agree with Bart on the ActiveX DLL, it is the easiest to get up an running, VB being similar enough to VBA to make it straightforward. As well as looking at the formulae, check whether any intermediate calculations can be used , rather than repeat long calculations many times. -- HTH RP (remove nothere from the email address if mailing direct) "RB Smissaert" wrote in message ... Putting the code in a VB6 ActiveX dll can make it faster as well and has the benefit that it is very easy. The other thing is to look at are the functions themself and see if they can be speeded up. Maybe it is worth to post them here. RBS "Ben" wrote in message ... Hi There I need to add some User Defined Functions to Excel 2003 - the functions are CPU intensive. The existing spreadsheet has the functions written in Excel VBA (as an XLA add in), and they are very slow. What are my choices ? Is this correct: (1) Write an XLL in C++ (2) Write a COM Add in in C++ (3) Write a COM Add in in C# (4) Write an Automation Add In in C# Has anyone done any comparisons on the performance of XLL's in C++ versus Automation Add Ins in C# ? I suppose C# is a great deal slower because of the Interop ? TIA |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel "Add Ins"
Hi Peter,
It probably is quite easy, but I had the impression before I bought it that it would be just like VBA or VB and that is not the case. The IDE is not that great either. RBS "Peter T" <peter_t@discussions wrote in message ... Hi Bart, Thanks for your comments. PowerBasic looks interesting but I note your comments about not so easy to learn & use, one day perhaps. Regards, Peter T "RB Smissaert" wrote in message ... Hi Peter, Haven't looked carefully into what kind of code gets faster and what code not, but in general I find the speed gain is between very little to a bit over twice as fast. Another option to get faster code might be to make a true, normal Windows dll with PowerBasic. I got this, but I must admit I didn't find it as easy as it was supposed to be and I haven't used it yet. It has been claimed though that certain types of code can be speeded up enormously. Laurent Longre's .xll functions are good as well and I have used his array sorting functions, making array sorts about twice as fast. There was some drawback though, which I can't remember now and I am not currently using it in my commercial app. RBS "Peter T" <peter_t@discussions wrote in message ... Hi Bob & Bart, With my particular UDF's I only find a relatively small performance increase by processing in a VB6 ActiveX dll. Obviously this observation only applies to my own functions and my method of implementation. FWIW the dll only does calculations like x = y * 2, albeit fairly intensively. Not object model things like x = Range("A1") * 2. Possibly I'm not doing things as efficiently as I might, in particular my UDF's exist in the first place in a normal addin like this - UDF's in a normal addin module. First do a few pre-checks on inputs, eg check if a input is "acceptable", is it an array, etc. Pass inputs to my dll function to process, then the addin UDF returns result or an array of results. Could I do this more efficiently ? A bit more info - the dll itself is a full app. It uses for other purposes the same functions exposed to the UDFs. Instancing for dll class referenced by the addin UDF's is GlobalMultiUse, so the dll functions are called directly by the addin functions. Regards, Peter T "Bob Phillips" wrote in message ... I agree with Bart on the ActiveX DLL, it is the easiest to get up an running, VB being similar enough to VBA to make it straightforward. As well as looking at the formulae, check whether any intermediate calculations can be used , rather than repeat long calculations many times. -- HTH RP (remove nothere from the email address if mailing direct) "RB Smissaert" wrote in message ... Putting the code in a VB6 ActiveX dll can make it faster as well and has the benefit that it is very easy. The other thing is to look at are the functions themself and see if they can be speeded up. Maybe it is worth to post them here. RBS "Ben" wrote in message ... Hi There I need to add some User Defined Functions to Excel 2003 - the functions are CPU intensive. The existing spreadsheet has the functions written in Excel VBA (as an XLA add in), and they are very slow. What are my choices ? Is this correct: (1) Write an XLL in C++ (2) Write a COM Add in in C++ (3) Write a COM Add in in C# (4) Write an Automation Add In in C# Has anyone done any comparisons on the performance of XLL's in C++ versus Automation Add Ins in C# ? I suppose C# is a great deal slower because of the Interop ? TIA |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel "Add Ins"
Thanks for your feedback ! After looking at COM I think I will avoid it
initially and go for an XLL. I will also avoid VSTO as I am stuck using VS 2003 (long story) and anyways, VSTO looks to me like a technology that is not quite ready. It is essentially .NET "shoe-horned" into Office, which is COM based. I will probably go over to VSTO when Office 12 arrives and it is truly a ..NET based product. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Help!!! Enter "7" in a cell and Excel changes the "7" to "11" immediately!!! | Excel Discussion (Misc queries) | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |