5

I'm trying to develop an 'auto run' macro to determine if the VBE is open (not necessarily the window of focus, just open). If this is TRUE then ... take some action.

If this macro is connected to a CommandButton it works but I can't get it to function anywhere in the ThisWorkbook:

Sub CloseVBE() 'use the MainWindow Property which represents ' the main window of the Visual Basic Editor - open the code window in VBE, ' but not the Project Explorer if it was closed previously: If Application.VBE.MainWindow.Visible = True Then MsgBox "" 'close VBE window: Application.VBE.MainWindow.Visible = False End If End Sub

I was given the following FUNCTION to do the same but I can't get it to work either:

Option Explicit Private Declare Function FindWindow Lib "User32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long Private Declare Function GetWindowText Lib "User32" Alias "GetWindowTextA" (ByVal hWnd As Long, ByVal lpString As String, ByVal cch As Long) As Long Private Declare Function GetWindowTextLength Lib "User32" Alias "GetWindowTextLengthA" (ByVal hWnd As Long) As Long Private Declare Function GetWindow Lib "User32" (ByVal hWnd As Long, ByVal wCmd As Long) As Long Private Const GW_HWNDNEXT = 2 Function VBE_IsOpen() As Boolean Const appName As String = "Visual Basic for Applications" Dim stringBuffer As String Dim temphandle As Long VBE_IsOpen = False temphandle = FindWindow(vbNullString, vbNullString) Do While temphandle <> 0 stringBuffer = String(GetWindowTextLength(temphandle) + 1, Chr$(0)) GetWindowText temphandle, stringBuffer, Len(stringBuffer) stringBuffer = Left$(stringBuffer, Len(stringBuffer) - 1) If InStr(1, stringBuffer, appName) > 0 Then VBE_IsOpen = True CloseVBE End If temphandle = GetWindow(temphandle, GW_HWNDNEXT) Loop End Function

1/23/2018 Here is an update to the original question:

I located the following code that performs EXACTLY as I was needing but when closing the workbook, the macro errors out on the line indicated:

Public Sub StopEventHook(lHook As Long) Dim LRet As Long Set lHook = 0'<<<------ When closing workbook, errors out on this line. If lHook = 0 Then Exit Sub LRet = UnhookWinEvent(lHook) Exit Sub End Sub

Here is the entire code, paste this into a regular Module:

Option Explicit Private Const EVENT_SYSTEM_FOREGROUND = &H3& Private Const WINEVENT_OUTOFCONTEXT = 0 Private Declare Function SetWinEventHook Lib "user32.dll" (ByVal eventMin As Long, ByVal eventMax As Long, _ ByVal hmodWinEventProc As Long, ByVal pfnWinEventProc As Long, ByVal idProcess As Long, _ ByVal idThread As Long, ByVal dwFlags As Long) As Long Private Declare Function GetCurrentProcessId Lib "kernel32" () As Long Private Declare Function GetWindowThreadProcessId Lib "user32" (ByVal hWnd As Long, lpdwProcessId As Long) As Long Private pRunningHandles As Collection Public Function StartEventHook() As Long If pRunningHandles Is Nothing Then Set pRunningHandles = New Collection StartEventHook = SetWinEventHook(EVENT_SYSTEM_FOREGROUND, EVENT_SYSTEM_FOREGROUND, 0&, AddressOf WinEventFunc, 0, 0, WINEVENT_OUTOFCONTEXT) pRunningHandles.Add StartEventHook End Function Public Sub StopEventHook(lHook As Long) Dim LRet As Long On Error Resume Next Set lHook = 0 '<<<------ When closing workbook, errors out on this line. If lHook = 0 Then Exit Sub LRet = UnhookWinEvent(lHook) Exit Sub End Sub Public Sub StartHook() StartEventHook End Sub Public Sub StopAllEventHooks() Dim vHook As Variant, lHook As Long For Each vHook In pRunningHandles lHook = vHook StopEventHook lHook Next vHook End Sub Public Function WinEventFunc(ByVal HookHandle As Long, ByVal LEvent As Long, _ ByVal hWnd As Long, ByVal idObject As Long, ByVal idChild As Long, _ ByVal idEventThread As Long, ByVal dwmsEventTime As Long) As Long 'This function is a callback passed to the win32 api 'We CANNOT throw an error or break. Bad things will happen. On Error Resume Next Dim thePID As Long If LEvent = EVENT_SYSTEM_FOREGROUND Then GetWindowThreadProcessId hWnd, thePID If thePID = GetCurrentProcessId Then Application.OnTime Now, "Event_GotFocus" Else Application.OnTime Now, "Event_LostFocus" End If End If On Error GoTo 0 End Function Public Sub Event_GotFocus() Sheet1.[A1] = "Got Focus" End Sub Public Sub Event_LostFocus() Sheet1.[A1] = "Nope" End Sub

