Thread: 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.

Sub TimeTest()
Static MyTime As Date
Dim cnt As Long, j As Long, i As Long
cnt = 5    'this is just so the macro has something to do

MyTime = Time + TimeValue("00:00:10")

    For i = 1 To 2000000
        If Time > MyTime Then         'This is the key test
            MsgBox "Time exceeded, no match found"
            Exit Sub
        End If
            For j = 1 To 2000000
                cnt = cnt - 1         'meaningless subtraction
            Next j
        cnt = cnt + 1                 'meaningless addition
    Next i

End Sub

Move the MyTime variable up out and above the first macro and declare it as a Public variable. That seemed to work for me:

Option Explicit

Public MyTime As Date
Sub TimeTest()
Dim cnt As Long, j As Long, i As Long
cnt = 5    'this is just so the macro has something to do

MyTime = Time + TimeValue("00:00:10")

    For i = 1 To 2000000
        If Time > MyTime Then         'This is the key test
            MsgBox "Time exceeded, no match found"
            Exit Sub
        End If
        
        Call NumCount(2000000)        'call the function below
        
        cnt = cnt + 1                 'meaningless addition
    Next i

End Sub


Private Function NumCount(i As Long)
Dim j As Long
    For j = 1 To i
        If Time > MyTime Then         'This is the key test
            MsgBox "Time exceeded, no match found"
            Exit Function
        End If
    Next j
End Function

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)