Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Dec 2015
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Question Loop code from one sub into multiple other subs automatically

    Hi

    Im struggeling to create a subroutine that is performing one command for multipel other subroutines.
    Ive been trying to make some sort of a loop without succcess.


    The function I want to create can be simplified to this example:

    Sub commandline()
    range("A1") =1
    end sub


    sub main1()
    'macro
    end sub

    sub main2()
    'macro
    end sub

    sub main3()
    'macro
    end sub

    Etcetera.. So my goal is to get the code in the commandline sub to trigger automatically in all main subs without writing the code in the main subs

    All help would be very much appreciated!

  2. #2
    Lounger
    Join Date
    Dec 2009
    Posts
    27
    Thanks
    2
    Thanked 10 Times in 4 Posts
    Just call one sub from another. So Main1 would look like:

    sub main1()
    ...
    commandline
    ...
    end sub

    when main1 reaches the call to commandline it will execute the code in that then continue with its next line. Variable you declare in main1 will not be visible in commandline and vice-versa.

  3. #3
    New Lounger
    Join Date
    Dec 2015
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Yes that is an option but imagine if you have 1000 main subs, then I still need to copy the commandline into every single one of the main subs.
    Therefore It would be of tremendous help if I could write one subroutine that gets applied to all the main subs directly. I hope there is a way for this :/

  4. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Quote Originally Posted by rolunor View Post
    Yes that is an option but imagine if you have 1000 main subs, then I still need to copy the commandline into every single one of the main subs.
    Therefore It would be of tremendous help if I could write one subroutine that gets applied to all the main subs directly. I hope there is a way for this :/
    If you'd have 1000 main subs, I'd say your code would have a serious organizational problem... That would be completely absurd.

    There no direct ways to do what you need to do. Unless the number of subroutines is big, the previous suggestion seems fine to me.
    Rui
    -------
    R4

  5. #5
    New Lounger
    Join Date
    Dec 2015
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I have a lot of images and each image have a macro assigned to it and therefore there are a lot of "main subs" in my program.
    The task of each main sub is to update a filter and hide raws in excel. I also added some extra functions in the macro for each main sub and for some simple tasks which are reacurring in all main subs it would have been nice to find some sort of more efficient way codewise and also timesaving solution.

    An image of the program where each green "checkmark" has a main sub asigned:

    sampleimage.jpg

  6. #6
    Lounger
    Join Date
    Dec 2009
    Posts
    27
    Thanks
    2
    Thanked 10 Times in 4 Posts
    It will be quite a lot of work to change it, but it will be more maintainable if you put all your code in a single Sub with a parameter. Wriet something like this:

    Sub DoTheAction(sFiltername As String)

    'general actions always done here

    Select Case sFiltername
    Case "1"
    'specific stuff for image 1
    Case "2"
    'specific stuff for image 2
    Case Else
    'default actions if you got here without clicking an image
    End Select
    End Sub

    then set the event for each of your images as something like:

    Sub Picture1_Click()
    DoTheAction "1"
    End Sub


    Use a more meaningful description than "1" or "2" in your strings. The advantage of this is that you only have one procedure to maintain.

  7. The Following User Says Thank You to Mr Humbug For This Useful Post:

    rolunor (2016-01-22)

  8. #7
    New Lounger
    Join Date
    Dec 2015
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Mr Humburg I will try it out to improve my code. Its most likely worth to do a rework in my code like that

  9. #8
    5 Star Lounger Lugh's Avatar
    Join Date
    Jun 2010
    Location
    Indy
    Posts
    620
    Thanks
    166
    Thanked 76 Times in 67 Posts
    Quote Originally Posted by Mr Humbug View Post
    sub main1()
    ...
    commandline
    ...
    end sub
    Quote Originally Posted by rolunor View Post
    imagine if you have 1000 main subs, then I still need to copy the commandline into every single one of the main subs.
    If you have [or can get] all your Main Subs in one or a few modules, which don't have any other kinds of Subs in them, then perhaps a global replace of a common phrase in all the Mains might help? So eg:

    Replace
    end sub
    with
    commandline
    end sub

    This might work better in a text editor.
    Lugh.
    ~
    Windows 10 Pro x64 1607; Office 2016 (365 Home) x32; Win Defender, MBAM Pro

    ASRock H97 Anniversary; Xeon E3-1231V3 (like i7)
    Gigabyte GeForce GTX 970; 12GB Crucial DDR3 1600
    Logitech MX Master mouse; Roccat Isku kb

  10. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Is the code in the main subs for the images similar enough that you need to have only one main sub and within the routine and then cycle through the images?

    Code:
    Public Sub DoSomething()
    For I = 1 To 20 'NUMBER OF IMAGES
        ActiveSheet.Shapes.Range(Array("Picture " & I)).Select
        'RUN SOME CODE HERE TO PERFORM AN ACTION
    Next I
    End Sub
    You can have an If statement(s) if needed to handle code variances between images. For example:

    Code:
    Public Sub DoSomething()
    For I = 1 To 20 'NUMBER OF IMAGES
        ActiveSheet.Shapes.Range(Array("Picture " & I)).Select
        'RUN SOME CODE HERE TO PERFORM AN ACTION
            if I=4 then  'IF THE FOURTH IMAGE IS SELECTED, DO THIS ELSE DO THAT
                Selection.ShapeRange.IncrementLeft 170.25
                Selection.ShapeRange.IncrementTop 0.75
            Else:
                Selection.ShapeRange.IncrementLeft 170.25
                Selection.ShapeRange.IncrementTop 0.75
            End IF
    Next I
    End Sub
    HTH,
    Maud

Posting Permissions

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