Calculation Optimization via Multithreading

**Telefonica** · 04-08-2011, 12:22 AM

Hi all, first post here. I recently posted this question in another forum but haven't gotten any responses. I'm running 2007.

I have a lot of formulas that occupy E6:YO6052

I calculate them every so often as part of a macro and I'm trying to optimize this calculation step.

The order of calculation doesn't matter (there are no dependents within the range), so right now I use RangeCalculateRowMajorOrder. The idea being that Excel won't check for dependents, etc.

I'm wondering if there's a better way of doing this, maybe something that's multi-threaded? For example, I could have one processor calculate E6:YO3,023 and my other processor calculate E3,024:YO6,052 (i have a core2duo)? Is there a way to do this? Any other ideas to help speed up the calculation?

Thanks for the help,
-Adam

**Leith Ross** · 04-08-2011, 12:32 AM

Hello Telefonica,

Welcome to the Forum!

Excel and VBA are both STA (Single Thread Apartment) models. While Windows does support MTA (Multi Threaded Apartments), the application alone can create them. This aspect of COM is not exposed nor accessible through VBA.

**MarvinP** · 04-08-2011, 12:39 AM

Hi Telefonica and welcome to the forum.

Turn your Calculation to Manual first of all. Then in VBA code try to calculate your range, only when needed with a command of

Please Login or Register  to view this content.

See if this helps.

I actually think the OS determines if/when/how to multi thread processors.

**Telefonica** · 04-08-2011, 12:53 AM

Thanks for the quick answers.

Leith - I thought that Excel had multi-thread capabilities. See the following link:

http://msdn.microsoft.com/en-us/library/bb687899.aspx

Excel tries to identify parts of the calculation chain that can be recalculated concurrently on different threads

My range should fall under the criteria and I'm trying to figure out if I can manually tell it to calculate the cells on multiple threads.

Thanks Marvin - I've def. got the VBA set to manual calculate. Definitely helps speed things up.

**Telefonica** · 04-08-2011, 12:57 AM

Hmmmm... this link is interesting.

http://msdn.microsoft.com/en-us/library/bb687868.aspx

Maybe I can create a function to calculate my range of formulas, define that function as thread safe and then call it within my macro.

**Leith Ross** · 04-08-2011, 01:00 AM

Hello Telefonica,

I stand corrected. Thanks for the link. I was unaware that change was made in Excel 2007.

**abousetta** · 04-10-2011, 02:12 AM

Microsoft says that this makes 2010 run faster, but does anyone have any experience using the multithreading properties in the new Excel? I found it was turned on by default on my 32-bit version. I haven't tested running times for macros on the older 2007 version compared to the upgrade but I am generally happier with 2010 over 2007.

abousetta

**MarvinP** · 04-10-2011, 10:55 AM

Hi,

I just remembered a story about fast computing you may like. Before the Apple II and the IBM PC, I had another geek chanllenge me to writing fast code that did something simple. We had a timer function on our HP 95 computers so we could test it. I won the race by simply defining my variables in a better order. I Dimed the most used variable first because I knew the computer needed to go look for the matching variable in the code. It would start at the front of the variables list and see if it matched. If not it would go to the next variable an see if it matched, etc. I dimmed my counter variable, the one that was used most often, first. Better understanding of code and how the computer deals with it is also important.

In Excel understanding Volatility and which common functions are slower may be a better investment in time. See DonkeyOte's signature line, which points to http://www.decisionmodels.com/calcsecretsi.htm

**abousetta** · 04-10-2011, 11:07 AM

Hi,

I agree with you 100% and this is such a great reminder of the intricate balance between the different components of vba and code efficiency. Maybe I was jumping the gun in my last post, but it would have been interesting to see if the multi-threading function in excel 2010 is something for the novice coder to user or something more for people with extensive experience writing code.

abousetta

Disclaimer: None of my doctoral degrees are in anything related to computer science

, but have been interested in computers ever since we had a T5 and 5 1/4 inch floppy disks and only recently got into learning vba.