SERVING THE QUANTITATIVE FINANCE COMMUNITY

 
User avatar
gc
Topic Author
Posts: 673
Joined: September 21st, 2002, 10:08 pm

Excel 2007: Slow VBA

January 26th, 2011, 7:01 pm

I am starting to test our sheets and addins in Excel 2007 (from Excel 2003) and I am finding Excel 2007 at times more than an order of magnitude slower than its predecessor, and sometimes on very trivial operations. An example is attached. In the sheet I look at a range from VBA (e.g. sheet1.range("A1").value = 234). Repeating the operation 10,000 times in Excel 2003 takes about 0.2seconds; in Excel 2007 with same spec machine takes 6seconds, a staggering 30 times slower!I know that it is more efficient to create an array and write it in a single operation; I just wonder if there is some hidden setting of Excel that I missed or if it is really something I've to live with.Another question: if I open another spreadsheet within the same Excel session; even if my VBA macro doesn't do anything to it, it slows down even further! For example, if I open both my test sheet and one downloaded from:Value of assumption.xlsthen the same task that took in Excel 2003 0.2seconds, in Excel 2007 6 seconds, now takes: 16 seconds. This worries me: I have no control on how many sheets a trader may open in the same session (nor I think I should worry), but if something as random as what else happens in the same session of Excel can have huge effects in performance, I am worried.Do you notice anything similar moving from Excel 2003 to Excel 2007? Any comments?thanksgc
Attachments
excel2007 benchmark.zip
(77.68 KiB) Downloaded 15 times
 
User avatar
semanticum
Posts: 100
Joined: March 28th, 2007, 8:01 am

Excel 2007: Slow VBA

January 27th, 2011, 8:00 am

Hi gcThe results on my machine (win xp, dual core, Excel 2007):ScreenUpdate = 0.641NoScreenUpdate = 0.625Go to 1. Office Button top left2. Excel Options3. Advanced4. Scroll down to Formulas5. Make sure the option "Enable multi-threaded calculation" is checkedDominik
 
User avatar
gc
Topic Author
Posts: 673
Joined: September 21st, 2002, 10:08 pm

Excel 2007: Slow VBA

January 27th, 2011, 9:26 am

Thanks Dominik,Your stats are very important for me. I tried enabling and disabling multi-threaded calculation, but in my case the difference is between 6sec and 6.5sec... The fact that you report 0.641/0.625 means that there is something on a different level going on for my machine and maybe the way Excel 2007 is packaged in the firm where I work.If someone sees this thread and runs Excel 2007, can post their results? (just to see if there is much std dev).BTW: my PC is: Intel Core 2 Duo CPU E8400 @ 3.00GHZ with 3.5GB RAMgc
 
User avatar
kimosabe
Posts: 416
Joined: November 25th, 2003, 12:24 pm

Excel 2007: Slow VBA

January 27th, 2011, 12:07 pm

Have you tried this with Excel 2010? That tends to be quite a bit faster than 2007. If you want to get rid of VBA you can use http://xll.codeplex.com to write xll add-ins. They have much better performance and are more portable.
 
User avatar
gc
Topic Author
Posts: 673
Joined: September 21st, 2002, 10:08 pm

Excel 2007: Slow VBA

January 27th, 2011, 12:22 pm

Thanks Kimosabe,Excel 2010 will probably be an option for me around 2020... the company I work for is very conservative in upgrading software and generally waits a few years until the first few service packs are released, to start rolling out products.XLL is certainly a good suggestion (we have loads of compiled libraries) but there are tasks that can only be done in VBA...gc
 
User avatar
FastExcel
Posts: 50
Joined: December 2nd, 2003, 8:10 am

Excel 2007: Slow VBA

January 27th, 2011, 1:43 pm

Excel 2007 VBA has a higher overhead per VBA/Excel data transfer call than previous versions, so the advantage of transferring data in large blocks using arrays is even greater with XL 2007.Make sure you are using Excel 2007 SP2 (SP2 contains a VBA speedup fix).My timings for your benchmark (screenupdating off) are:XL 2003 0.359XL 2007 SP2 0.499XL 2010-32 SP1 0.452See http://www.decisionmodels.com/VersionCompare.htm for more benchmark timings for the Excel versions.
 
User avatar
gc
Topic Author
Posts: 673
Joined: September 21st, 2002, 10:08 pm

Excel 2007: Slow VBA

January 27th, 2011, 2:08 pm

