none
VBE flashes while programming in the VBE RRS feed

  • Question

  • I am creating a worksheet using VBA.  The worksheet includes Event Code which is also being created using the VBE, guided using information from Chip Pearson's website. At first, I had a problem where the VBE window would open to the worksheet code after the macro was run.  I added code to keep only the desired VBE windows visible, and also added code from Chip Pearson site to Eliminate Screen Flicker During VBProject Code.

    At present, the VBE remains closed after the code is run, but there is a single flash of a blank window that occurs. The window stays on the screen long enough to see that it has the caption of the main VBE window, and is completely empty.

    Any suggestions as to how to get rid of this completely would be appreciated.

    Here is some relevant code portions:

    Option Explicit
    'For LockWindowUpdate Windows API Function Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _ (ByVal ClassName As String, ByVal WindowName As String) As Long Private Declare Function LockWindowUpdate Lib "user32" _ (ByVal hWndLock As Long) As Long Sub CreateSummarySheet(Optional NEWSHEET As Boolean = True)

    ...
    Variable declarations
    ...

    'For LockWindowUpdate Windows API Function Dim VBEHwnd As Long On Error GoTo ErrH: Application.VBE.MainWindow.Visible = False VBEHwnd = FindWindow("wndclass_desked_gsk", _ Application.VBE.MainWindow.Caption) If VBEHwnd Then LockWindowUpdate VBEHwnd End If With Application .ScreenUpdating = False .EnableEvents = False 'do not run event code while creating or altering sheet End With ...
    More code
    ... 'Event Code that gets added

    Dim vbProj As VBIDE.VBProject
        Dim vbComps As VBIDE.VBComponents
        Dim vbComp As VBIDE.VBComponent
        Dim vbCodeMod As VBIDE.CodeModule
        Dim LineNum As Long
        Const DQUOTE As String = """" 'one doublequote character
        
        Set vbProj = ThisWorkbook.VBProject
        Set vbComp = vbProj.VBComponents(Worksheets("Summary").CodeName)
        Set vbCodeMod = vbComp.CodeModule With vbCodeMod LineNum = .CreateEventProc("Change", "Worksheet") LineNum = LineNum + 1 .InsertLines LineNum + 1, "Dim r As Range, rw As Range, c As Range" .InsertLines LineNum + 2, " Dim i As Long, j As Long, k As Long, l As Long"
    ...
    More event code
    ...
    End with With Application .ScreenUpdating = True .EnableEvents = True End With

    ...
    More code formatting the worksheet
    ... 'For LockWindowUpdate Windows API Function Application.VBE.MainWindow.Visible = False ErrH: LockWindowUpdate 0& End Sub

    Some further investigation shows that the problem seems to be occurring at this point:

    With vbCodeMod
      LineNum = .CreateEventProc("Change", "Worksheet")

    After that first "LineNum" code runs, the window opens.  Interrupting the code prior to that line does not show any open windows.  If I interrupt the code after that line,  I see the "Sheet Code" window with just that one line.  Putting the LockWindowUpdate before that line doesn't improve things.  Putting the LockWindowUpdate after that line immediately closes that window, but does not prevent it from opening. I don't know enough about what is required to prevent that window from opening, but it seems to me that is what I need.

    Thanks.


    Ron


    Saturday, March 23, 2013 3:12 AM

Answers

  • Ron,

    I have no solution for your problem, but why don't you use a hidden template sheet (with code) that you just copy and rename?

    Bernie


    Saturday, March 23, 2013 1:02 PM
  • Hi Ron,

    FWIW I agree the template approach is better than adding code, particularly to "self". Although perhaps not in this case in some scenarios the project running the code can recompile while running causing "bad things to happen".

    If you particularly want to add code to any module AFAIK there's no way to prevent the VBE opening (if it wasn't already) and the window of the module being written to from also opening. Disabling screenupdating doesn't help (it's the VBE not Excel) and personally I have enough bad experience with LockWindowUpdate I don't even want to test your code!

    However go with the flow. Don't wait for the VBE to get opened, open it yourself and put it out of the way, ie off the screen. When done restore things and close the VBE. In a light test this worked for me, no flicker and without disabling screenupdating or LockWindowUpdate. No doubt there are better ways to do it.

    Option Explicit
    Declare Function SetWindowPos Lib "user32.dll" (ByVal hwnd As _
                                 Long, ByVal hWndInsertAfter As Long, ByVal x As Long, ByVal
    y As _
                                 Long, ByVal cx As Long, ByVal cy As Long, ByVal wFlags As
    Long) As Long
    Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
                                 (ByVal ClassName As String, ByVal WindowName As String) As
    Long
    
    Const HWND_NOTOPMOST = -2
    Const SWP_HIDEWINDOW = &H80
    Const SWP_NOSIZE = &H1
    
    Sub OpenAndHideVBE()
    Dim hWndVBE As Long
    Dim objVBE As vbe
            Set objVBE = Application.vbe
            hWndVBE = FindWindow("wndclass_desked_gsk", _
                                                    Application.vbe.MainWindow.Caption)
            Call SetWindowPos(hWndVBE, 0&, 0&, 2000&, 1, 1, SWP_HIDEWINDOW Or
    SWP_NOSIZE)
         Application.vbe.MainWindow.Visible = True
            AppActivate Application.Caption
    
    End Sub
    
    Sub ShowAndCloseVBE()
    Dim hWndVBE As Long
    Dim cbt As CommandBarButton
    Dim objVBE As VBIDE.vbe
    Dim objWin As VBIDE.Window
            Set objVBE = Application.vbe
            ' optionally close all module windows, or just the newly opened module 
    window
         For Each objWin In objVBE.Windows
                 If objWin.Type = vbext_wt_CodeWindow Then
                         objWin.Close
                 ElseIf objWin.Type = vbext_wt_Designer Then
                         objWin.Close
                 End If
         Next
            objVBE.MainWindow.WindowState = vbext_ws_Minimize
            objVBE.MainWindow.Visible = False
            hWndVBE = FindWindow("wndclass_desked_gsk", _
                                                    Application.vbe.MainWindow.Caption)
            Call SetWindowPos(hWndVBE, HWND_NOTOPMOST, 0, 0, 400, 300, 0)
            Set cbt = Application.vbe.CommandBars.FindControl(ID:=752)
         AppActivate Application.Caption
         cbt.Execute
    
    End Sub

    Call OpenAndHideVBE before writing to the project and ShowAndCloseVBE when done.

    It might be worth trapping the "normal" window's size with GetWindowRect then resetting same in the second SetWindowPos call. If your screen is taller than 2000 increase the temporary top (y) position.

    Peter Thornton

    Saturday, March 23, 2013 4:11 PM
    Moderator

All replies

  • Ron,

    I have no solution for your problem, but why don't you use a hidden template sheet (with code) that you just copy and rename?

    Bernie


    Saturday, March 23, 2013 1:02 PM
  • Thank you, Bernie, for that alternative suggestion.  I had thought of using workbook code, but rejected that idea as the sheet name might be changed unpredictably by the end user, and a new sheet generated with the proper name, both of which would require the event code.

    But a template should work, I think.  And would probably execute faster.  If no one comes up with a solution to the flashing, I will give that a try.

    This is my first attempt to create a worksheet with event code using VBA, so I was exploring the technique.  But the screen flash is not something the end user should have to deal with, and developing a solution to that might be useful in the future.


    Ron

    Saturday, March 23, 2013 3:49 PM
  • Hi Ron,

    FWIW I agree the template approach is better than adding code, particularly to "self". Although perhaps not in this case in some scenarios the project running the code can recompile while running causing "bad things to happen".

    If you particularly want to add code to any module AFAIK there's no way to prevent the VBE opening (if it wasn't already) and the window of the module being written to from also opening. Disabling screenupdating doesn't help (it's the VBE not Excel) and personally I have enough bad experience with LockWindowUpdate I don't even want to test your code!

    However go with the flow. Don't wait for the VBE to get opened, open it yourself and put it out of the way, ie off the screen. When done restore things and close the VBE. In a light test this worked for me, no flicker and without disabling screenupdating or LockWindowUpdate. No doubt there are better ways to do it.

    Option Explicit
    Declare Function SetWindowPos Lib "user32.dll" (ByVal hwnd As _
                                 Long, ByVal hWndInsertAfter As Long, ByVal x As Long, ByVal
    y As _
                                 Long, ByVal cx As Long, ByVal cy As Long, ByVal wFlags As
    Long) As Long
    Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
                                 (ByVal ClassName As String, ByVal WindowName As String) As
    Long
    
    Const HWND_NOTOPMOST = -2
    Const SWP_HIDEWINDOW = &H80
    Const SWP_NOSIZE = &H1
    
    Sub OpenAndHideVBE()
    Dim hWndVBE As Long
    Dim objVBE As vbe
            Set objVBE = Application.vbe
            hWndVBE = FindWindow("wndclass_desked_gsk", _
                                                    Application.vbe.MainWindow.Caption)
            Call SetWindowPos(hWndVBE, 0&, 0&, 2000&, 1, 1, SWP_HIDEWINDOW Or
    SWP_NOSIZE)
         Application.vbe.MainWindow.Visible = True
            AppActivate Application.Caption
    
    End Sub
    
    Sub ShowAndCloseVBE()
    Dim hWndVBE As Long
    Dim cbt As CommandBarButton
    Dim objVBE As VBIDE.vbe
    Dim objWin As VBIDE.Window
            Set objVBE = Application.vbe
            ' optionally close all module windows, or just the newly opened module 
    window
         For Each objWin In objVBE.Windows
                 If objWin.Type = vbext_wt_CodeWindow Then
                         objWin.Close
                 ElseIf objWin.Type = vbext_wt_Designer Then
                         objWin.Close
                 End If
         Next
            objVBE.MainWindow.WindowState = vbext_ws_Minimize
            objVBE.MainWindow.Visible = False
            hWndVBE = FindWindow("wndclass_desked_gsk", _
                                                    Application.vbe.MainWindow.Caption)
            Call SetWindowPos(hWndVBE, HWND_NOTOPMOST, 0, 0, 400, 300, 0)
            Set cbt = Application.vbe.CommandBars.FindControl(ID:=752)
         AppActivate Application.Caption
         cbt.Execute
    
    End Sub

    Call OpenAndHideVBE before writing to the project and ShowAndCloseVBE when done.

    It might be worth trapping the "normal" window's size with GetWindowRect then resetting same in the second SetWindowPos call. If your screen is taller than 2000 increase the temporary top (y) position.

    Peter Thornton

    Saturday, March 23, 2013 4:11 PM
    Moderator
  • Thank you, Peter.  I will give your code a try.

    Ron

    Saturday, March 23, 2013 6:08 PM