Run code after set period of time

EssKayKay

Active Member
Joined
Jan 5, 2003
Messages
407
Office Version
  1. 2007
Platform
  1. Windows
Hello,
I’m not sure this is even possible but I’ll start here.

I have a routine (cmdPmtMade_Click) that for the most part works fine. However, as the routine is run numerous times the whole process begins to slow down. Over time, what initially takes a couple seconds to run can progress to nearly a minute. I have tried to see what is causing this but was unsuccessful in my review.

I have a second routine that basically copies all the data from one worksheet to another then re-saves the file under the original name. After running this “Repair” sub, the “PmtMade” sub (and others) works great for awhile until it again begins to slow down. If I rerun the REPAIR, all is fine again for some time.

What I was thinking of is some way that if the original routine (PmtMade) takes more that X number of seconds, a message box would appear prompting the user to run the “Repair” routine to speed up the process.

So, my question – Is there some command that when “PmtMade” button is clicked after a specified time (say 10 seconds), will trigger some other code or message box ? I tried Application.OnTime but could not get that to work (I probably had something set wrong).

Thanks for viewing,
Steve K.
 
I've changed the placement of the Refresh call. I think the above will not work.

VBA Code:
Private Sub cmdPmtMade_Click()

ThisWorkbook.x = ThisWorkbook.x + 1 



If Sheets("Amortize").Columns(17).Hidden = False Then 'MsgBox "Visible"
Range("A4,H5").Select
Range("H5").Activate
End If

Application.ScreenUpdating = False
Application.EnableEvents = True

Range("OneTime") = "---"
Range("TempCell") = "Pmt_Made"

Call OneTimePmt1
Call OneTimePmt2

If Range("F6") <= 0 Or Range("F7") <= 0 Or Range("F8") <= 0 Or Range("F10") <= 0 Then
  MsgBox " Missing Loan Information." & vbNewLine & _
  " Enter Loan Info criteria. . .", , " - Loan Info -"
  If Range("F10") = "" Then Range("F10").Select
  If Range("F8") = "" Then Range("F8").Select
  If Range("F7") = "" Then Range("F7").Select
  If Range("F6") = "" Then Range("F6").Select
Protect_It

If ThisWorkbook.x = 5 Then Refresh_ALL   'This reference may need to change depending on code placement

Exit Sub
End If

'======== Added 0/06/24 ==========
If Range("M34") > 0 Then Formulas2Values '<=== check this for editing
  PaymentSetup
  UpdateOneTimePmt
  ResetPITI
  OneTimeDATE
Range("M31").End(xlDown).Offset(1, 0).Select

'--- Set selected Pmt_Date at screen center ---
If Range("M45") > 0 Then
  If ActiveCell.Row > 13 Then ActiveWindow.ScrollRow = ActiveCell.Row - 13
     Range("M2035").End(xlUp).Offset(1, 0).Select
  End If

UnProtect_It
Range("F29").ClearContents
Range("F29") = "Ready"

If ThisWorkbook.x = 5 Then Refresh_ALL   'This reference may need to change depending on code placement

Application.EnableEvents = True
Application.ScreenUpdating = true

End Sub
 
Upvote 0
Solution

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Thank you Skybot for your quick response and support. This is working quite well. I have a little to add but I am very close. If I run into problems I'm sure I will be back but this looks promising.

Side Note - actually, I believe both routines worked fine but I settled on your second revision.

Again, much appreciated. . .
Steve K.
 
Upvote 0
I was having a problem marking this as solved but I think I got it.
Once again, thanks. . .
 
Upvote 0
Hello all,
I am back on this issue once again.

SkyBot’s routine works pretty good (thank you SkyBot). However, I think what would work better for me is if there is some other way to prompt me (or better, run another macro) after a set time length, not the number of times the original routine is run.

What I have in mind is some way to determine how long a sub routine takes to run including any calls. If so, then post that time to some cell.
As example, let’s say the routine took 10.5 seconds to complete, then update cell "P1" with 10.5. If that’s possible, I believe I could make this work.

Again, thanks for viewing,
Steve K.
 
Upvote 0
I think I found what I was looking for. Here's the link:

VBA Code To Calculate How Long Your Macro Takes To Run

There are various scenarios where you may want to calculate the time it takes for your VBA code to run. I often do this when trying to test various ways to code a procedure. I like to see which method gives me a faster run time.
www.thespreadsheetguru.com
www.thespreadsheetguru.com


Rich (BB code):
Sub CalculateRunTime()      '--- in seconds ---
'PURPOSE: Determine how many seconds it took for code to completely run
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

Dim StartTime As Double
Dim SecondsElapsed As Double

'Remember time when macro starts
   StartTime = Timer

'*****************************
    'Insert Your Code Here...

'*****************************

'Determine how many seconds code took to run
    SecondsElapsed = Round(Timer - StartTime, 2)

'Notify user in seconds
   MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
   Range("P1") = SecondsElapsed
End Sub


Thank you all. Hopefully this does what I want. Any other problems, I'm sure I'll be back.
Steve K.
 
Upvote 0

Forum statistics

Threads
1,224,568
Messages
6,179,580
Members
452,927
Latest member
whitfieldcraig

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top