Paste this into the ThisWorkbook :

Option Explicit Private Sub Workbook_BeforeClose(Cancel As Boolean) StopAllEventHooks End Sub Private Sub Workbook_Open() StartHook End Sub
Improve this question
5
  • Explain what "errors out" means, including the entire error message. And format your code - highlight the code lines, and press ctrl+k or click the {} button on the edit toolbar. Commented Jan 24, 2018 at 0:00
  • 1
    Just a side note but you could be killing the hook chain by not calling CallNextHookEx() in your WinProc. Commented Jan 24, 2018 at 0:33
  • The error message is : Object required. Please understand my knowledge of working with the API is less than nothing. These functions were located on the 'Net. Commented Jan 24, 2018 at 2:37
  • What I meant by "that performs EXACTLY as I was needing" is ... if the focus is taken off a worksheet it provides notification of such. I need for the API code to detect if the VBE has focus or is open and for the same notification to be given. From that .. instead of a notification I can replace that with a call to a macro to perform the subsequent needed action/s. Commented Jan 24, 2018 at 3:34
  • 1
    Interesting question. Out of curiosity, why do you want to do this? Commented Jan 26, 2018 at 14:04

2 Answers 2

Reset to default
2

Why not just using the ThisWorkBook module with an Workbook_Open event?

Code in ThisWorkBook code module

 Private Sub Workbook_Open() ' or... Sub Workbook_Activate() ' checkIsVBEOpen If Application.VBE.MainWindow.Visible = True Then MsgBox "VBE window is open", vbInformation ' do something ' ... ' close VBE window Application.VBE.MainWindow.Visible = False Else MsgBox "VBE window is NOT open" ' do nothing else End If End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

T.M. Thank you for your response. I have taken your suggestion and added a timing function to it, placing the code in a Routine Module. The timer checks and fires the action as required. Again, THANK YOU to all who responded.
Do I need to mark this thread "SOLVED" or something similar ? If so ... how ?
2

Good news: only two minor changes are required to get it to work fine on my system (Excel 2013 x86 on Win 8.1 x64):

  • Comment out the offending line (!)

  • Add the following declaration for UnhookWinEventat the top of the module:

    Private Declare Function UnhookWinEvent Lib "user32.dll" (ByVal hHook As Long)

Set x=y sets object variable x to reference object instance y. As a result, it cannot be used for Long, String, or other non-object types. That is why you get an Object Required error when that line runs. Details of Set are in the answers to this question.

Separately, I'm not sure where you got the code from, but the error line would make the StopEventHook function a no-op if it worked:

Public Sub StopEventHook(lHook As Long) Dim LRet As Long On Error Resume Next Set lHook = 0 '<<<- The error line --- throws away the input parameter! If lHook = 0 Then Exit Sub ' ... then this always causes the Sub to exit. LRet = UnhookWinEvent(lHook) Exit Sub ' note: don't need this; you can remove it if you want. End Sub

If lHook did get set to 0, the next line would always cause the Sub to exit, so the hook would never be unloaded.

Possible crash issue

Sometimes Excel crashes when I close the workbook, but not always. I actually don't think of that as a problem because I am used to hooks bringing down Office :) . However, @RossBush's comment that "you could be killing the hook chain by not calling CallNextHookEx() in your WinProc" may be part of the issue. If you run into that problem and can't figure out how to fix it, I would suggest asking a separate question. There are certainly plenty of folks who have encountered the same!

Improve this answer

1 Comment

Thank you for your response. I'm running Win 10 / Excel 2007. The code "tries to work" but there is hesitation and crashing here. I've decided to use a different approach incorporating a macro similar to the one listed below by T.M.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.