Macro to run another macro

[mattress58]

I am running a macro that opens another file, runs a macro in that workbook and after it runs my macro extracts data from it. The problem I am having is that if an error occurs while the macro that is running in the workbook I open (due to the parameters selected), that macro will have a message box pop up that says "No Rates Found". My macro is still running because it doesn't know that the other has stopped and wont produce the data.

What is a way to have my macro stop when this happens and have a message box pop up also. I don't have access to the macro I am running against. I just pulled the name of the macro behind the button when you right click and select assign macro like you can see below.

Please Login or Register  to view this content.

[Jim885]

... What is a way to have my macro stop when this happens and have a message box pop up also. I don't have access to the macro I am running against. I just pulled the name of the macro behind the button when you right click and select assign macro like you can see below.

Please Login or Register  to view this content.

The best way to resolve this is for you to post the complete macros you are running. Otherwise, unless someone has ESP, there is no way to help you.
Use Alt + F11 to open the VB Editor. Then paste the macros to this thread.

[mattress58]

Please Login or Register  to view this content.

[LJMetzger]

Hi mattress58,

You need to recode the son Macro as a function. It can then return a value to the father macro. See the attached workbooks and the sample code that follows:

Father Workbook in an ordinary code module (e.g. Module1):

Please Login or Register  to view this content.

Son Workbook in an ordinary code module (e.g. Module1):

Please Login or Register  to view this content.

------------------------

Typical flow of control in the son macro is as follows (non-working pseudocode):

Please Login or Register  to view this content.

Lewis

[mattress58]

I am trying to add this in my workbook. The question I have is : Is this looking for an error after the macro opens the other workbook or if there is an error opening the other workbook?

[LJMetzger]

If there is an error opening the other workbook, the Father Workbook (macro) will generate a runtime error.
For example, RunTime Error 1004 will occur if the file containing the Son Macro is NOT OPEN, when you try to call the Son Macro using Application.Run. This type of error is in the Father Workbook ONLY.

Once you are successful, calling the Son Macro, the Son Macro does whatever processing you tell it to do in the code. It is your responsibility to identify errors when the Son Macro runs. These errors can be Excel RunTime errors (e.g. Range("A33E") - illegal cell Address ), or data errors that you define such as a cell that should contain a number has text in it, or a cell that should contain data is empty.

For each of these errors in the Son Macro, you assign an arbitrary number. You return the error to the Father Macro, and the father macro displays the appropriate message.

I hope I understood your question. If not please ask for more help.

Lewis