Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closing a Macro Early
Dan,
Personally, I would never use Stop due to its immediate effect, not only on running code, but the state of variables through out. If code is written correctly, Stop is never necessary. May be you need something like this. Change your FormatTab routine to be a function and test its return value: Function FormatTab (arg as ???) As Boolean 'assume failure FormatTab=false 'Process.... If Somecondition=false then exit function 'Get here then everything OK FormatTab=true End Function Then in you calling code: For Each ws In Worksheets ws.Activate If FormatTab (Header)=False then exit sub Next You can return a string or long instead of a boolean so you can return more info as to the cause of the problem. Another option would be to use error handling. Function FormatTab (arg as ???) 'No error handling in routine 'Process.... If Somecondition=false then Err.raise 1250, "FormatTab", "The reason for the error" 'Get here then everything OK End Function Then in you calling code: On Error Goto Handler For Each ws In Worksheets ws.Activate FormatTab Header Next 'Other code Exit sub Handler: msgbox "Error in " & Err.Source & vbnewline & Err.Description End sub NickHK "Charles Chickering" wrote in message ... Type Stop instead of exit sub -- Charles Chickering "A good example is twice the value of good advice." "Dan Kelly" wrote: I have a Macro that iterates through all the Worksheets in a Workbook and changes their format using as part of the following Loop: For Each ws In Worksheets ws.Activate FormatTab (Header) Next ActiveWorkbook.Protect MsgBox ("Issue Sheet Converted") During the FormatTab routine I test for an incorrect format and am trying to exit the Macro at that point using the following code to close the workbook without saving: MsgBox (errortxt) ActiveWorkbook.Saved = True ActiveWorkbook.Close Exit Sub This however causes me a problem as the original code is trying to complete - and falls over on the ActiveWorkbook.Protect Can I halt the Macro rather than the Sub at Exit Sub? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ending a macro early conditionally on one cell being blank | Excel Discussion (Misc queries) | |||
Help with auto macro on closing | Excel Discussion (Misc queries) | |||
Closing excel from a macro | Excel Programming | |||
closing without saving within a macro | Excel Programming | |||
Closing Excel from a Macro | Excel Programming |