+ Reply to Thread
Results 1 to 14 of 14

Re-arrange Macros order for quick editting and use, without using goto?

  1. #1
    Forum Contributor
    Join Date
    04-02-2017
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    403

    Cool Re-arrange Macros order for quick editting and use, without using goto?

    Hi guys,

    This question is in regards to the following macro (It does work on my worksheets).

    Please Login or Register  to view this content.

    The two parts highlighted in red need to be changed often to other formulas and then have the macro run on many different columns (achieved by changing Num1).
    QUESTION: Is there a way I could move these two highlighted red bits to the beginning of the macro or have them easily accessible. Or does someone have a better or smarter way to do this? Ideally I would place them both near where the "Num1" at the start of the code. (See where the XXX is)
    The only solution I can think of is something like the following, using "goto" which would be very slow to run and does not seem like a clean solution.

    Please Login or Register  to view this content.
    Thanks,
    Jimmy
    Last edited by JimmyWilliams; 12-12-2017 at 08:12 PM.

  2. #2
    Valued Forum Contributor ranman256's Avatar
    Join Date
    07-29-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2003
    Posts
    1,173

    Re: Re-arrange Macros order for quick editting and use, without using goto?

    1. never use Gotos.
    2. 'more accessible' makes no sense in a digital world when you can Ctl-F and jump to it.

  3. #3
    Forum Contributor
    Join Date
    04-02-2017
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    403

    Re: Re-arrange Macros order for quick editting and use, without using goto?

    Hi Ranman,

    Thanks for the comment but I am asking what to use instead of goto.

    Ctrl + F is an ok idea but only while I am using this macro all the time. I would like a way to be able to pick it up again in 12 months from now, and know exactly what bits need to be changed to do what I need.
    I am almost thinking of creating a userform type interface for it, but really if I am able to get the code to the start of the macro, all I would need is some comments for future notes and I'd be done.

    Thanks,
    Jimmy.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Re-arrange Macros order for quick editting and use, without using goto?

    Jimmy

    What is it you are looking for a 'smarter' way to do?

    Do you want to run a specific code based on criteria?
    If posting code please use code tags, see here.

  5. #5
    Forum Contributor
    Join Date
    04-02-2017
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    403

    Re: Re-arrange Macros order for quick editting and use, without using goto?

    Hi Norie,

    I think you're on the right path.

    I have a bunch of IF statements within a FOR loop.
    The IF statements determine what equation should be plugged into the cell. But it means I have those equations in between other bits of my code.

    I am using the same criteria multiple times to create many different equations. meaning I have to continuously find this code in the middle to edit it. It would be nice if I could take the part I keep editing and move it to the front of the macro, so I can access it quicker.

    Thanks Norie,
    Jimmy

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Re-arrange Macros order for quick editting and use, without using goto?

    Jimmy

    Why would moving that code to the 'front' make a difference?

    Could you explain in words what you are trying to do?

    Perhaps even upload a sample workbook with some dummy data and expected results.

    That might help us help you.

  7. #7
    Forum Contributor
    Join Date
    04-02-2017
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    403

    Re: Re-arrange Macros order for quick editting and use, without using goto?

    Example 20171214v08.xlsm


    See the Module: SummarySubtotalsGrowthz and the Sub SummarySubtotalsGrowth()

    Please Login or Register  to view this content.
    This code is right in the middle of my macro, it would be nice to have it at the start, so I can switch between the equations I want to use.

  8. #8
    Forum Contributor
    Join Date
    04-02-2017
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    403

    Re: Re-arrange Macros order for quick editting and use, without using goto?

    *Bumping this thread*

  9. #9
    Forum Contributor
    Join Date
    07-23-2016
    Location
    Texas
    MS-Off Ver
    2016
    Posts
    273

    Re: Re-arrange Macros order for quick editting and use, without using goto?

    GO TO's have their place such as on error go to! Also, using go to's to jump to an exit point in a macro is useful. Of course, use of go to's are subject to personal opinion and delcarations of never use Gotos should be qualified as personal opinion...by the way, I use to be a professional programmer at Pratt & Whitney Aircraft research center and made extensive us of Goto's both in Fortran, Basic, and Assembler.

  10. #10
    Forum Contributor
    Join Date
    04-02-2017
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    403

    Re: Re-arrange Macros order for quick editting and use, without using goto?

    Hi Joe,

    it's not that I have anything against GO TO's.
    Its' just that I feel like there must be a better way to achieve what I am after. Like having an another sub or a function or something.

  11. #11
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Re-arrange Macros order for quick editting and use, without using goto?

    Hi

    It seems to me that all you need are some String variables for your formulas. You can then assign values to the variables at the start of your code and refer to the variables in the body of the code.

    PS avoiding GoTo is a good habit. There are times to use it but they should be the exception not the rule, and you should always know why you're doing it. The issue with GoTo is not one of speed but that it tends to be used to create spaghetti code that is extremely hard to follow and maintain.
    Last edited by xlnitwit; 12-18-2017 at 03:12 AM. Reason: Typo and follow up
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  12. #12
    Forum Contributor
    Join Date
    04-02-2017
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    403

    Re: Re-arrange Macros order for quick editting and use, without using goto?

    Quote Originally Posted by xlnitwit View Post
    Hi

    It seems to me that all you need are some String variables for your formulas....
    That seems way to logical... Surely if it was that easy I would have thought of it in the first place.. However it seems like the right thing to do. I'll give it a crack and get back when I've got the time. but I think you're right...

    yep, I agree with all the comments regarding GoTo.

    Jimmy

  13. #13
    Forum Contributor
    Join Date
    04-02-2017
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    403

    Re: Re-arrange Macros order for quick editting and use, without using goto?

    Quote Originally Posted by xlnitwit View Post
    Hi

    It seems to me that all you need are some String variables for your formulas.....

    I just remember my original train of thought.
    For some reason (cant exactly remember),
    I wasn't able to other variables or strings at the start. I think its because some of the variables are undefined until half way through the code, which prevented me from moving the code to the front.. Or something like that.

  14. #14
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Re-arrange Macros order for quick editting and use, without using goto?

    Then I think you have answered your own question. If the values depend on other variables that you cannot declare/assign at the beginning of your code, then you cannot declare them at the start either. It may be that you could create separate functions for assigning the values, which would allow you to put the declarations and assignments at the beginning of the routine you are thinking of, but that's pure speculation at this point.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How can I arrange numbers in ascending order but....
    By mikehende in forum Excel General
    Replies: 2
    Last Post: 11-15-2016, 03:18 PM
  2. Macro to export to PDF but also re arrange the tab order
    By AnalystMH in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-25-2015, 12:18 PM
  3. Arrange modules in serial order in vba
    By mukeshbaviskar in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-15-2013, 02:56 AM
  4. Arrange in sequence order order whith selected filter
    By MAKEXAN12 in forum Excel General
    Replies: 9
    Last Post: 05-09-2013, 10:58 AM
  5. How to arrange in ascending order all subtotals
    By faisal123 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-17-2013, 06:12 AM
  6. Arrange columns into set order
    By stawil in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-16-2011, 09:02 AM
  7. arrange cell order
    By norika in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-28-2005, 04:59 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1