Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VBA Slower in 2010 than 2007

Posted on 2011-09-14
5
Medium Priority
?
665 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 35

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 35

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 2000 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

885 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