How to Diagnose Why "Code Execution has been interrupted"
Hi All,
For going on 2 years I've been building a large addin, over 100 modules, with 800 or so Subs and Functions. I'm not a 'professional' developer and am naive techie aspects of VIBDE enironment. Two days ago I started getting the Code execution has been interrupted error. The problem does not happen all the time. Macro's with not a lot of calls seem to do 'better'. Some of the code below is in called Subs or functions that are 5 levels 'deep' from the Sub executed via the menu I've built for the addin. Other than adding a new module to the app, I don't recall doing anything really different. I don't know what to do next to diagnose the condition causing the interruption, and am seeking suggestions. Samples of code with the error are below, they are NOT the same from running to running. Work done, and some facts are below, please let me know if you need more facts. Dell desktop, 346 meg memory, both disk drives recently defragged, XP home version. 4 workbooks are open in addition to addin, addin is 10 meg, workbook size ranges from < 30K to 7 meg. Have used Rob Bovey's code cleaner on ALL of the above. (daily on the addin) In ALL cases: each execution continues to successful end when Continue is clicked, err.number is zero in the immediate window. Every module in the addin is < 64K in size. Subs and functions range from a couple of lines to a max of about 1500. I get the error when running Bovey's code cleaner and Documentor as well as my own code and it too completes when Continue is clicked. Put in some "On error resume next" lines before the locations where the interruption seems to repeat, and the code stops on the 'On error...' too. Thanks much, Neal Z. Examples of code seen when debug button clicked: If Chars = "x" Or Chars = "" Or Chars = Dflt Then 'THIS LINE WAS YELLOW 'PART OF A ROW LOOP EDITING VALUES IN CELLS ' ELIM any lead/trail blanks from column B. If Len(.Cells(Row, iColB).Value) < Len(Trim(.Cells(Row, iColB).Value)) Then .Cells(Row, iColB).Value = Trim(.Cells(Row, iColB).Value) End If 'THIS LINE WAS YELLOW 'below part of larger macro formating a sheet If .Rows("2:" & (gSVCcolHdrRow - 1)).RowHeight = 16 Then Else: .Rows("2:" & (gSVCcolHdrRow - 1)).RowHeight = 16 'below, same macro as above .Rows((FirstCpyRow - 10) & ":" & (FirstCpyRow + PaEndRow + 10)).ClearContents 'different Mac from above, but again, a .rows process If SyPLastRow 0 Then SyPws.Columns.Hidden = False SyPws.Rows("1:" & SyPLastRow).Hidden = False 'THIS LINE WAS YELLOW Else GoSub Err_ColBNotFound End If Sub SCRNback(bASU As Boolean) 'Restore screenupdating to prior value. Application.ScreenUpdating = bASU End Sub 'this LINE was YELLOW Public Function Find_ValInColF(Ws As Worksheet, ByVal sLookFor As String, _ ByVal Row As Long, ByVal FmCol As Integer, ByVal ToCol As Integer) As Integer ' Return column of the cell where a value is found in a row. Dim Arg As Range If FmCol < 1 Then FmCol = 1 If ToCol < 1 Or ToCol MSoMaxCol Then ToCol = MSoMaxCol 'mso max=256 Set Arg = Ws.Range(Ws.Cells(Row, FmCol), Ws.Cells(Row, ToCol)) _ .Find(sLookFor, LookIn:=xlValues, Lookat:=xlWhole) ' LINE BELOW WAS YELLOW, Arg HAD a value. If Not Arg Is Nothing Then Find_ValInColF = Arg.Column End Function -- Neal Z |
How to Diagnose Why "Code Execution has been interrupted"
Neil, if the code is just stopping with the line highlighted, then set a
password on the project and click on the option to hide the project from viewing. I have seen similar problems and that appears to be the only solution. Bob Flanagan Macro Systems http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "Neal Zimm" wrote in message ... Hi All, For going on 2 years I've been building a large addin, over 100 modules, with 800 or so Subs and Functions. I'm not a 'professional' developer and am naive techie aspects of VIBDE enironment. Two days ago I started getting the Code execution has been interrupted error. The problem does not happen all the time. Macro's with not a lot of calls seem to do 'better'. Some of the code below is in called Subs or functions that are 5 levels 'deep' from the Sub executed via the menu I've built for the addin. Other than adding a new module to the app, I don't recall doing anything really different. I don't know what to do next to diagnose the condition causing the interruption, and am seeking suggestions. Samples of code with the error are below, they are NOT the same from running to running. Work done, and some facts are below, please let me know if you need more facts. Dell desktop, 346 meg memory, both disk drives recently defragged, XP home version. 4 workbooks are open in addition to addin, addin is 10 meg, workbook size ranges from < 30K to 7 meg. Have used Rob Bovey's code cleaner on ALL of the above. (daily on the addin) In ALL cases: each execution continues to successful end when Continue is clicked, err.number is zero in the immediate window. Every module in the addin is < 64K in size. Subs and functions range from a couple of lines to a max of about 1500. I get the error when running Bovey's code cleaner and Documentor as well as my own code and it too completes when Continue is clicked. Put in some "On error resume next" lines before the locations where the interruption seems to repeat, and the code stops on the 'On error...' too. Thanks much, Neal Z. Examples of code seen when debug button clicked: If Chars = "x" Or Chars = "" Or Chars = Dflt Then 'THIS LINE WAS YELLOW 'PART OF A ROW LOOP EDITING VALUES IN CELLS ' ELIM any lead/trail blanks from column B. If Len(.Cells(Row, iColB).Value) < Len(Trim(.Cells(Row, iColB).Value)) Then .Cells(Row, iColB).Value = Trim(.Cells(Row, iColB).Value) End If 'THIS LINE WAS YELLOW 'below part of larger macro formating a sheet If .Rows("2:" & (gSVCcolHdrRow - 1)).RowHeight = 16 Then Else: .Rows("2:" & (gSVCcolHdrRow - 1)).RowHeight = 16 'below, same macro as above .Rows((FirstCpyRow - 10) & ":" & (FirstCpyRow + PaEndRow + 10)).ClearContents 'different Mac from above, but again, a .rows process If SyPLastRow 0 Then SyPws.Columns.Hidden = False SyPws.Rows("1:" & SyPLastRow).Hidden = False 'THIS LINE WAS YELLOW Else GoSub Err_ColBNotFound End If Sub SCRNback(bASU As Boolean) 'Restore screenupdating to prior value. Application.ScreenUpdating = bASU End Sub 'this LINE was YELLOW Public Function Find_ValInColF(Ws As Worksheet, ByVal sLookFor As String, _ ByVal Row As Long, ByVal FmCol As Integer, ByVal ToCol As Integer) As Integer ' Return column of the cell where a value is found in a row. Dim Arg As Range If FmCol < 1 Then FmCol = 1 If ToCol < 1 Or ToCol MSoMaxCol Then ToCol = MSoMaxCol 'mso max=256 Set Arg = Ws.Range(Ws.Cells(Row, FmCol), Ws.Cells(Row, ToCol)) _ .Find(sLookFor, LookIn:=xlValues, Lookat:=xlWhole) ' LINE BELOW WAS YELLOW, Arg HAD a value. If Not Arg Is Nothing Then Find_ValInColF = Arg.Column End Function -- Neal Z |
All times are GMT +1. The time now is 04:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com