Solved

VBA Slower in 2010 than 2007

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

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

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,…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

759 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