Why does "SetForeGroundWindow" fail to bring Excel to focus with VBA?

Question

I've read countless articles all stating SetForeGroundWindow API is great for bringing focus back to Excel when VBA functions call other applications. I have tried this code numerous ways and iterations, I've tried other API's, (SetFocus, BringWindowToFront, ShowWindow, FindWindow (to get hWnd), etc.) with no success.

What am I missing here? Everything I can think of screams at me this should work based on my research! (Note - not all functions listed above were included in code snippet - keeping a clean example here.)

Private Declare PtrSafe Function SetForegroundWindow Lib "user32" (ByVal HWnd As LongPtr) As Long
Private Declare PtrSafe Function ShowWindow Lib "user32" (ByVal HWnd As LongPtr, ByVal nCmdShow As Long) As Long
Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal milliseconds As Long)

Public Const SW_SHOW As Integer = 5
Public Const SW_HIDE As Integer = 0
Public Const SW_SHOWNORMAL as Integer =1

' Do some code to open Chrome Browser, or Explorer, etc.

Sub SetExcelFocus()
  Dim returnVal As Integer
  Sleep 150
  returnVal = SetForegroundWindow(Application.HWnd)
  Debug.Print returnVal 'Prints 0!
  If returnVal = 0 Then 'always returned 0!
    DoEvents 'Added in case Excel needed to process it was being tagged/activated again
    ShowWindow Application.HWnd, SW_HIDE 'Accurately hides Excel Window from TaskBar and hides it from GUI!
    Sleep 150    
    SetForegroundWindow (Application.HWnd) 'Does not bring Excel into foreground.
    Sleep 150
    ShowWindow Application.HWnd, SW_SHOW 'Accurately shows Excel Window in TaskBar and brings back to GUI but not to foreground!
    Sleep 150
    SetForegroundWindow (Application.HWnd) 'Does not bring Excel into foreground.
    Sleep 150
    ShowWindow Application.HWnd, SW_SHOWNORMAL 'Supposed to activate the window and show normal window size!
    Sleep 150
    SetForegroundWindow (Application.HWnd) 'Does not bring Excel into foreground.
    Sleep 150
  End If 
End Sub

Answer 1

UPDATE!

So the data below (left in, blockquoted/struckthrough) was actually NOT the culprit of this failure!

I had to add Application.SendKeys "{TAB}" (or Application.SendKeys "{ESC}", etc) - anything that utilizes a special key (Application.SendKeys " " for example does not work).

Additionally, using CreateObject("WScript.Shell") for SendKeys did not produce the appropriate result in this case!

Because Application.SendKeys often turns off the Numlock, I did some digging and found an SO Article with a great function that confirmed Numlock's condition and always turned it back on. (I had to use a modified version of the first linked answer - see my answer posted containing my modifications - original use of this function found did not work. . .)

Updated Focus Routine:

Sub SetExcelFocus
  Dim returnVal as Integer
  Sleep 250
  returnVal = SetForegroundWindow(Application.HWnd)
  Debug.Print returnVal 'Now prints 1 after the first initialization of Applicaiton.SendKeys!
  If returnVal = 0 Then
    DoEvents
    Debug.Print "Could not bring Excel back to front. . ."
    Sleep 250
    Application.SendKeys "{TAB}", False
    Reset_NumLock 'See linked article above for this function
    SetForegroundWindow Application.HWnd
  End If
End Sub

I do not know what the root of the problem is, but I suspect it has to do with my Group Policy not allowing the "Window Manager" to be on the Increase Scheduling Priority Security Policy (Company's policy - cannot change). It is either THAT, OR, it has to do with the Prerequisites not being met for SetForegroundWindow to properly work in Windows 10 (I think along the lines of foreground app suddenly calling itself to front even though another window was present). . .

Perhaps it worked better in Windows 7 or 8? I've seen this referred to (as well as the Prereq's) in various places including here on SO (but haven't found in MSDN). . . If anyone has a link handy for this specific item - feel free to add to this for a more complete answer.

What really solved this for me puzzled me at first, but after pondering this, it makes a little sense.

But why did it WORK?

My limited understanding of this behavior assumes that since the first call to SetForegroundWindow did toggle something within the PC (I see the orange "Updated" status on the taskbar for the Excel instance), that when forcing Excel to issue a specialized key, this forced Windows to update the GUI and finally allow Excel to be at the forefront. What is strange and I still cannot quite make it out - the solution makes the SetForegroundWindow routine work every time it is called after the Application.SendKeys until the workbook is closed and re-opened again.

Leaving this here for posterity:

After much more toying with this, I finally found this article which gave me a different idea (although it didn't spell out the reason this occurs. . .).

You CANNOT use the Sleep Sub just before the call to SetForegroundWindow! Something about Sleep causes it to fail 100% of the time. Removed all the Sleep commands and added a single Application.Wait as shown in the linked article and now this works flawlessly everytime. As I was writing this answer, SO happened to have another article within the "Similar Questions" box which has this answer. . . I believe it alludes to the issue at heart. It appears Sleep causes Excel to be "Non-Responsive" and thus most-likely prevents the application from being hooked again appropriately? At any rate - this works! If anyone can point to a definitive article around the pit-falls of Sleep - I would be grateful.

Sub SetExcelFocus   
  Dim returnVal as Integer  
  Application.Wait (Now() + TimeValue("00:00:01"))   
  returnVal = SetForegroundWindow(Application.HWnd)   
  Debug.Print returnVal 'Now prints 1!
  If returnVal = 0 Then
    DoEvents
    ShowWindow Application.HWnd, SW_HIDE
    SetForegroundWindow (Application.HWnd)
    ShowWindow Application.HWnd, SW_SHOW
    SetForegroundWindow (Application.HWnd)
    ShowWindow Application.HWnd, SW_SHOWNORMAL
    SetForegroundWindow (Application.HWnd)
  End If
End Sub