Thanks FastExcel for the second quote....I also managed to find a test PC and with a different build from our standard desktop, and on that one I have similar timings as yours and semanticum.I'm trying to chase the IT group now to see if they spot the difference. "About Microsoft Office" says that the fast one is: Microsoft Office Excel 2007 (12.0.6529.5000) SP2 MSO (12.0.6529.5000)while the slow one:Microsoft Office Excel 2007 (12.0.6425.1000) SP2 MSO (12.0.6529.5000)Both SP2 and with just a minor number difference.... I am a bit happier that it's not Excel 2007 on itself to be this slow, still finding what determines the different behaviour between the two will be interesting,gc
 
User avatar
kimosabe
Posts: 416
Joined: November 25th, 2003, 12:24 pm

Excel 2007: Slow VBA

January 27th, 2011, 3:56 pm

In the year 2525...@gc Just curious, but what do you think VBA can do that XLL's can't?
 
User avatar
gc
Topic Author
Posts: 673
Joined: September 21st, 2002, 10:08 pm

Excel 2007: Slow VBA

January 27th, 2011, 4:04 pm

Quote@gc Just curious, but what do you think VBA can do that XLL's can't? Fair question... In my case I have a toolbar and a button on this toolbar extracts some data and creates an Excel chart. (The inputs, positioning and chart formatting are retrieved from the sheet, while the underlying data is obtained through a number of calls to a .DLL that talks to our servers and databases) Maybe I am wrong, I haven't investigated on creating toolsbars and charts from XLLs. Do you think it's possible? If so I'll explore the options.thanksgc
Last edited by gc on January 26th, 2011, 11:00 pm, edited 1 time in total.
 
User avatar
Hansi
Posts: 3300
Joined: January 25th, 2010, 11:47 am

Excel 2007: Slow VBA

January 27th, 2011, 4:19 pm

QuoteOriginally posted by: gcMaybe I am wrong, I haven't investigated on creating toolsbars and charts from XLLs. Do you think it's possible? If so I'll explore the options.thanksgcWhen you say toolbars do you mean Ribbon items for 2007 etc? If so yes it's possible. Easiest way is with add-in express but it can be done manually as well.You'll have access to the same charting functions in an XLL as in VBA code.
Last edited by Hansi on January 26th, 2011, 11:00 pm, edited 1 time in total.
 
User avatar
gc
Topic Author
Posts: 673
Joined: September 21st, 2002, 10:08 pm

Excel 2007: Slow VBA

January 27th, 2011, 4:29 pm

QuoteOriginally posted by: HansiQuoteOriginally posted by: gcMaybe I am wrong, I haven't investigated on creating toolsbars and charts from XLLs. Do you think it's possible? If so I'll explore the options.thanksgcWhen you say toolbars do you mean Ribbon items for 2007 etc? If so yes it's possible. Easiest way is with add-in express but it can be done manually as well.You'll have access to the same charting functions in an XLL as in VBA code.It's good to be proven wrong! This is interesting and I'll investigate this,thanksgc
 
User avatar
kimosabe
Posts: 416
Joined: November 25th, 2003, 12:24 pm

Excel 2007: Slow VBA

January 27th, 2011, 7:19 pm

Just to be crystal clear, here is a short summary of my undestanding of the available tools:The XLL SDK only exposes the old Excel4 macro commands and functions. It is kind of clunky, but you can do a lot. It is possible, e.g., to create pre Excel 2007 toolbars, but putting buttons in the toolbar involves using the clipboard. Yuck.VBA is much easier to use and does indeed have more functionality.For Excel 2007 and later, the SDK has no support for the new ribbon interface. Menus and toolbars will show up in the Add-Ins tab, but with a distinctly ghetto look. If you want s snazzy new ribbon interface to your xll, see. Andrew Whitechapel's blog or cough up the dosh for Add-in Express.
 
User avatar
FastExcel
Posts: 50
Joined: December 2nd, 2003, 8:10 am

Excel 2007: Slow VBA

January 28th, 2011, 3:02 pm

And make sure you have disabled the Google Office Com addin and toolbar.
 
User avatar
gc
Topic Author
Posts: 673
Joined: September 21st, 2002, 10:08 pm

Excel 2007: Slow VBA

February 5th, 2011, 8:41 pm

Thanks all for your comments and suggestions. At the end the problem was due to a hotfix used in the version of Excel that is deployed in the package distributed in my company.I got the latest hotfixes and Excel 2007 is performing decently now,gc
ABOUT WILMOTT

PW by JB

Wilmott.com has been "Serving the Quantitative Finance Community" since 2001. Continued...


Twitter LinkedIn Instagram

JOBS BOARD

JOBS BOARD

Looking for a quant job, risk, algo trading,...? Browse jobs here...


GZIP: On