Calling Macro Inside Macro

Hi All,

I have Macros calling in a Macro, for example:

Sub Macro_All

Call Macro1
Call Macro2
Call Macro3

End Sub

Macro1, Macro2 and Macro4 each import a different text file from a designated folder and perform some operations. However, sometimes the file may not be available (which is ok), but in this case the code stops and there is an error.

I would like to make an adjustment such that if for example Macro2 does not work (because there is no file to import) it skips it and passes to Macro3 and so on.

Is this something I can do?

Many thanks,

Untested but wouldn't the on error resume next statement in each of the called macros allow it to return to the main macro to continue execution?

You could convert your subroutines to functions, which would allow you to return the completion status.

Sub Macro_All

if Macro1 then
    if Macro2 then
        if Macro3 then

        else
            ' report failure of macro3
        endif
    else
       ' report failure of macro2
    endif
else
    ' report failure of macro1
endif
End Sub

Sub Macro1()

' your code

End Sub

as a function it would be something like this. The important part is how you are currently handling the error. You can incorporate in to that the setting of the completeStatus variable, which is returned to the calling routine.

Function Macro1() as boolean

    Dim completedStatus as boolean

    completedStatus=true
    ' your code
    ' if there is an error or no file then
    completedStatus = false

' return value if all good
    Macro1 = completedStatus

end function