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

DavidPostill
  • 153,128
  • 77
  • 353
  • 394
learningAsIGo
  • 518
  • 2
  • 5
  • 13
  • 1
    Windows? Which version? – DavidPostill Jan 12 '16 at 20:31
  • Windows 7. I'll add it to the post – learningAsIGo Jan 12 '16 at 20:34
  • Probably not what you want to hear, but the easier solution may be to redesign your code so that it is more efficient. If you want to ask that question, there's an audience for it here. – Excellll Jan 12 '16 at 20:44
  • Certainly possible. The bottleneck is 5 array formulas that are run over ~1000 cells each. Array formulas are inherently slow so I don't know if much performance can be gained through an alternative solution but I might post it in question form later – learningAsIGo Jan 12 '16 at 20:51
  • 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 '16 at 22:06
  • I do have a multi core CPU and believe it is using all of the cores since I see a 99-100% CPU usage in the task manager. Will give the core affinity thing a try – learningAsIGo Jan 13 '16 at 00:27
  • 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 '16 at 03:33
  • Have you checked if your system has enough memory? If you have a multicore CPU and the whole system becomes slow, this could be because it it swapping... – Calimo Jan 14 '16 at 09:34
  • @learningAsIGo 100% CPU usage means it's using 100% *of a core*, of which you probably have a grand total of 4 or 8 virtual cores. The fact that this makes Windows unusable is baffling to me; perhaps more evidence that Windows is really a bloated pile of crap. – cat Jan 14 '16 at 20: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 '16 at 01:31
  • If it's *really* slow and you don't need to update in "real time" as you make changes, you could always export the relevant data in some format like CSV, run it through a compiled program (FORTRAN, or what ever language works best) and then import the results back in. Excel isn't the only hammer you can use on this nail. – Joe Jan 18 '16 at 20:52

9 Answers9

55

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

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

B540Glenn
  • 1,105
  • 8
  • 17
  • 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 '16 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 '16 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 '16 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 '16 at 01: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 '16 at 04: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.

Slithy Toves
  • 213
  • 1
  • 2
  • 9
  • 1
    Doesn't seem to work: I set the priority to very low and it still lags the computer. – learningAsIGo Jan 12 '16 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](http://arstechnica.com/civis/viewtopic.php?t=506046). – Slithy Toves Jan 12 '16 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 '16 at 07: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

Community
  • 1
Yorik
  • 4,166
  • 1
  • 11
  • 16
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/

Community
  • 1
Ryan
  • 59
  • 4
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.

RPh_Coder
  • 478
  • 4
  • 4
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.

Vomit IT - Chunky Mess Style
  • 40,038
  • 27
  • 84
  • 117
Loppy
  • 1
  • 1
-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

diasje
  • 37
  • 1
  • 1
  • 2
  • 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 '16 at 01: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.

Andreas Rejbrand
  • 803
  • 2
  • 16
  • 33
Alexey Vesnin
  • 563
  • 5
  • 9
  • 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?" – Kyle Pittman Jan 13 '16 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 '16 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 '16 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... – Alexey Vesnin May 02 '18 at 18:47