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

 

[GWteB]

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

Looks akay to me.

 

[GWteB]

Thanks GWTeB . I now understand

Excellent, well done!

 

[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

How and where would I call this sub-routine in my entire code? A short example can help

 

[GWteB]

How and where would I call this sub-routine in my entire code? A short example can help

Best way would probably an automagically invocation with a certain time interval, let's say 5 seconds.

Before your time consuming code will be executed you're supposed to schedule this interval. In fact you're just scheduling one time on wich the Focus_Excel procedure will be invoked. Note that the latter needs a slight modification to achieve the interval, by setting a new schedule time. After your code in question has been executed, you can cancel the schedule. To make sure the schedule doesn't accidentally "stays alive" it's also cancelled the moment the workbook is closed.

Usage example:

Public Sub UsageExample()

    FocusExcelSchedule  ' with current code the Focus_Excel procedure will be invoked every 5 seconds
    '
    ' run some time consuming stuff
    '
    FocusExcelCancel    ' cancel current schedule

End Sub

This goes in the ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    FocusExcelCancel    ' cancel current schedule

End Sub

This goes in a standard module:

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

Private Type TLocals
    FocusExcelScheduledTime As Date        ' <<< storage for scheduled date & time
End Type
Private this As TLocals

Public Sub Focus_Excel()

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

Public Sub FocusExcelSchedule()
    this.FocusExcelScheduledTime = Now + TimeValue("00:00:05")     ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<< change length of time to suit
    Application.OnTime EarliestTime:=this.FocusExcelScheduledTime, Procedure:="Focus_Excel"
End Sub

Public Sub FocusExcelCancel()
    On Error Resume Next
    Application.OnTime EarliestTime:=this.FocusExcelScheduledTime, Procedure:="Focus_Excel", Schedule:=False
End Sub