+ Reply to Thread
Results 1 to 26 of 26

Time limit on Code Execution

  1. #1
    Registered User
    Join Date
    06-26-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    22

    Time limit on Code Execution

    Hi guys,

    I'm just wondering if there's a code where i can put a time limit on certain code execution.

    For example,

    I have a function which will allow me to search for combination of numbers which will sum to zero. However, because the range could be as long as 300 rows or even the possibility of not finding the combination that will sum to zero, the macro will take ages to complete or even causes the whole excel to hang. So What i want is to instruct macro to stop doing the search if it cannot find the results after say 20 minutes of searching.

    Is that possible?
    Last edited by bgunawan; 07-15-2009 at 07:47 AM. Reason: Issue Solved

  2. #2
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    Re: Time limit on Code Execution

    Could you use this:
    Please Login or Register  to view this content.
    Looking for great solutions but hate waiting?
    Seach this Forum through Google

    www.Google.com
    (e.g. +multiple +IF site:excelforum.com/excel-general/ )

    www.Google.com
    (e.g. +fill +combobox site:excelforum.com/excel-programming/ )

    Ave,
    Ricardo

  3. #3
    Registered User
    Join Date
    06-26-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    22

    Question Re: Time limit on Code Execution

    Hi rwgrietveld,

    I'm going to give it a shot.

    Just want to confirm though.

    Seconds = (1 / 24 / 60 / 60) ' indicates 1 day divided by 24hrs divided 60 mins and divided again by 60 seconds to arrive at per second.

    Hence if i want to use minutes instead, it will be:

    Minutes = (1 / 24 / 60)

    EndTime = Now() + 5 * Minutes ' 5 minutes

    is that right? also should i insert my code so that it will stop?

    is it after the line while Now()<EndTime?

  4. #4
    Registered User
    Join Date
    06-26-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    22

    Question Re: Time limit on Code Execution

    Hi rwgrietveld,

    I've tried your code. Although it works as desired, it does not stop the macro when I insert my code after while Now()<EndTime line.

    It only start counting after my code has finished running. What i want is for this counter to stop the macro once the specified time limit has expired.

  5. #5
    Registered User
    Join Date
    06-26-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Time limit on Code Execution

    Perhaps I should be a little bit more descriptive.

    In the spreadsheet that I have attached, there are 2 sheets which have been labelled. One named "Solution found" and the other "solution cannot be found".

    When I run the below code, despite taking ages to run, the macro can provide me with the relevant combination of quantity will sum to zero. This is the one listed in sheet("solution found"). With the other sheet, however, it also take ages but it can never finish executing. It almost appears that the macro has hang. I have even tried executing it overnight and it still looks as if it is running. I do realise that if i try to find the combination myself, it doesn't appear to have any combination that will sum to zero.

    So what I want is to add a code so that when the below code is being executed, it can only run for e.g. 20 minutes. If after, 20 minutes it hasn't found a solution yet, just abort and resume next.

    The below code was copied from someone and I have tweak it a bit so that it works along with my other codes. Hence, just wondering if there is such a code.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by bgunawan; 07-09-2009 at 12:33 AM. Reason: thread posted in the wrong forum

  6. #6
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: Time limit on Code Execution

    hi,

    I've come looking from the below link to see if I can help optimise your code but I can't access the link you've provided (in post 6 of this thread) as it states "no thread specified" & I can't find other possible threads against your username using the site's Search tool.

    Can you please re-upload your sample file with all the current code (& all relevant layout*) included?
    Also, can you please include column headers to assist with clarity?
    *This looks reasonably different when compared to the layout in your other thread(http://www.excelforum.com/excel-prog...g-of-data.html).

    btw, This thread is currently in the Excel Programming forum which I think is a valid location for it. Has it been moved?


    Rob
    Rob Brockett
    Kiwi in the UK
    Always learning & the best way to learn is to experience...

  7. #7
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Time limit on Code Execution

    This is just air code, but you can probably do this yourself once you understand my suggestion.

    1) When you first start your macro, declare a variable and set it to NOW + 20 minutes.
    Please Login or Register  to view this content.
    2) Your code has several "loops" in it where you're doing stuff, inside the loops include a timecheck to see if time is up.
    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  8. #8
    Registered User
    Join Date
    06-26-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Time limit on Code Execution

    Rob,

    Yes, i believe the moderator has moved my thread. It was originally posted under Access Programming.

    Also, I believe my code and sample file have been uploaded in this thread. Can't you access it? The post which contain the sample and code is the one above your reply. If not, I'll upload it again.

  9. #9
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: Time limit on Code Execution

    hi,

    My request for you to re-upload the complete file (including the code - as part of the file) was because the uploaded file in post # 5 only appears to contain results not the initial information/layout which exists in your other thread. (I'm here, based on the other thread, to see if I could help optimise your code so that an Over-ride Timer (the subject of this thread) wouldn't be needed at all.)

    We are more likely to be able give a complete answer when there is a more complete framework, rather than trying to put everything together from different sources/posts. If you would like me to try & help, can you please upload a new sample file including:
    1) raw data with headers on each sheet to provide context,
    2) your latest code (as part of the file rather than in the thread) &
    3) expected results with some brief explanation of the necessary logic?

    Thanks
    Rob
    Last edited by broro183; 07-09-2009 at 06:47 PM. Reason: attempt at improving clarity

  10. #10
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Time limit on Code Execution

    Hi

    Another option. Rather than using a time limit, how about using an iteration limit. As JBeaucaire has pointed out, you have various loops in your code. If you create some sort of global counter, you can increment it in all the loops that are of significance, and when it reaches the predefined limit (10,000 or 100,000), then it will stop and move to the next item.

    rylo

  11. #11
    Registered User
    Join Date
    06-26-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    22

    Question Re: Time limit on Code Execution

    Rob,

    As requested, I have uploaded a new sample file for you. I have also added some comments, so hopefully it should be clear. Let me know if you need further clarification.

    The reason why I want this time limit on code execution is if you try to run this macro on the data in Sheet("solution not found"), not only does it take ages, it almost appears that the macro hangs.

    The macro code was copied from someone else and I merely tweak it a bit in order for it to flow with my other codes. 98% of the code has not been edited.

    Perhaps you could shed some light on how to improve the macro. Note, however, the raw data may go up to 400 rows or even more. It all depends on the data generated by the report on that day.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    06-26-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    22

    Smile Re: Time limit on Code Execution

    Hi JBeaucaire & rylo,

    I'm trying to play around with that idea and I'll let you guys know how I go.

    Thanks beforehand.

  13. #13
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: Time limit on Code Execution

    hi Bgunawan,

    I'm sorry I was mistaken, after looking at the code you have provided in the sample file of post #11 (after a rough Babelfish translation for the comments ) I don't think I will be able to optimise it much (although I'll try...). This code is definitely written rather than recorded & may help me learn as I play.

    - Goodluck with your development

    Btw, I could easily improve the speed of the macro code that you posted in the other thread, which I guess is effectively the manipulation/preparation macro to get your extract into the format for running the "FinalSubset" macro of this thread. If you would like help optimising the preparation macro, feel free to start a new thread with the sample file & then p.m. me a link to the thread.

    Rob

  14. #14
    Registered User
    Join Date
    06-26-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    22

    Wink Re: Time limit on Code Execution

    Rob,

    No worries mate. Still want to thank you for having a look.

    The code posted in the other post is not part of this code. It's totally different assignment altogether. I appreciate your offer with the other code, however, our company is upgrading the system soon, hence that code is only needed temporarily.

    Thank you once again.

  15. #15
    Registered User
    Join Date
    06-26-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    22

    Question Re: Time limit on Code Execution

    Hi JBeaucaire,

    I do understand your suggestion. However, my only problem is I don't where to put the code so that it works. I have tried putting it in a number of places but all seem to fail.

    So it'd be much appreciated if you could shed some light.

    Thanks.

  16. #16
    Registered User
    Join Date
    06-26-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    22

    Wink Re: Time limit on Code Execution

    Hi rylo,

    Although your iteration limit suggestion may be an alternative solution, I don't think it'd be suitable in this instance as the code may freeze in the first instance. Maybe I'm wrong but when the code runs, it appears that way.

  17. #17
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Time limit on Code Execution

    I can show you how with a simple macro. I basically set the MyTime value at the top of the macro, in the example here I'm adding 10 seconds from the time we start the macro. Then inside the FOR/NEXT loop(s), the first thing I do is check if the current time is greater than the stored MyTime limit. If it is, it presents a message and aborts.

    So, all through your macro you need to insert this into the beginning of the loops so each time through the loops it checks the time before running its other commands.

    NOTE: The MyTime variable in my sample is declared as a Static variable, so it should be available to all other macros and functions.

    Once you get this to work with a short time (10 seconds in my example below) in your macro, then you can change it to 20 minutes.
    Please Login or Register  to view this content.
    Last edited by JBeaucaire; 07-13-2009 at 02:06 AM.

  18. #18
    Registered User
    Join Date
    06-26-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    22

    Smile Re: Time limit on Code Execution

    Hi JBeaucaire,

    It appears that the value of MyTime variable does not get carried over to the Functions. When I debug the code in Function, it indicates MyTime = Empty.

    I have even tried inserting the whole code in Function but the it appears that MyTime is not static, it keeps changing as the loop in the Function goes to the very 1st line of the code, hence MyTime variable keeps on changing and the message will never be triggered.

    Please feel free to throw in more ideas. I think your current idea should work if the value of MyTime can be carried over to the Function. I'll do some research online as well to see if I can find other solution to this problem.

  19. #19
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Time limit on Code Execution

    Move the MyTime variable up out and above the first macro and declare it as a Public variable. That seemed to work for me:
    Please Login or Register  to view this content.

  20. #20
    Forum Contributor
    Join Date
    02-23-2006
    Location
    Near London, England
    MS-Off Ver
    Office 2003
    Posts
    770

    Re: Time limit on Code Execution

    Looking at the original macro code it is massively recursive.
    Is the aim of the macro to find not only a pattern of numbers that SUM's to zero, but that also uses the maximum number of the values from the input list as possible?
    Otherwise should the macro not just find "-104408" and "104408" and present you with that?
    If you find the response helpful please click the scales in the blue bar above and rate it
    If you don't like the response, don't bother with the scales, they are not for you

  21. #21
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: Time limit on Code Execution

    hi all,

    I've had a go at modifying this (based on the existing code & the principle suggested by JB), but I haven't been successful so far (see my module called "modifiedtranslation").

    In the scheme of things a small gain (in theory anyway!), is moving the use of "redim preserve" outside the loop in the initial macro & instead using a variant array to speed up the creation of the input values. This is on the basis that the initial data in the sheet should be valid (or at least validated) prior to the macro being run.


    As Phil states, it is massively recursive & to see if I could find some other approaches, I Google searched for the macro's name ("Public Function Kombinationen("). I found one use of it as a proposed solution in an old monthly challenge on MrExcel.com (http://www.mrexcel.com/pc09.shtml). I've included the code of the eventual challenge winner & a runner up (Ioannis & Tushar Mehta) in separate modules but have not tried to adapt them to your spreadsheet layout. Modifying them to your file could take a while, but that would be a "one-off" time/cost & potentially they may work even better than what you currently have.

    I've uploaded my sample file in case anyone's interested in investigating further using Tushar or Ioannis's code...


    Goodluck,
    Rob
    Attached Files Attached Files

  22. #22
    Registered User
    Join Date
    06-26-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Time limit on Code Execution

    Hi Phil_V,

    That is right. The reason why I've chosen to include this macro is because it uses the maximum number of the values from the input list that will sum to zero. This aim is crucial for the purpose of my objective.

  23. #23
    Registered User
    Join Date
    06-26-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    22

    Smile Re: Time limit on Code Execution

    Hi JBeaucaire,

    fantastic work mate. It worked. Extremely impressed. Thank you so much for your help.

  24. #24
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Time limit on Code Execution

    If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


    (Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

  25. #25
    Registered User
    Join Date
    06-26-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    22

    Smile Re: Time limit on Code Execution

    Hi JBeaucaire,

    No worries, I have already leave reputation feedback for you as soon as the macro worked.

    Thank you once again.

  26. #26
    Registered User
    Join Date
    06-26-2009
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    22

    Smile Re: Time limit on Code Execution

    Hi Rob,

    THank you for your help too. I have seen that macro but i cannot remember why I have chosen the one that I have over the macro that you have attached. I'll still try to play around with yours again and see which one is better.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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