Determine if VBE is open

Question

I am trying to develop an “autostart” macro to determine if VBE is open (not necessarily the focus window, just open). If it is TRUE, then ... follow some steps.

If this macro is connected to a CommandButton, it works, but I cannot get it to function anywhere in this book:

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:

 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 the update to the original question:

I found the following code that EXACTLY as I needed, but when closing the book, the macros 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 whole code, paste it 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 this book:

 Option Explicit Private Sub Workbook_BeforeClose(Cancel As Boolean) StopAllEventHooks End Sub Private Sub Workbook_Open() StartHook End Sub 

Answer 1

Why not just use the ThisWorkBook module with the Workbook_Open event?

Code in ThisWorkBook 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 

Answer 2

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

• Comment out the outer line (!)

• Add the following declaration for UnhookWinEvent at the top of the module:

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

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

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

 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 set to 0, the next line will always exit Sub , so the hook will never be unloaded.

Possible error

Sometimes Excel crashes when you close a workbook, but not always. I really don’t think of it as a problem, because I'm used to grabbing Office :). However, the @RossBush comment that “you can kill the hook chain without calling CallNextHookEx () in WinProc” may be part of the problem. If you encounter this problem and cannot figure out how to fix it, I would suggest asking a separate question. Of course, there are many people who have encountered this!