none
Excel 2013 hangs when opening multiple excel windows via VBA for a single workbook. RRS feed

  • Question

  • Hello, I was wondering if anyone had come across this problem or if anyone might know of issues with the code I'm using. I appreciate any feedback you can offer. Thank you.

    Current Environment: 
     Windows 7 SP1, Office 365, Norton 360 Security

     Problem:
     Opening multiple excel windows via excel vba for a single workbook causes excel to freeze.
     Occurs in Excel 2013 (Office 365) - Does not occur in Excel 2007 on Windows 7 SP1 (Same Computer)
     This issue has always occurred with excel 2013, bypassed before by using windows 2007.
     Issue also occurs running 2013 on Windows 8.1 Pro

     Note: The problem does not occur during the run of the vba code, it occurs after the code has 
     completed successfully and another action is taken on the spreadsheet. Also, I tried manually opening multiple windows in the same file but no issues were found doing this: View->New Window

    Workbook description:
     Created a clean, pared-down workbook to test with: test.xlsm
     Size: 22Kb
     Sheets: 1
     Named Ranges: None
     Formulas: None
     Links/Hyperlinks: None
     Tables Defined: 1 
     Table Size: 5 Columns, 470 rows
     Modules: 1
     Macros in Module: 2
     Event Modules: None (Nothing occurs based on spreadsheet events)

     Actions to recreate the problem:
     1. Open workbook
     2. Select Developer->Macros: Choose InitWindows in the selection screen.
     3. Select Formulas->Name Manager.

    Note: Kept this workbook simple for testing by not adding buttons or event triggers. Also it always hangs when I open the Name Manager but hanging will occur during other activity as well.

    Code Being Run:

    Note: The code snippet did not have an option for VBA so I chose VB.net. It looks terrible but I don't know how to fix it. I've placed a copy outside code snippet for now so it can be seen.

    Also you'll notice in this test version of the code I open the same sheet in each window, again this is simply to keep the book and code as small and simple as possible.

    Option Explicit
    Global glbWkBkName As String

    '---------------------------------------------------------------------------------------
    ' Procedure : InitWindows
    ' Author    : Ron
    ' Date      : 6/7/2015
    ' Called By : Workbook_Open
    ' Purpose   : Sets up 3 windows upon entering the workbook. Provides a view of 3 sheets
    '             used while entering data. The middle sheet is where data entry is performed,
    '             the left sheet provides reference information on the data being entered,
    '             and the right sheet provides summary information about the data entered.
    '---------------------------------------------------------------------------------------
    '
    Sub InitWindows()
        Dim wnWin As Window

        On Error GoTo InitWindows_Error
        
        glbWkBkName = ThisWorkbook.Name

        Application.ScreenUpdating = False

        'Get rid of all open windows to start at 1.
        'Easier than determining which windows are open and processing them.

        Do Until Windows.Count = 1
            Windows(Windows.Count).Close
        Loop

        'Create 2 more for a total of 3 windows.
        ActiveWindow.NewWindow
        ActiveWindow.NewWindow

        For Each wnWin In Windows
            Select Case wnWin.WindowNumber
                Case Is = 1:    SetWindow wnWin, "Test", 6, 514
                Case Is = 2:    SetWindow wnWin, "Test", 530, 698
                Case Is = 3:    SetWindow wnWin, "Test", 1230, 200
            End Select
        Next wnWin

        Debug.Assert glbWkBkName <> ""
        Set wnWin = Windows(glbWkBkName & ":2")

        Windows(glbWkBkName & ":2").Activate

        'Debug.Print glbWkBkName & ":2"
        'ClrSkillTreeCharData
    ExitProcedure:
        On Error Resume Next
        Set wnWin = Nothing
        Application.ScreenUpdating = True
        Exit Sub

    InitWindows_Error:
        'Call UnexpectedError(Err.Number, Err.Description, Err.Source, _
        'Err.HelpFile, Erl, "InitWindows")
        Resume ExitProcedure
    End Sub
    '---------------------------------------------------------------------------------------
    ' Procedure : SetWindow
    ' Author    : Ron
    ' Date      : 6/7/2015
    ' Called By : InitWindows
    ' Purpose   : Positions a Window based on the information passed in and sets the active
    '             sheet.
    '---------------------------------------------------------------------------------------
    '
    Private Sub SetWindow(ByRef wnWindow As Window, wsSheet As String, lLeftPos As Long, lWidth As Long)
    On Error GoTo SetWindow_Error
        wnWindow.Activate
        Sheets(wsSheet).Select
        With wnWindow
            .WindowState = xlNormal
            .Top = 6
            .Left = lLeftPos
            .Width = lWidth
            .Height = 627
            .DisplayGridlines = False
        End With

    ExitProcedure:
        On Error Resume Next
    Exit Sub

    SetWindow_Error:
        '(Err.Number, Err.Description, Err.Source, _
        'Err.HelpFile, Erl, "SetWindow")
        Resume ExitProcedure
    End Sub


    ---------------------------------------------------------------------------------------' Procedure : InitWindows' Author    : Ron' Date      : 6/7/2015' Called By : ' Purpose   : Sets up 3 windows upon entering the workbook. Provides a view of 3 sheets'             used while entering data. The middle sheet is where data entry is performed,'             the left sheet provides reference information on the data being entered,'             and the right sheet provides summary information about the data entered.'---------------------------------------------------------------------------------------'Sub InitWindows()    Dim wnWin As Window    On Error GoTo InitWindows_Error        glbWkBkName = ThisWorkbook.Name    Application.ScreenUpdating = False    'Get rid of all open windows to start at 1.    'Easier than determining which windows are open and processing them.    Do Until Windows.Count = 1        Windows(Windows.Count).Close    Loop    'Create 2 more for a total of 3 windows.    ActiveWindow.NewWindow    ActiveWindow.NewWindow    For Each wnWin In Windows        Select Case wnWin.WindowNumber            Case Is = 1:    SetWindow wnWin, "Test", 6, 514            Case Is = 2:    SetWindow wnWin, "Test", 530, 698            Case Is = 3:    SetWindow wnWin, "Test", 1230, 200        End Select    Next wnWin    Debug.Assert glbWkBkName <> ""    Set wnWin = Windows(glbWkBkName & ":2")    Windows(glbWkBkName & ":2").Activate    'Debug.Print glbWkBkName & ":2"    'ClrSkillTreeCharDataExitProcedure:    On Error Resume Next    Set wnWin = Nothing    Application.ScreenUpdating = True    Exit SubInitWindows_Error:    'Call UnexpectedError(Err.Number, Err.Description, Err.Source, _    'Err.HelpFile, Erl, "InitWindows")    Resume ExitProcedureEnd Sub'---------------------------------------------------------------------------------------' Procedure : SetWindow' Author    : Ron' Date      : 6/7/2015' Called By : InitWindows' Purpose   : Positions a Window based on the information passed in and sets the active'             sheet.'---------------------------------------------------------------------------------------'Private Sub SetWindow(ByRef wnWindow As Window, wsSheet As String, lLeftPos As Long, lWidth As Long)On Error GoTo SetWindow_Error    wnWindow.Activate    Sheets(wsSheet).Select    With wnWindow        .WindowState = xlNormal        .Top = 6        .Left = lLeftPos        .Width = lWidth        .Height = 627        .DisplayGridlines = False    End WithExitProcedure:    On Error Resume NextExit SubSetWindow_Error:    '(Err.Number, Err.Description, Err.Source, _    'Err.HelpFile, Erl, "SetWindow")    Resume ExitProcedureEnd Sub
    Steps taken to resolve:
    1. Verified code was good:
    Searched internet/microsoft site for examples
    All code appears to be valid for 2013 version.
    Ran code through debugger: no issues found.
    2. Made sure no addins were turned on: No result.
    a) Select Developer->AddIns unchecked all.
    b) Select File->Options->Addins->Manage->COM addins unchecked all.
    3. Made sure Office version was up to date.
    4. Turned off anti-virus: No result. (Suggested in a microsoft troubleshooting procedure found on internet)
    5. Did a repair on the office 365 software: No result.
    4. Opened excel in safemode: Issue still occurred.
    6. Ran checks on workbook: File->Info-Inspect Workbook: No issues found. 
    7. Ran troubleshoot compatibility: 
       Select Start->All Applications->Microsoft Office 2013->Excel (shift right click)->Troubleshoot Compatibility
       Had a problem fixing the issue raised an error saying: Incompatible Application-> Fix Application EXCEL
    8. Manually tried setting compatibility:
    Start->All Applications->Microsoft Office 2013->Excel (right click)->Properties->Compatibility
    Windows XP SP 2 and Windows XP SP 3:  
    Error occured upon trying to open file: There was a problem sending the command to the program.
        Windows Vista SP 2: File begins to open and then quickly disappears.
    Windows 7: Original issue continues to occur.


       



    • Edited by njdba Monday, June 8, 2015 6:37 PM
    Monday, June 8, 2015 6:28 PM

