UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Vba Error Handling When Opening Another Macro, Any Version    
 
   
need_vba_help
post Aug 9 2019, 07:02 AM
Post#1



Posts: 8
Joined: 9-August 19



I have a process in which a macro loops through a list of other macros to run. I'm trying to add error handling in the parent macro so that if one of the other macros being called errors then it won't pop up with an alert and it will also close out and move on to the next macro in the list. Here is what I'm experimenting with but I'm finding it does not control the list of macros being opened.

CODE
Sub Test()
Application.DisplayAlerts = False

On Error GoTo ErrorHandling
    
Workbooks.Open Filename:= "C:\temp\SubMacro.xlsm"
    
Done:
    Exit Sub
ErrorHandling:
    MsgBox "The following error occurred: " & Err.Description
End Sub


Does anyone know if what I'm trying to achieve is possible?
Go to the top of the page
 
DanielPineault
post Aug 9 2019, 07:25 AM
Post#2


UtterAccess VIP
Posts: 6,901
Joined: 30-June 11



Have you tried using

CODE
On Error Resume Next


but this can be dangerous and normally to be avoided. Much better is setup your code to handle the errors and act appropriately to each.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
need_vba_help
post Aug 9 2019, 07:30 AM
Post#3



Posts: 8
Joined: 9-August 19



I have not tried that because I do not want the submacro being opened to continue. My goal is the main macro to recognize there is an error, close the submacro, and then update a flag or send an email to handle the error.
Go to the top of the page
 
Debaser
post Aug 9 2019, 07:40 AM
Post#4



Posts: 147
Joined: 11-October 18



On Error Resume Next in the calling macro will not affect whatever error handling (or lack thereof) is in the called macros. If the called macro stops with an error, control will pass back to the calling macro which will then carry on with its next line, not with the next line of the called code.
Go to the top of the page
 
need_vba_help
post Aug 9 2019, 07:52 AM
Post#5



Posts: 8
Joined: 9-August 19



I just tested
CODE
Sub Test()
Application.DisplayAlerts = False

On Error Resume Next
    
Workbooks.Open Filename:="C:\temp\SubMacro.xlsm"
    
End Sub


The problem with it is that the called macro still displays the error message which requires manual intervention to click end. Is there a way to eliminate any manual parts of it so that the calling macro will close out/ignore the called macro error?
Go to the top of the page
 
DanielPineault
post Aug 9 2019, 08:33 AM
Post#6


UtterAccess VIP
Posts: 6,901
Joined: 30-June 11



You'd need to setup proper error handling in the Child macros, On error resume next, but that is really bad practice.

Why are you getting errors in the 1st place is the real question here. The child macros should not be generating errors. That is what you should focus on, making the child macros not report 'useless' errors.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
need_vba_help
post Aug 9 2019, 08:47 AM
Post#7



Posts: 8
Joined: 9-August 19



That's what I was afraid of. I figured I would have to address it in each child macro and not be able to deal with it from the parent. I would say 95% of the time the child macros run without issue. However, such things such as file paths changing, files missing, etc cause them to fail and hold up the rest of the process which is why I was hoping to automate the handling of the error.
Go to the top of the page
 
Debaser
post Aug 9 2019, 09:20 AM
Post#8



Posts: 147
Joined: 11-October 18



You didn't call a macro, you just opened a workbook. Does that workbook automatically run code when it opens, and are you sure that:

a. It's actually a VBA error message?
b. The message is not displayed by an error handler in the code in the other workbook?
Go to the top of the page
 
need_vba_help
post Aug 9 2019, 09:38 AM
Post#9



Posts: 8
Joined: 9-August 19



The example I provided is a scaled down version of what is actually being done. The parent macro creates a list of several child macros to execute but for the sake of this discussion I was just opening one. Each macro is set to auto-run upon opening so that is why I am not telling it which macro to execute. To answer your other questions

a. Yes, the test macro I'm working with produces the error: "Run-time error '424': Object required"
b. There is no error handling in any of the child macros
Go to the top of the page
 
Debaser
post Aug 9 2019, 11:59 AM
Post#10



Posts: 147
Joined: 11-October 18



Unless the main code explicitly calls the code in the workbooks (eg using Application.Run), any error handler in the main code will not apply to the code in the opened workbooks.
Go to the top of the page
 
need_vba_help
post Aug 9 2019, 12:43 PM
Post#11



Posts: 8
Joined: 9-August 19



I'm not sure if this is exactly what you mean but I'm having the same issue where the child macro pops up with the error and the parent is not handling it

CODE
Sub Test()
Application.DisplayAlerts = False

On Error GoTo ErrorHandling
    
Application.Run "'C:\temp\SubMacro.xlsm'!ExecuteMyMacros"
    
Done:
    Exit Sub
ErrorHandling:
    MsgBox "The following error occurred: " & Err.Description
End Sub
Go to the top of the page
 
Debaser
post Aug 9 2019, 02:35 PM
Post#12



Posts: 147
Joined: 11-October 18



1. Is that workbook already open?
2. Given that your error handler creates an error message box, I assume you have checked that that is not the error you are seeing?
Go to the top of the page
 
need_vba_help
post Aug 12 2019, 07:27 AM
Post#13



Posts: 8
Joined: 9-August 19



1. The child macro is not open until told to do so in the parent
2. The error is definitely happening in the child macro because when I click End then it jumps back to the vba in the parent macro
Go to the top of the page
 
Debaser
post Aug 12 2019, 09:06 AM
Post#14



Posts: 147
Joined: 11-October 18



It appears as though Run works differently in Windows from Mac, which I had forgotten. On Windows, the error handling isn't passed down when you use Run.

Given that, if you really can't amend the macros in question, I think your only option would be using API calls to set up a hook waiting for any message boxes to appear and dismissing them - i.e. something like the code in post #7 here: https://www.mrexcel.com/forum/excel-questio...ard-msgbox.html
Go to the top of the page
 
need_vba_help
post Aug 12 2019, 10:40 AM
Post#15



Posts: 8
Joined: 9-August 19



Thanks for the link. I'll have to run some tests to see if that will work for my situation.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th October 2019 - 11:55 PM