To identify Excel VBA debug mode / Trap RRS feed

  • Question

  • I have an application which launches excel(excel 2010) , while launching the excel application we use to create instances of my MFC / com dlls in VBA and also we use debug the VBA code accordingly.

    As my  excel programs are large in size we use to keep the focus continuously on excel to achieve performance gain.

    The code to keep the focus on excel is developed in C++ COM dll and added as reference in VBA.

    The problem is while debugging the VBA code the excel is grabbing the focus from VBA and not allowing the user to debug the VBA code due to excel functionality implementation mentioned above.

    My idea is to stop the focus on excel when ever excel enter  in to the VBA due to VBA break point / trap.

    Is there any way / API to notify my COM dll when ever excel enters in to VBA due to trap / break point ?

    Please provide your suggestions.



    Friday, November 7, 2014 7:09 AM

All replies

  • Your situation is very confusing and I am not sure I understand properly but it sounds to me that your attached .dll forces the focus on Excel spreadsheet view... 

    If that's the case, can you not just remove the .dll from references for debugging purposes? Does it serve any other purpose than forcing the spreadsheet view? You mentioned just the performance impact so removing it temporarily probably wouldn't hurt?

    I mean you can call APIs like: FindWindow and SetActiveWindow but as soon as Debug.Print/Assert and breakpoints in code are hit by the VBA code the VBE gets the focus and there ain't much you can do about that.. 

    I am not sure this would be of any help but here is an example you can play with ->

    Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
        (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
    Declare Function SetActiveWindow Lib "user32.dll" _
        (ByVal hwnd As Long) As Long
    Sub Find_Window()
        Dim hWndExcel As Long
        hWndExcel = FindWindow("XLMAIN", Application.Caption)
        SetActiveWindow (hWndExcel)
    End Sub

    Friday, November 7, 2014 12:56 PM