raw speed for excel worksheet functions? - xll -or com addin?
Hi,
A question for those of you who have experimented with this. I want to implement some heavy duty mathematical processing which is accessible from an excel front end. As I understand it my options a 1. Pure VBA functions (very slow but compatible with all versions of excel from excel 97 upwards) 2. Pure C/C++ dll with wrapper functions in VBA (much faster but still relatively slow for large worksheets with many cell formulae - again available in all versions of excel 97 upwards) 3. A COM dll with wrapper functions in VBA (same as 2 above except allows use in other projects such as a VB project - eughhh - I know - I don;t see that as a plus either) 4. A COM dll which can be seen straight from worksheet level (I think excel 2002 supports this? - need guidance on this though...) 5. Good old fashioned xlls in pure C/C++ I've sort of discounted options 1 and 2. What I'm really left with is a core set of DLLs with an interface of an XLL or a COM DLL. What I really want to know is do people find COM slower in terms of real speed - especially if anyone has experimented with option 4 compared to 5. And are Xlls deprecated in favour of COM? (My gut preference is XLLs - I just don't trust COM for speed; Xll coding is a bit dirty but not in a way that seems to kill speed) (and btw has anyone had problems with compiling their Xlls with Visual Studio .NET 2003 and its treatment of string literals?) Any suggestions/guidelines/criticism would be much appreciated. Thanks in advance, M. |
raw speed for excel worksheet functions? - xll -or com addin?
Hi John,
And are Xlls deprecated in favour of COM? I think the folks at MS would like them to be, but nobody else is paying attention. (My gut preference is XLLs - I just don't trust COM for speed; Xll coding is a bit dirty but not in a way that seems to kill speed) I personally haven't done any performance comparisons between XLLs and Automation Add-ins (which are only supported in Excel 2002 and higher), but Automation Add-ins are at best equal to XLLs and very likely slower due to the overhead imposed by COM. Whether the difference is enough to matter for any given application would require testing, which I've never done because everything I write has to be backward compatible with Excel 2000 and/or Excel 97. (and btw has anyone had problems with compiling their Xlls with Visual Studio .NET 2003 and its treatment of string literals?) The method for byte-counting strings in the function table that was demonstrated in the Excel SDK was never valid, It slipped by because a quirk in the MS VC compiler prior to 2003 placed string literals in writeable memory. This is no longer the case in 2003, so your function table simply needs to be a 3D char array, e.g.: #define NUM_FUNCTIONS 5 #define NUM_REGISTER_ARGS 11 #define MAX_LENGTH 255 static char FunctionTable[NUM_FUNCTIONS][NUM_REGISTER_ARGS][MAX_LENGTH] = { // function table initializer. }; -- 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 * "John Doe" wrote in message om... Hi, A question for those of you who have experimented with this. I want to implement some heavy duty mathematical processing which is accessible from an excel front end. As I understand it my options a 1. Pure VBA functions (very slow but compatible with all versions of excel from excel 97 upwards) 2. Pure C/C++ dll with wrapper functions in VBA (much faster but still relatively slow for large worksheets with many cell formulae - again available in all versions of excel 97 upwards) 3. A COM dll with wrapper functions in VBA (same as 2 above except allows use in other projects such as a VB project - eughhh - I know - I don;t see that as a plus either) 4. A COM dll which can be seen straight from worksheet level (I think excel 2002 supports this? - need guidance on this though...) 5. Good old fashioned xlls in pure C/C++ I've sort of discounted options 1 and 2. What I'm really left with is a core set of DLLs with an interface of an XLL or a COM DLL. What I really want to know is do people find COM slower in terms of real speed - especially if anyone has experimented with option 4 compared to 5. And are Xlls deprecated in favour of COM? (My gut preference is XLLs - I just don't trust COM for speed; Xll coding is a bit dirty but not in a way that seems to kill speed) (and btw has anyone had problems with compiling their Xlls with Visual Studio .NET 2003 and its treatment of string literals?) Any suggestions/guidelines/criticism would be much appreciated. Thanks in advance, M. |
All times are GMT +1. The time now is 04:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com