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
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.
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.
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.
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
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/
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.
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.
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
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.
