Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
On error GoTo Question
Good day, i would just like to know if it is good practice to use "On
Error GoTo" within your code e.g. Sub ChangeLcs() ' Open LCS list and modify ' Application.WindowState = xlMinimized Application.DisplayAlerts = False '----- If there is no file goto NoFile MsgBox ----------- On Error GoTo NoFile '----- If there is a file open it to process ------------ ChDir "L:\LCS" Workbooks.OpenText FileName:="L:\LCS\" & myLCS & ".xls", Origin:=437, StartRow _ :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _ , Space:=False, Other:=True, OtherChar:="|", FieldInfo:=Array(Array(1, 1 _ ), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 4), Array(8, 4), _ Array(9, 1), Array(10, 1)), TrailingMinusNumbers:=True With Rows("1:1") .Font.Bold = True .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Columns("A:J").EntireColumn.AutoFit Range("A2").Select ShowFileAccessInfoLCS '----- Message if there is no LCS file available --------NoFile: MsgBox "Sorry, there is no LCS report for the project you selected !" End Sub Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
On error GoTo Question
Les,
There is nothing wrong with using error information; something it is best (only) way to code. However, it is only useful if you use it in someway. And include a "Exit sub/Function" to avoid executing the handler, when no error has occurred e.g. On Error GoTo Handler '...Code Exit Sub Handler: Select Case Err.Number Case 70 'Fix the problem Resume 'Try that line again Case 80 'Change something Resume Next 'Continue on the next line Case Else 'Cannot fix Msgbox Err.Description 'Exit the sub End Select End Sub The is also "On Error Resume Next", which is correctly can be useful, but used wrongly will (probably) only lead to further errors being skipped over. NickHK "Les Stout" wrote in message ... Good day, i would just like to know if it is good practice to use "On Error GoTo" within your code e.g. Sub ChangeLcs() ' Open LCS list and modify ' Application.WindowState = xlMinimized Application.DisplayAlerts = False '----- If there is no file goto NoFile MsgBox ----------- On Error GoTo NoFile '----- If there is a file open it to process ------------ ChDir "L:\LCS" Workbooks.OpenText FileName:="L:\LCS\" & myLCS & ".xls", Origin:=437, StartRow _ :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _ , Space:=False, Other:=True, OtherChar:="|", FieldInfo:=Array(Array(1, 1 _ ), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 4), Array(8, 4), _ Array(9, 1), Array(10, 1)), TrailingMinusNumbers:=True With Rows("1:1") .Font.Bold = True .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Columns("A:J").EntireColumn.AutoFit Range("A2").Select ShowFileAccessInfoLCS '----- Message if there is no LCS file available --------NoFile: MsgBox "Sorry, there is no LCS report for the project you selected !" End Sub Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
On error GoTo Question
Thanks very musch for the Explanation NickHk
Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
On error GoTo Question
To me, the helps are not clear when an On Error ceases to function, so
I usually include On Error GoTo 0 to cancel the handler before I leave the procedure. On Error GoTo Handler ... code ... On Error GoTo 0 Exit Sub Handler: ...code... On Error GoTo 0 End Sub Carl. On Mar 7, 4:54 am, "NickHK" wrote: Les, There is nothing wrong with using error information; something it is best (only) way to code. However, it is only useful if you use it in someway. And include a "Exit sub/Function" to avoid executing the handler, when no error has occurred e.g. On Error GoTo Handler '...Code Exit Sub Handler: Select Case Err.Number Case 70 'Fix the problem Resume 'Try that line again Case 80 'Change something Resume Next 'Continue on the next line Case Else 'Cannot fix Msgbox Err.Description 'Exit the sub End Select End Sub The is also "On Error Resume Next", which is correctly can be useful, but used wrongly will (probably) only lead to further errors being skipped over. NickHK "Les Stout" wrote in message ... Good day, i would just like to know if it is good practice to use "On Error GoTo" within your code e.g. Sub ChangeLcs() ' Open LCS list and modify ' Application.WindowState = xlMinimized Application.DisplayAlerts = False '----- If there is no file goto NoFile MsgBox ----------- On Error GoTo NoFile '----- If there is a file open it to process ------------ ChDir "L:\LCS" Workbooks.OpenText FileName:="L:\LCS\" & myLCS & ".xls", Origin:=437, StartRow _ :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _ , Space:=False, Other:=True, OtherChar:="|", FieldInfo:=Array(Array(1, 1 _ ), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 4), Array(8, 4), _ Array(9, 1), Array(10, 1)), TrailingMinusNumbers:=True With Rows("1:1") .Font.Bold = True .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Columns("A:J").EntireColumn.AutoFit Range("A2").Select ShowFileAccessInfoLCS '----- Message if there is no LCS file available --------NoFile: MsgBox "Sorry, there is no LCS report for the project you selected !" End Sub Les Stout *** Sent via Developersdexhttp://www.developersdex.com***- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
On error GoTo Question
Carl, the on error only stays in effect during a procedure. I like to have
an error handler always in effect, so I often do the following: Sub Main_Procedure() On error goto eTrap X_Main_Procedure exit sub eTrap: msgbox "Woops" End Sub The above will trap almost all errors. One big exception is errors in userforms. For some reason those are not trapped by the above. Bob Flanagan Macro Systems 144 Dewberry Drive Hockessin, Delaware, U.S. 19707 Phone: 302-234-9857, cell 302-584-1771 http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "Carl Hartness" wrote in message oups.com... To me, the helps are not clear when an On Error ceases to function, so I usually include On Error GoTo 0 to cancel the handler before I leave the procedure. On Error GoTo Handler ... code ... On Error GoTo 0 Exit Sub Handler: ...code... On Error GoTo 0 End Sub Carl. On Mar 7, 4:54 am, "NickHK" wrote: Les, There is nothing wrong with using error information; something it is best (only) way to code. However, it is only useful if you use it in someway. And include a "Exit sub/Function" to avoid executing the handler, when no error has occurred e.g. On Error GoTo Handler '...Code Exit Sub Handler: Select Case Err.Number Case 70 'Fix the problem Resume 'Try that line again Case 80 'Change something Resume Next 'Continue on the next line Case Else 'Cannot fix Msgbox Err.Description 'Exit the sub End Select End Sub The is also "On Error Resume Next", which is correctly can be useful, but used wrongly will (probably) only lead to further errors being skipped over. NickHK "Les Stout" wrote in message ... Good day, i would just like to know if it is good practice to use "On Error GoTo" within your code e.g. Sub ChangeLcs() ' Open LCS list and modify ' Application.WindowState = xlMinimized Application.DisplayAlerts = False '----- If there is no file goto NoFile MsgBox ----------- On Error GoTo NoFile '----- If there is a file open it to process ------------ ChDir "L:\LCS" Workbooks.OpenText FileName:="L:\LCS\" & myLCS & ".xls", Origin:=437, StartRow _ :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _ , Space:=False, Other:=True, OtherChar:="|", FieldInfo:=Array(Array(1, 1 _ ), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 4), Array(8, 4), _ Array(9, 1), Array(10, 1)), TrailingMinusNumbers:=True With Rows("1:1") .Font.Bold = True .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Columns("A:J").EntireColumn.AutoFit Range("A2").Select ShowFileAccessInfoLCS '----- Message if there is no LCS file available --------NoFile: MsgBox "Sorry, there is no LCS report for the project you selected !" End Sub Les Stout *** Sent via Developersdexhttp://www.developersdex.com***- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Question on "On Error GoTo skip" | Excel Discussion (Misc queries) | |||
goto (F5) question | Excel Discussion (Misc queries) | |||
Error Handling - On Error GoTo doesn't trap error successfully | Excel Programming | |||
On Error Goto doesn't goto | Excel Programming | |||
On Error Goto doesn't goto | Excel Programming |