Solved

VBA Slower in 2010 than 2007

Posted on 2011-09-14
5
609 Views
Last Modified: 2012-05-12
I wrote a complex VBA script in Excel 2007. It usually took about 20-30 minutes to do the run. When I would check the task manager it would be using one of the cores at 100%, in the case of 4 cores it would use 25% of the total CPU.

Now I have upgraded to Excel 2010. When I run the same script it takes 1-2 hours and runs all cores at a 100%, I even tried this on a 12 core machine and it used 100% of all 12 cores. My understanding is that VBA is not multi-threaded, nor is my code in any way written for parallel processing. So what is going on? Why is the code executing slower and using more CPU?

Thanks.
0
Comment
Question by:sypder
  • 2
  • 2
5 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 36539813
Can you post the code, or perhaps an idea of what it does?

Have you changed any options or have any options not been carried over from the previous version?

eg no of processors to use for multi-thread calculations
0
 
LVL 3

Author Comment

by:sypder
ID: 36540019
The code is the result of a year of work, so it is massive. It is basically trying to optimize a complex packing problem.

So in 2007 I know that the code always ran on just one processor, so now I am trying to disable the multi-threading and see if I get similar performance. I wonder if perhaps multi-threading was disabled by default in my 2007 version and I just never bothered to notice.
0
 
LVL 33

Expert Comment

by:Norie
ID: 36540039
Well I had a similar problem but not to this extent and I changed the multi-thread calculation option to use all processors.

Seemed to speed things up but not much, though what I was doing wasn't really calculation intensive.
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 36540371
If you are using Solver, the engine changed between Excel 2007 and 2010. The new Solver engine is supposed to be faster, however.

If you are not using Solver in a complex optimization problem, it might be worth your effort.

FWIW, Microsoft is encouraging Excel users with complex problems to consider the use of computing clusters. You can do so using Excel and VBA. http://blogs.office.com/b/microsoft-excel/archive/2010/02/04/excel-2010-and-high-performance-computing.aspx
0
 
LVL 3

Author Comment

by:sypder
ID: 36558178
No, I was not using Solver. Because of the complexity of the problem, I built a custom bi-section method algorithm.

So here is what I did:

- Turned off multi-threading. This did not increase the run time, but now the script uses only 1 core instead of 12 (leaving the other 11 to do something else).
- Realized the Excel 2010 did not always preserve the "calculate manually" setting, so I now verify in my VBA that it is set to manual calculation

Seems to now run at the same speed as 2007 and use the same resources.

Thanks for your help!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now