Solved

VBA Slower in 2010 than 2007

Posted on 2011-09-14
5
629 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
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…

860 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