Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / September 2007

Tip: Looking for answers? Try searching our database.

Flag me if macro errors

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Jason Morin - 13 Sep 2007 16:44 GMT
I have series of 6 macros I call with a master macro. I'd like all macros to
run. If 1 or more macros errors out, I'd like it to move to the next macro in
the sequence, then notify me at the end (with a msgbox) which macros errored
out. Here's the basic construct. Thx.

Sub mMasterMacro()

On Error GoTo Exit_Me
Application.ScreenUpdating = False

'Run macros
mMacro1
mMacro2
mMacro3
mMacro4
mMacro5
mMacro6

Exit_Me:
Application.ScreenUpdating = True

End Sub
Bob Phillips - 13 Sep 2007 16:53 GMT
Jason,

Make them functions that return a Boolean, and trap errors and set to False
if error, True if not.

Then call like this

Sub mMasterMacro()

On Error GoTo Exit_Me
Application.ScreenUpdating = False

'Run macros
If Not mMacro1 Then sErrors = serrors & £nMacro1" &  vbnewline
If Not mMacro2 Then sErrors = serrors & £nMacro2" &  vbnewline
If Not mMacro3 Then sErrors = serrors & £nMacro3" &  vbnewline
If Not mMacro4 Then sErrors = serrors & £nMacro4" &  vbnewline
If Not mMacro5 Then sErrors = serrors & £nMacro5" &  vbnewline
If Not mMacro6 Then sErrors = serrors & £nMacro6" &  vbnewline

If sErrors <> "" then MsgBox sErrors

Exit_Me:
Application.ScreenUpdating = True

End Sub

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>I have series of 6 macros I call with a master macro. I'd like all macros
>to
[quoted text clipped - 21 lines]
>
> End Sub
JE McGimpsey - 13 Sep 2007 17:05 GMT
One way I've used is to call the macros as Functions rather than Subs.
For instance:

   Public Sub mMasterMacro()
       Dim sErr As String
       Application.ScreenUpdating = False
       If Not bFunction1 Then _
               sErr = vbNewLine & "Error in Function1"
       If Not bFunction2 Then _
               sErr = sErr & vbNewLine & "Error in Function2"
       If Not bFunction3 Then _
               sErr = sErr & vbNewLine & "Error in Function3"
       If Not bFunction4 Then _
               sErr = sErr & vbNewLine & "Error in Function4"
       If Not bFunction5 Then _
               sErr = sErr & vbNewLine & "Error in Function5"
       If Not bFunction6 Then _
               sErr = sErr & vbNewLine & "Error in Function6"
       If Len(sErr) <> 0 Then _
               MsgBox "There were errors:" & vbNewLine & sErr
       Application.ScreenUpdating = True
   End Sub

   Private Function bFunction1() As Boolean
       Dim bResult As Boolean
       bResult = True      'Assume success!
       On Error GoTo ErrHandler
       'code here
   ResumeHere:
       bFunction1 = bResult
       Exit Function
   ErrHandler:
       bResult = False
       'Additional error handling here
       Resume ResumeHere
   End Function

etc...

> I have series of 6 macros I call with a master macro. I'd like all macros to
> run. If 1 or more macros errors out, I'd like it to move to the next macro in
[quoted text clipped - 18 lines]
>
> End Sub
Tom Ogilvy - 13 Sep 2007 17:46 GMT
If you don't want to change them to functions, here is another approach.  The
called macros should have no internal error handling.

Sub mMasterMacro()
Dim i As Long, v As Variant
On Error GoTo ErrHandler
Application.ScreenUpdating = False
v = Array("mmacro1", "mmacro2", "mmacro3", _
 "mmacro4", "mmacro5", "mmacro6")
'Run macros
i = 1
mMacro1
i = 2
mMacro2
i = 3
mmacro3
i = 4
mmacro4
i = 5
mmacro5
i = 6
mmacro6
MsgBox Left(s, Len(s) - 1) & " had problems"
Application.ScreenUpdating = True
ErrHandler:
s = s & v(i - 1) & ","
Resume Next

End Sub

Sub mMacro1()
r = 3 / 0
End Sub
Sub mMacro2()
r = 3
End Sub
Sub mmacro3()
r = 4 / 0
End Sub
Sub mmacro4()
r = 4
End Sub
Sub mmacro5()
r = 5 / 0
End Sub
Sub mmacro6()
r = 5
End Sub

Signature

Regards,
Tom Ogilvy

> I have series of 6 macros I call with a master macro. I'd like all macros to
> run. If 1 or more macros errors out, I'd like it to move to the next macro in
[quoted text clipped - 18 lines]
>
> End Sub
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.