Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Is there a way to limit how much CPU usage excel has access to when running? I have a VBA script that calculates a bunch of giant array formulas. The entire calculation takes maybe 20 minutes using 100% of my CPU but I can't use my machine during the time and would rather have it run in the background at like 50% CPU usage so I can continue to do other things. Any suggestions?

My OS is Windows 7 Enterprise 64-bit and the Excel version is 2007-32 bit

share|improve this question
1  
Windows? Which version? – DavidPostill Jan 12 at 20:31
    
Windows 7. I'll add it to the post – learningAsIGo Jan 12 at 20:34
8  
Get a multicore CPU? If you already have one (who doesn't?) and Excel is using all the cores (which I didn't know Excel could do), then set its affinity in Task Manager. – user20574 Jan 12 at 22:06
2  
This doesn't answer the question, but make sure that you turn off window updates before you start calculating and updating thousands of cells. If you have any charts then turn them off too. VBA is slow, but when it's really slow then it's usually one or both of those to blame. – Coxy Jan 13 at 3:33
1  
@cat : No, a 100% CPU usage in Windows Task Manager means 100% across all logical processors. If only one core filled it will be either 25% (on a common dual core with hyper threading) or ~36% (turbo boosting). – Martheen Cahya Paulo Jan 15 at 1:31
up vote 54 down vote accepted

If a VBA function is called from several formulas or if your script generates or forces the recalculation of several formulas, then this should definitely make use of the multi-threaded calculation feature of Excel. Respectively, this would either run multiple instances of your VBA function for each formula, or recalculate multiple cells simultaneously while your vba script is running on a single thread.

You can limit the number of threads used by Excel to recalculate formulas in Excel Options... Advanced Tab... Formulas section.

enter image description here

share|improve this answer
    
The VBA sub isn't the issue, it's the excel array formula that the VBA code generates. – learningAsIGo Jan 13 at 0:27
    
@learningAsIGo Right, I simplified my answer. Let us know whether setting the number of threads to 1 helps. – mtone Jan 13 at 0:30
1  
This seems to have done the trick. I set it to 3 cores and it maxes at 75% of CPU leaving me plenty of room to do other things. Thanks! – learningAsIGo Jan 13 at 1:00

Instead of lowering the priority, try changing the affinity in the Task Manager. If you have more than 1 CPU, you can limit the number of CPUs Excel will use. This will free the other CPUs to work on other things.

Right click Excel in the Processes tab and select Set Affinity. Choose CPU(s) where you want Excel to run.

share|improve this answer
3  
Note that for affinity, Windows considers each core a CPU as well. (So this would work if you have a dual or quad core.) – jpmc26 Jan 12 at 23:33
1  
Well if talking specifically about cores, why not HyperThreading threads? They are also distinct logical CPUs. I.e. a quad-core CPU with HT will have 8 logical CPUs. – Ruslan Jan 13 at 10:31
    
I do this all the time when running HandBrake and just want to browse the internet for an hour without pausing or stopping the transcoding process. I think it would be worthwhile to mention that after some time the cores do get released back to the program and the affinity "resets", at least it happens to me with handbrake. – MonkeyZeus Jan 13 at 17:42
2  
@MonkeyZeus Usually, the affinity is not saved when a process exits. It only applies to the process currently running. It could be that HandBrake was spawning new processes itself or you started new ones. – jpmc26 Jan 14 at 1:40
    
@jpmc26 Good to know! I definitely did not start a new process myself so it must be spawning it on its own. – MonkeyZeus Jan 14 at 4:40

You can try lowering the priority of the excel process, by finding opening the task manager, switching to the "Details" or "Processes" tab (depending on your version of Windows), right clicking on the excel.exe process, and selecting a lower priority. This should give more CPU time to other processes.

share|improve this answer
1  
Doesn't seem to work: I set the priority to very low and it still lags the computer. – learningAsIGo Jan 12 at 20:37
1  
Maybe try increasing the priority of your web browser, or whatever you're trying to do at the same time, as well? I found this discussion on how effective changing priorities is. – Slithy Toves Jan 12 at 20:41
3  
@SlithyToves: That really shouldn't matter. Priority determines which program gets the CPU, if there are multiple candidates. Setting Excel to very low means it only gets a CPU slice when all other programs are done. However, when Excel does get a CPU slice, it won't give up that slice. It will just lose the competition for the next CPU time slice. – MSalters Jan 13 at 7:34

There are Sleep and Wait functions available in VBA or via a declaration. However, the "overly simplistic rule of thumb" is to never use Sleep(). (google '"never use sleep()" programming')

Doc page for Application.Wait ( https://msdn.microsoft.com/en-us/library/office/ff822851.aspx ). Note that Sleep and Wait will cause Excel to become unresponsive for the duration you specify and this can cause time-slice "train wrecks."

If your calculation involves a loop of some sort, then one way to handle this for your specific purpose (forfeit calculation time for CPU availability) is to make a special wait function of your own that, for example loops DoEvents() for 1 second and then returns.

DoEvents basically tells your code/interpreter to give up time for the OS etc. It will definitely cause your code to take longer. It may also allow you to edit the worksheet while the calculation is going on, so your milage may vary. Test.

See, for instance, http://stackoverflow.com/questions/469347/is-there-an-equivalent-to-thread-sleep-in-vba

share|improve this answer

A similiar questins was asked a few years ago that has a solution: Are there solutions that can limit the CPU usage of a process?

Process Tamer works for Windows 7. http://www.donationcoder.com/Software/Mouser/proctamer/

share|improve this answer

I had the same issue when my laptop had 4GB of memory. As soon as I got it upgraded to 16GB, the problem stopped. Just another possible solution.

share|improve this answer

Add the following 2 lines somewhere near the start of your macro:

'Turn off screen updating

 Application.ScreenUpdating = False

And these 2 lines near the end:

'Turn screen updating back on

 Application.ScreenUpdating = True

It will then have less work to do while you do something else.

share|improve this answer

If you are using a 32bi office in a 64bit OS you should use this app: http://www.ntcore.com/4gb_patch.php

This app will enable excel or any other office 32bit to use mo

share|improve this answer
    
The question is about limiting CPU, your answer is about activating AWE on apps which probably won't even take advantage of AWE. – Martheen Cahya Paulo Jan 15 at 1:38

Use OpenOffice or LibreOffice: its scripting power is much more effective than the MS one, and you can literally have a "throttle" in your computations.

EDIT: Why downvote? take a look at the manual and see for yourself. You can convert your calculations to Java and be just fine with all the throttling power of the JVM, for example.

share|improve this answer
3  
I didn't downvote, but I'm assuming it is because you proposed a workaround instead of a solution to the very direct question asked in the OP: "Is there a way to limit how much CPU usage excel has access to when running?" – Monkpit Jan 13 at 20:08
    
@Monkpit my story with OpenOffice has started from a similar problem, portation took roughly a week for all the docs we had, but after that I've made an opinion, that I'll never use MS Office again. – Alexey Vesnin Jan 13 at 20:16
    
OpenOffice is profoundly superior to Office for the reason that 99% of people will use Office over Libre, so developers (probably like this guy). That's why I downvoted. – Thomas Shera Jan 13 at 20:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.