Excel slow when not in focus

[Sam Hamels]

Hi everyone,


I have a large Excel file with a long macro that needs to run.
I am *not* looking for ways to optimize my Excel file or the macro code in order to increase its performance.
Performance (duration of the macro) is perfectly acceptable as long my instance of Excel remains 'in focus'.
As soon as I have the Windows focus on anything else (e.g. the calculator app), Excel, which is running the macro, slows down immensely.
I cannot simply have Excel 'remain in focus' the whole time while the macro is running, because I need to use my computer for other (non-CPU intensive) stuff in the meantime.

I am looking for a way to have Excel remain its regular speed, regardless of it being the Windows app that is in focus or not.

I have searched extensively on Google to find a solution, but haven't found any.
All I can find are some posts from 5 or more years ago that point to the same problem, with no real solution being given in the replies.

I'm using Excel 2019 64 bit.
Windows 10 (updated to the latest version).

Going into Windows Task Manager and setting the priority to 'High' or even 'Realtime' does not seem to make any difference. Excel still slows down immensely when not in focus.

There is also a change in Windows advanced system settings that is sometimes suggested elsewhere to solve this problem, but that also doesn't seem to make any difference in my case. (Settings -> Advanced System Settings -> Advanced tab -> Performance -> Settings -> Advanced tab -> Adjust for best performance of -> set to "Background Services" instead of "Programs").

Still desperately looking for a solution to this problem...


Cheers,
Sam

 

[Joe4]

I have been watching this thread, and haven't seen any replies, so I have a few comments.

First, I don't know if there is any way to speed up Excel when running in the background. It may hust be the way that computers works, and how resources are allocated. If the other things you are doing take up a large amount of resources, then that would certainly have a negative impact on performance.

I have a large Excel file with a long macro that needs to run.
I am *not* looking for ways to optimize my Excel file or the macro code in order to increase its performance.
Performance (duration of the macro) is perfectly acceptable as long my instance of Excel remains 'in focus'.

Maybe you should be looking at optimizing performance. If it takes so long to run, that you want to run it in the background, it may not be very efficient to start with. Anything you can do to speed up performance will only help. Why would you not want to try to do that?

 

[Sam Hamels]

Hi Joe,


Thanks for the reply.

I am not CPU-constrained. One instance of Excel running my macro at full speed (i.e. when it is 'in focus') takes up about 15% of my CPU.
I do not run any other programs that take up a significant amount of CPU while my Excel file is open and running the macro.
Whenever that Excel instance becomes 'out of focus' by clicking on another open program, Excel's CPU usage drops down, bringing the progress of the macro to a crawl.

The reason I'm not interested in optimizing the macro itself, is because the normal Excel code cannot be optimized by much anymore.
The reason it takes long is because I'm using an external add-in called 'OpenSolver', which is called upon thousands of times as part of my macro.
It is the work that this external add-in is doing that is taking a long time (a few seconds each time it is called, as long as Excel is 'in focus', but a few minutes each time whenever Excel is 'not in focus').
It is not possible for me to go into the code of the add-in to optimize its performance.

Edit: spelling

 

[Joe4]

In researching this, it looks like you have already tried most of the suggested things to give Excel a higher priority. If these do not help, the answer is really quite simple. Run the process on other dedicated computer where you can leave Excel open and as the program with the focus while it runs. You might even be able to schedule it to run it from a server.

Barring that, based on what you have mentioned (trying the other things and not being able to optimize), I really do not see any other options.

 

[Sam Hamels]

Hi Joe,


I'd like to try your suggestion (simply running Excel separately on another computer, and have it remain in focus the whole time).
However, I need some VBA code to repeatedly pull Excel in the foreground (bring it into focus), just to be sure. I've seen that Excel can sometimes still go out of focus (seemingly by itself).

I've tried using things like:

AppActivate Application.caption

and

AppActivate ThisWorkbook.Application


But those don't seem to work when Excel is already out of focus.
The Excel block in my taskbar just start flickering with an orange color (asking for my attention), but focus is not actually brought to Excel.
I guess this is Windows preventing Excel from "stealing focus".
Any ideas on how to prevent this (i.e. allow​ Excel to steal focus)?

 

[John_w]

This procedure gives the Excel window focus. You could call it periodically with Application.OnTime.

Option Explicit

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    Private Declare PtrSafe Function SetForegroundWindow Lib "user32.dll" (ByVal hwnd As LongPtr) As Long
    Private Declare PtrSafe Function GetForegroundWindow Lib "user32.dll" () As LongPtr
    Private Declare PtrSafe Function GetWindowThreadProcessId Lib "user32.dll" (ByVal hwnd As LongPtr, ByRef lpdwProcessId As LongPtr) As Long
    Private Declare PtrSafe Function AttachThreadInput Lib "user32" (ByVal idThreadAttach As Long, ByVal idThreadAttachTo As Long, ByVal fAttach As Long) As Long
    Private Declare PtrSafe Function GetCurrentThreadId Lib "kernel32" () As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Private Declare Function SetForegroundWindow Lib "user32.dll" (ByVal hwnd As Long) As Long
    Private Declare Function GetForegroundWindow Lib "user32.dll" () As Long
    Private Declare Function GetWindowThreadProcessId Lib "user32.dll" (ByVal hwnd As Long, ByRef lpdwProcessId As Long) As Long
    Private Declare Function AttachThreadInput Lib "user32" (ByVal idThreadAttach As Long, ByVal idThreadAttachTo As Long, ByVal fAttach As Long) As Long
    Private Declare Function GetCurrentThreadId Lib "kernel32" () As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If


Public Sub Focus_Excel()
    
    AttachThreadInput GetWindowThreadProcessId(GetForegroundWindow(), vbNull), GetCurrentThreadId(), True
    SetForegroundWindow Application.hwnd
    AttachThreadInput GetWindowThreadProcessId(GetForegroundWindow(), vbNull), GetCurrentThreadId(), False
    
End Sub

 

[Sam Hamels]

John, you're a life save!
Thank you so much.
This works perfectly.

Consider my problem solved.

 

[Sam Hamels]

John,

Do you have any other thoughts on how to prevent Excel from slowing down when it is not in focus in the first place?
Solving that issue would be even better...

 

[John_w]

No Sam, I can't think of anything and have never seen this problem.

Have you read https://social.msdn.microsoft.com/F...of-running-excel-in-background?forum=exceldev and the provided links?

 

[Sam Hamels]

Yeah, I tried everything you can find via Google.
It's so weird that this is apparently an unsolvable issue.
I wish Excel just had an option under settings that said "don't throttle Excel performance when Excel is not in focus"