none
Canceling a routine from within a nested sub routine RRS feed

  • Question

  • I have Excel VBA macro1 that calls macro2 that calls macro3.  Macro3 checks for a condition and if met executes the Exit Sub statement.  Each of the macros is stored in a different module in the same VBA project.  I declare a bunch of arrays, workbooks and worksheets, mostly as  globals in a different module (same project).

     

    If the condition x = y is met I want the whole routine to stop and I want all the variables reset. I want the thing to die!

     

    But when I run it after it’s run and exited, it’s still somehow affected by the initial run. I can fix this by manually clicking the Reset command on the VBA toolbar in between runs, but the users won’t be able to do that.  How can I get this routine to truly kill itself when x = y in macro3? 

     

    I also need it to leave the workbook open, so closing the workbook isn’t a viable option.  Any ideas?

     

     

    Option Explicit

    Option Compare Text

     

    Public x as integer

    Public y as integer

    Public bn_Cancel as Boolean

     

    Sub macro1()

    Code lines here

    Macro2

    If bn_Cancel = true then Exit Sub

    More code lines here

    End Sub

     

    Sub macro2()

                    Code lines here

                    Macro3

                    If bn_Cancel = true then Exit Sub

    End sub

     

    Sub macro3()

                    If x = y then

                                    bn_Cancel  = true                           

    Exit sub

    End if

                    More code lines here

    End Sub

     

    Wednesday, April 3, 2013 4:42 AM

Answers

  • You should initialize bn_Cancel to False in macro1, so that you start with a clean slate:

    Sub macro1()
        bn_Cancel = False
        'Code lines here
        Macro2
        If bn_Cancel = true then Exit Sub
        'More code lines here
    End Sub


    Regards, Hans Vogelaar

    • Proposed as answer by Shasur Wednesday, April 3, 2013 12:48 PM
    • Marked as answer by tmhtraining Wednesday, April 3, 2013 2:16 PM
    Wednesday, April 3, 2013 6:17 AM

All replies

  • You should initialize bn_Cancel to False in macro1, so that you start with a clean slate:

    Sub macro1()
        bn_Cancel = False
        'Code lines here
        Macro2
        If bn_Cancel = true then Exit Sub
        'More code lines here
    End Sub


    Regards, Hans Vogelaar

    • Proposed as answer by Shasur Wednesday, April 3, 2013 12:48 PM
    • Marked as answer by tmhtraining Wednesday, April 3, 2013 2:16 PM
    Wednesday, April 3, 2013 6:17 AM
  • That did the trick. Many thanks.
    Wednesday, April 3, 2013 2:16 PM