Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2011
    Inside your mind

    Unanswered: Using Call Sub, but not running all of the code?

    Hi guys,

    Need a bit of help.

    I have two Subs, SubA and SubB.

    SubA is Public
    SubB is Private

    SubA contains a lot of code.
    SubB contains: Call SubA, and nothing else.

    I need to put a line of code in SubA, that only only runs when SubA itself is run, so it doesn't occur when SubB Calls it.

    Is this possible?

    I've tried putting a sub-Sub, and declaring it as Private (as I would do in Java - Well, kind of anyway), but VBA won't have any of it.

    Any ideas on how to sort this?

    What I'm doing is using the 'dropdown' method of a combo box, it needs to be used on the 'Change' (which is SubA), but not on the 'AfterUpdate' (which is SubB). I can't put it on the 'GotFocus' event, because it's part of a tabbed form, and is no.1 on the tab priority, and I don't want it to open everytime the tab is changed as it's a multicolumn combobox, and its contents is pretty huge.

    I realise I can copy and paste all the code from SubA, into SubB, whilst omitting the 'dropdown' method, but this violates best practice because it's repeating code. As there's quite a lot of it, and it's fairly complex, I don't want to have to make amendments twice all the time, and risk causing additional errors.

    Any advice/solutions, please fire away...

    Thanks in advance peeps!
    Looking for the perfect beer...

  2. #2
    Join Date
    Jan 2012

    You could declare a public variable in a class module along with your code for subA, as follows...

    Option Compare Database
    Public SubACheck As Boolean
    Public Sub SubA()
    If SubACheck = False Then
    MsgBox ("Code would run.")
    'Code to run here
    MsgBox ("Code would NOT run.")
    End If
    SubACheck = False
    End Sub
    Then to ensure the code will NOT run from SubB, your code would read as follows...

    Private Sub SubB()
    SubACheck = True
    Call SubA
    End Sub
    Hope this helps,



    Access 2000 User

  3. #3
    Join Date
    Mar 2009
    Provided Answers: 15
    You can also use an optional argument :
    Public Sub SubA([Other arguments (if any)], Optional Byval CalledBySubB as Boolean)
        If CalledBySubB = False Then  ' Code would run.
            'Code to run here
            ' Code would NOT run.
        End If
    End Sub
    Then in SubB:
    Private Sub SubB()
        Call SubA [Other arguments (if any)], True
    End Sub
    Note: Once you define one argument as optional, any arguments that follow it in the argument list must also be optional (see: Using Optional Arguments)
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts