33

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

11
  • 1
    Windows? Which version?
    – DavidPostill
    Jan 12, 2016 at 20:31
  • Windows 7. I'll add it to the post Jan 12, 2016 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.
    – user253751
    Jan 12, 2016 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, 2016 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
    Jan 15, 2016 at 1:31

9 Answers 9

56

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

3
  • The VBA sub isn't the issue, it's the excel array formula that the VBA code generates. Jan 13, 2016 at 0:27
  • @learningAsIGo Right, I simplified my answer. Let us know whether setting the number of threads to 1 helps.
    – mtone
    Jan 13, 2016 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! Jan 13, 2016 at 1:00
29

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.

5
  • 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, 2016 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, 2016 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, 2016 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, 2016 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, 2016 at 4:40
7

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.

3
  • 1
    Doesn't seem to work: I set the priority to very low and it still lags the computer. Jan 12, 2016 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. Jan 12, 2016 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, 2016 at 7:34
3

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, https://stackoverflow.com/questions/469347/is-there-an-equivalent-to-thread-sleep-in-vba

2

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/

0

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.

0

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.

-2

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

1
  • The question is about limiting CPU, your answer is about activating AWE on apps which probably won't even take advantage of AWE.
    – Martheen
    Jan 15, 2016 at 1:38
-5

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.

4
  • 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?" Jan 13, 2016 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. Jan 13, 2016 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. Jan 13, 2016 at 20:44
  • @ThomasShera Well, I can't understand at all - why for such a long years Microsoft guys can not just fix the stuff? No, no new stuff needed, no addons - just make your product actually working... May 2, 2018 at 18:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .