# Time limit on Code Execution

1. ## 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?

2. ## Re: Time limit on Code Execution

Could you use this:
``Please Login or Register  to view this content.``

3. ## 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. ## 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. ## 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.``

6. ## 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?
*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

7. ## 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.``

8. ## 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. ## 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

10. ## 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. ## 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.

12. ## 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. ## 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.

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. ## 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. ## 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. ## 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. ## 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.``

18. ## 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. ## 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. ## 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?

21. ## 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

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. ## Re: Time limit on Code Execution

Hi JBeaucaire,

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

24. ## 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. ## 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. ## 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.

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

#### 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