Answers

  • I did some additional testing and found that if I modify the line that says:

    Case Is = 3:    SetWindow wnWin, "Test", 1230, 200

    To

    Case Is = 3:    SetWindow wnWin, "Test", 1230, 225

    Excel no longer freezes. It appears excel was unable to handle window widths of 200 or lower. I reported this issue to Microsoft and they agreed that this is a bug in excel 2013. They are currently working on it.


    • Marked as answer by njdba Saturday, June 13, 2015 10:02 PM
    Saturday, June 13, 2015 6:09 PM

All replies

  • Hello, I was wondering if anyone had come across this problem or if anyone might know of issues with the code I'm using. I appreciate any feedback you can offer. Thank you.

    Current Environment: 
     Windows 7 SP1, Office 365, Norton 360 Security

     Problem:
     Opening multiple excel windows via excel vba for a single workbook causes excel to freeze.
     Occurs in Excel 2013 (Office 365) - Does not occur in Excel 2007 on Windows 7 SP1 (Same Computer)
     This issue has always occurred with excel 2013, bypassed before by using windows 2007.
     Issue also occurs running 2013 on Windows 8.1 Pro

     Note: The problem does not occur during the run of the vba code, it occurs after the code has 
     completed successfully and another action is taken on the spreadsheet. Also, I tried manually opening multiple windows in the same file but no issues were found doing this: View->New Window

    Workbook description:
     Created a clean, pared-down workbook to test with: test.xlsm
     Size: 22Kb
     Sheets: 1
     Named Ranges: None
     Formulas: None
     Links/Hyperlinks: None
     Tables Defined: 1 
     Table Size: 5 Columns, 470 rows
     Modules: 1
     Macros in Module: 2
     Event Modules: None (Nothing occurs based on spreadsheet events)

     Actions to recreate the problem:
     1. Open workbook
     2. Select Developer->Macros: Choose InitWindows in the selection screen.
     3. Select Formulas->Name Manager.

    Note: Kept this workbook simple for testing by not adding buttons or event triggers. Also it always hangs when I open the Name Manager but hanging will occur during other activity as well.

    Code Being Run:

    Note: The code snippet did not have an option for VBA so I chose VB.net. It looks terrible but I don't know how to fix it. I've placed a copy outside code snippet for now so it can be seen.

    Also you'll notice in this test version of the code I open the same sheet in each window, again this is simply to keep the book and code as small and simple as possible.

    Option Explicit
    Global glbWkBkName As String

    '---------------------------------------------------------------------------------------
    ' Procedure : InitWindows
    ' Author    : Ron
    ' Date      : 6/7/2015
    ' Called By : Workbook_Open
    ' Purpose   : Sets up 3 windows upon entering the workbook. Provides a view of 3 sheets
    '             used while entering data. The middle sheet is where data entry is performed,
    '             the left sheet provides reference information on the data being entered,
    '             and the right sheet provides summary information about the data entered.
    '---------------------------------------------------------------------------------------
    '
    Sub InitWindows()
        Dim wnWin As Window

        On Error GoTo InitWindows_Error
        
        glbWkBkName = ThisWorkbook.Name

        Application.ScreenUpdating = False

        'Get rid of all open windows to start at 1.
        'Easier than determining which windows are open and processing them.

        Do Until Windows.Count = 1
            Windows(Windows.Count).Close
        Loop

        'Create 2 more for a total of 3 windows.
        ActiveWindow.NewWindow
        ActiveWindow.NewWindow

        For Each wnWin In Windows
            Select Case wnWin.WindowNumber
                Case Is = 1:    SetWindow wnWin, "Test", 6, 514
                Case Is = 2:    SetWindow wnWin, "Test", 530, 698
                Case Is = 3:    SetWindow wnWin, "Test", 1230, 200
            End Select
        Next wnWin

        Debug.Assert glbWkBkName <> ""
        Set wnWin = Windows(glbWkBkName & ":2")

        Windows(glbWkBkName & ":2").Activate

        'Debug.Print glbWkBkName & ":2"
        'ClrSkillTreeCharData
    ExitProcedure:
        On Error Resume Next
        Set wnWin = Nothing
        Application.ScreenUpdating = True
        Exit Sub

    InitWindows_Error:
        'Call UnexpectedError(Err.Number, Err.Description, Err.Source, _
        'Err.HelpFile, Erl, "InitWindows")
        Resume ExitProcedure
    End Sub
    '---------------------------------------------------------------------------------------
    ' Procedure : SetWindow
    ' Author    : Ron
    ' Date      : 6/7/2015
    ' Called By : InitWindows
    ' Purpose   : Positions a Window based on the information passed in and sets the active
    '             sheet.
    '---------------------------------------------------------------------------------------
    '
    Private Sub SetWindow(ByRef wnWindow As Window, wsSheet As String, lLeftPos As Long, lWidth As Long)
    On Error GoTo SetWindow_Error
        wnWindow.Activate
        Sheets(wsSheet).Select
        With wnWindow
            .WindowState = xlNormal
            .Top = 6
            .Left = lLeftPos
            .Width = lWidth
            .Height = 627
            .DisplayGridlines = False
        End With

    ExitProcedure:
        On Error Resume Next
    Exit Sub

    SetWindow_Error:
        '(Err.Number, Err.Description, Err.Source, _
        'Err.HelpFile, Erl, "SetWindow")
        Resume ExitProcedure
    End Sub


    ---------------------------------------------------------------------------------------' Procedure : InitWindows' Author    : Ron' Date      : 6/7/2015' Called By : ' Purpose   : Sets up 3 windows upon entering the workbook. Provides a view of 3 sheets'             used while entering data. The middle sheet is where data entry is performed,'             the left sheet provides reference information on the data being entered,'             and the right sheet provides summary information about the data entered.'---------------------------------------------------------------------------------------'Sub InitWindows()    Dim wnWin As Window    On Error GoTo InitWindows_Error        glbWkBkName = ThisWorkbook.Name    Application.ScreenUpdating = False    'Get rid of all open windows to start at 1.    'Easier than determining which windows are open and processing them.    Do Until Windows.Count = 1        Windows(Windows.Count).Close    Loop    'Create 2 more for a total of 3 windows.    ActiveWindow.NewWindow    ActiveWindow.NewWindow    For Each wnWin In Windows        Select Case wnWin.WindowNumber            Case Is = 1:    SetWindow wnWin, "Test", 6, 514            Case Is = 2:    SetWindow wnWin, "Test", 530, 698            Case Is = 3:    SetWindow wnWin, "Test", 1230, 200        End Select    Next wnWin    Debug.Assert glbWkBkName <> ""    Set wnWin = Windows(glbWkBkName & ":2")    Windows(glbWkBkName & ":2").Activate    'Debug.Print glbWkBkName & ":2"    'ClrSkillTreeCharDataExitProcedure:    On Error Resume Next    Set wnWin = Nothing    Application.ScreenUpdating = True    Exit SubInitWindows_Error:    'Call UnexpectedError(Err.Number, Err.Description, Err.Source, _    'Err.HelpFile, Erl, "InitWindows")    Resume ExitProcedureEnd Sub'---------------------------------------------------------------------------------------' Procedure : SetWindow' Author    : Ron' Date      : 6/7/2015' Called By : InitWindows' Purpose   : Positions a Window based on the information passed in and sets the active'             sheet.'---------------------------------------------------------------------------------------'Private Sub SetWindow(ByRef wnWindow As Window, wsSheet As String, lLeftPos As Long, lWidth As Long)On Error GoTo SetWindow_Error    wnWindow.Activate    Sheets(wsSheet).Select    With wnWindow        .WindowState = xlNormal        .Top = 6        .Left = lLeftPos        .Width = lWidth        .Height = 627        .DisplayGridlines = False    End WithExitProcedure:    On Error Resume NextExit SubSetWindow_Error:    '(Err.Number, Err.Description, Err.Source, _    'Err.HelpFile, Erl, "SetWindow")    Resume ExitProcedureEnd Sub
    Steps taken to resolve:
    1. Verified code was good:
    Searched internet/microsoft site for examples
    All code appears to be valid for 2013 version.
    Ran code through debugger: no issues found.
    2. Made sure no addins were turned on: No result.
    a) Select Developer->AddIns unchecked all.
    b) Select File->Options->Addins->Manage->COM addins unchecked all.
    3. Made sure Office version was up to date.
    4. Turned off anti-virus: No result. (Suggested in a microsoft troubleshooting procedure found on internet)
    5. Did a repair on the office 365 software: No result.
    4. Opened excel in safemode: Issue still occurred.
    6. Ran checks on workbook: File->Info-Inspect Workbook: No issues found. 
    7. Ran troubleshoot compatibility: 
       Select Start->All Applications->Microsoft Office 2013->Excel (shift right click)->Troubleshoot Compatibility
       Had a problem fixing the issue raised an error saying: Incompatible Application-> Fix Application EXCEL
    8. Manually tried setting compatibility:
    Start->All Applications->Microsoft Office 2013->Excel (right click)->Properties->Compatibility
    Windows XP SP 2 and Windows XP SP 3:  
    Error occured upon trying to open file: There was a problem sending the command to the program.
        Windows Vista SP 2: File begins to open and then quickly disappears.
    Windows 7: Original issue continues to occur.


       



    Sorry for the trouble. If you suspect Norton might cause this issue, you can disable your Norton and check if you still have this issue. Let me know if you need any further help. 

    Thanks!

    Raj

    Norton Support

    Thursday, June 11, 2015 3:01 AM
  • Thank you Raj.

    I do not believe Norton is the issue.

    After further testing I have found that the problem stems from the window that is being opened with a width of 200. I changed that value to a larger number, 225, and the freezing does not occur. Anything below 200 seems to cause excel to freeze. I reported the problem to Microsoft Tech Support. I'll update this as I know more.

    Thanks again for your input.

    Friday, June 12, 2015 8:45 PM
  • I did some additional testing and found that if I modify the line that says:

    Case Is = 3:    SetWindow wnWin, "Test", 1230, 200

    To

    Case Is = 3:    SetWindow wnWin, "Test", 1230, 225

    Excel no longer freezes. It appears excel was unable to handle window widths of 200 or lower. I reported this issue to Microsoft and they agreed that this is a bug in excel 2013. They are currently working on it.


    • Marked as answer by njdba Saturday, June 13, 2015 10:02 PM
    Saturday, June 13, 2015 6:09 PM