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

 

[khotso]

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

 

[khotso]

I get a compile error: expected expression when I try to paste this code in my 2010 excel file. I tride changing the VBA7 to "Win64" but nothing changed, I still get the error. Please assist. Also, how would I call the function in my code?

 

[GWteB]

Hi khotso, Welcome to MrExcel.

Some BulletinBoard Code has crept in (there has been a conversion since then). It should look like ...

Option Explicit

#If 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
#Else
    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
#End If


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

 

[khotso]

Hi khotso, Welcome to MrExcel.

Some BulletinBoard Code has crept in (there has been a conversion since then). It should look like ...

Option Explicit

#If 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
#Else
    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
#End If


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

Thanks \GWteB. it solves the error. In my code how/where do I call the Focus_Excel Sub to solve the slowing down issue?

 

[khotso]

Thanks \GWteB. it solves the error. In my code how/where do I call the Focus_Excel Sub to solve the slowing down issue? Do I call this sub in my main module which runs the whole code?

 

[GWteB]

You are welcome! You can invoke the code from wherever you want.

 

[khotso]

You are welcome! You can invoke the code from wherever you want.

at the beginning of the code or just anywhere?

 

[GWteB]

Just anywhere, and as much times you're comfortable with.

 

[khotso]

i invoked it like this. it sits in module 2. is it ok?

 

[khotso]

Just anywhere, and as much times you're comfortable with.

Thanks GWTeB . I now understand