VBA Slower in 2010 than 2007

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.
LVL 3
sypderAsked:
Who is Participating?
 
byundtCommented:
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
 
NorieVBA ExpertCommented:
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
 
sypderAuthor Commented:
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
 
NorieVBA ExpertCommented:
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
 
sypderAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.