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?

Could you use this:

Please Login or Register  to view this content.

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.

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

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.

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

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

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.

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.

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?

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

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)