une


I have written some text analysing code that takes a long time to execute if the text selection made by the user for analysis by the macro is too large. Therefore I would like to add the following functionality to the macro;

If after 15 seconds of execution the macro has not completed, I would like execution to halt and output a message to the user asking them to reduce the size of the selection they have made before running the macro again. How could I do this




Re: Timer on macro execution run time

Andy Pope


You will need to store the start time in a variable and then whilst looping in your code check the difference between now and the stored time.

You would use an IF THEN statement to compare the time difference and then use a msgbox to inform the user of the future considerations.







Re: Timer on macro execution run time

une

Once again, thanks a lot. You have helped me so much.




Re: Timer on macro execution run time

une

I tried your idea with limited success. It is a simple concept but perhaps I have implemented it incorrectly. I declared variables as follows;

Dim startTime As Double
Dim nowTime As Double
Dim elapsedTime As Double

Then at the start of the Sub added the statement;

startTime = Second(Now)

Then in each of the complex loops I added the following immediately after the loop start;

nowTime = Second(Now)

If nowTime - startTime > 15 Then

MsgBox "Your selection is too large or complex" & vbCrLf & _
"for your system to analyze in less than" & vbCrLf & _
"15 seconds." & vbCrLf & vbCrLf & _
"Please try again with a smaller selection."

This seemed to work sometimes, but not often. I watched a macro take 2 minutes to execute and no warning appeared.

So I tested the end of the sub to see what the elapsed time was as follows;

nowTime = Second(Now)
elapsedTime = nowTime - startTime
MsgBox elapsedTime

This output a value of 10, even though the macro took 2 minutes to execute. I can't see how the elapsed time could only be 10, it had to be at least 120. I am unsure about what went wrong here. Strangley when I make a really big selection and make the macro run for a few minutes, elapsedTime comes back as a negative number sometimes

PS - sorry for the formatting of my posts. This text editor seems to have a mind of its own, indenting at will in unpredictable ways that cannot be reversed. How do I tag code so it is formatted nicley in posts





Re: Timer on macro execution run time

une

Here's a guess, maybe the clock is resetting itself to zero after every 60 seconds So maybe it is not a clock timer as such but a 60 sec looping timer. If I am right, how can I implement a timer that runs like a normal clock/stopwatch



Re: Timer on macro execution run time

une

It seems that's it. I got around this by using the following statement to create a number representing the current time;

timeInSecondsVariable = Year(Now)*365*24*3600 + Hour(Now)*3600 + Minute(Now)*60 + Second(Now)

Hopefully Now uses 24 hour time, oh now I think about it, I will have to add Day(Now)*24*3600 so you don't get caught out at midnight!

I hope there is nothing between Year and Day. Bring on the next millenium bug!

There has to be a timer available of some sort to avoid all this hassle I would guess. Is there





Re: Timer on macro execution run time

Andy Pope

Here is a very simple example.

You need 2 userforms and a standard code module.

In the standard code module add this code. Userform2 will be displayed, this can contain your wait message. The loop will ask every 2 seconds whether to continue or not.

The key to being able to display the userform and execute code is the Modeless state of the userform. This requires office 2000 or above.

Public g_datStartTime As Date
Sub Main()

Dim lngLoop As Long

g_datStartTime = Now

UserForm2.Show vbModeless
DoEvents

For lngLoop = 1 To 3999999
If Now - g_datStartTime > TimeValue("00:00:02") Then
If MsgBox("Loop has taken 2 seconds. Do you wish to continue", vbYesNo Or vbQuestion) = vbNo Then
Unload UserForm2
Exit Sub
Else
g_datStartTime = Now
End If
End If
Next

Unload UserForm2
UserForm1.Show

End Sub






Re: Timer on macro execution run time

une

Thanks. I will analyze your code and look into the "Modeless state". In your example could UserForm2 contain a Command Button (along with the wait message) that if clicked would halt the macro Then the user could exit the macro at any time, sort of like a more refined Ctrl-Break substitute. If so, what statement would you put in the Command Button's code With your help my little program is very close to being up and running now. I hope Bill sends you a present.



Re: Timer on macro execution run time

Andy Pope

You can use a public variable as a flag to capture user pressing the button on userform2

Standard code module.

Public g_datStartTime As Date
Public g_blnUserCancel As Boolean
Sub Main()

Dim lngLoop As Long

g_datStartTime = Now
g_blnUserCancel = False

UserForm2.Show vbModeless
DoEvents

For lngLoop = 1 To 3999999
If Now - g_datStartTime > TimeValue("00:00:02") Or _
g_blnUserCancel = True Then
If MsgBox("Loop has taken 2 seconds. Do you wish to continue", vbYesNo Or vbQuestion, _
IIf(g_blnUserCancel, "User Cancelled", "Time Out")) = vbNo Then
Unload UserForm2
Exit Sub
Else
g_datStartTime = Now
g_blnUserCancel = False
End If
End If
DoEvents
Next

Unload UserForm2
UserForm1.Show

End Sub



Userform2 code module where userform contains a commandbutton.

Private Sub CommandButton1_Click()

g_blnUserCancel = True

End Sub