Run Sub automatically on periodic basis

Hi,

I am using Excel 2007.  I have written a Subroutine that I want to run 
automatically once every thirty seconds without any user interaction.  Can 
anyone tell me how I can set this up?

Thanks in advance,
Paul

-- 
Paul Kraemer
0
Utf
2/1/2010 7:23:01 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
733 Views

Similar Articles

[PageSpeed] 24

This code will run your procedure when the workbook has been opened.  At the 
end of your code the OnTime event will schedule another run of your code 30 
seconds later.  Thus, it will continue to loop until you close the workbook.  
Hope this helps!  If so, let me know, click "YES" below.

Private Sub Workbook_Open()
    Call my_Procedure
End Sub

Sub my_Procedure()

    ' your code here
    
    Application.OnTime EarliestTime:=Now + TimeValue("00:00:30"), 
Procedure:="my_Procedure"

End Sub
-- 
Cheers,
Ryan


"Paul Kraemer" wrote:

> Hi,
> 
> I am using Excel 2007.  I have written a Subroutine that I want to run 
> automatically once every thirty seconds without any user interaction.  Can 
> anyone tell me how I can set this up?
> 
> Thanks in advance,
> Paul
> 
> -- 
> Paul Kraemer
0
Utf
2/1/2010 8:03:01 PM
You provided no code for stopping the proc from rescheduling itself.
To end an OnTime event, you must provide it the EXACT time that it is
scheduled to run. Since you use Now+TimeValue(), you have no way of
getting the scheduled run time.  Instead, you should save the time
value in a module-scoped variable and pass the value of that variable
to OnTime. E.g.

Dim RunWhen As Double
Sub my_Procedure()
	' your code here
	RunWhen = Now + TimeSerial(0,0,30)
	application.ontime  EarliestTime:=RunWhen,  _
		Procedure:="my_Procedure"
End Sub

Sub StopOnTime
	Application.OnTime earliesttime:=RunWhen, _
	    procedure:="my_Procedure", schedule:=False
End Sub

See www.cpearson.com/Excel/OnTime.aspx for more detail about working
with OnTime and Windows timers.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]




On Mon, 1 Feb 2010 12:03:01 -0800, Ryan H
<RyanH@discussions.microsoft.com> wrote:

>This code will run your procedure when the workbook has been opened.  At the 
>end of your code the OnTime event will schedule another run of your code 30 
>seconds later.  Thus, it will continue to loop until you close the workbook.  
>Hope this helps!  If so, let me know, click "YES" below.
>
>Private Sub Workbook_Open()
>    Call my_Procedure
>End Sub
>
>Sub my_Procedure()
>
>    ' your code here
>    
>    Application.OnTime EarliestTime:=Now + TimeValue("00:00:30"), 
>Procedure:="my_Procedure"
>
>End Sub
0
Chip
2/1/2010 8:35:26 PM
Reply:

Similar Artilces:

Update Links Automatically
Running Excel XP and was wondering is there a setting which will automatically update any external links that I have referenced, without the dialog option of "Do you want to update links" etc Thanks There's a setting you can toggle. Tools|options|edit tab Uncheck "Ask to update automatic links" Then the prompt goes away, but the links are updated. This is your own setting--it affects all the workbooks you open, but won't affect anyone else when they open that workbook. (They'd have to change their setting, too.) John wrote: > > Running Excel XP...

microsoft excel does not calculate
In large workbooks (> 10MB) I found that often Excel does not calculate. I have to enter the cursor in the cell (or the formula bar) and press enter, so that the formula is recalculated. However, my calculation is set on automatic (Tools/Options/Calculate etc.). Does somebody know why this happens and has an answer? Try the last option on this page and see if it is likely to be your issue:- http://www.decisionmodels.com/calcsecretsf.htm -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 --------------------...

check instance running
My application will call another application and would like check is it running or not. Are there any function to check the instance already running? Your information is great apprecaited, Check http://www.mvps.org/access/api/api0007.htm at "The Access Web" -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "iccsi" <inungh@gmail.com> wrote in message news:d755e8d9-8e1b-414f-a115-ae450d48075f@y17g2000yqd.googlegroups.com... > My application will call another application and would like check is > i...

Run Time Error '1004' Application or Onject-defined
Hi all. I am having a strange problem with the below code in that it produces the run time error when trying to Paste the Selection. However, if I perform the action manually in exactly the same way, as in recording the macro in the first place, it works!? How can I get this to run - it is after all, a really simple code. (Most frustrating) Thanks, Wes. Windows("Transaction_Record.xlsm").Activate Sheets("DETAILED_PMNTS_REC").Select Range("N2077:QV2089").Select Application.CutCopyMode = False Selection.Copy Windows("AUDIT_TR...

outlook running on XP Pro in workgroup mode prompts for password on exchange 2003
we have a windows 2000 network with exchange 2000 local user are members of the domain, remote offices connect via Lan to Lan VPN with 5 or more computers running in workgroup mode. The users in the workgroup have local accounts on there XP pc that match the domain username/password. Currently they local to the exchange server with any issues. Outlook passes there username/password to the exchange server and logs then on. I have added a windows 2003 server and promoted it to a backup ADS server, added another windows 2003 server as a member server. Installed Exchange 2003 SP2. Move all the lo...

Running a .bat file with spaces in the path.
I am trying to run a .bat file from a macro. The shell command works okay most of the time - but I cannot seem to make it work if there is a space in the path. (So, running C:\Apps\BFIle.bat works, but not C:\My Apps\BFile.bat) As the .bat file I want to run will change, I want to enter the path in a cell, and then run the macro with a button. This means simply enteing the path in double quotation marks in the Shell command will not work. BFile = Range("A2").Value i = Shell (BFile, 1) Any ideas? Odd that a space is giving you a problem. This works for me whether the path has a ...

Can XIRR start with a "0" in the first period
I am having issues calculating XIRR. When my first cash flow is 0, I get an error message, even though a regular IRR calc allows a 0 in the first period. Is this just the way XIRR works or is there a way I can get around it? Thanks for you help. carlsondaniel@gmail.com wrote: > I am having issues calculating XIRR. When my first cash flow is 0, I > get an error message, even though a regular IRR calc allows a 0 in the > first period. Is this just the way XIRR works or is there a way I can > get around it? This appears to be a defect in the XIRR algorithm. As you noted, IRR wo...

Automatic Comments Boxes
I have a spreadsheet with two tabs that I use for work, One tab is inbound quantities and the other tab is the order no of that quantity. What I would like to do is where the quantity in a cell in inbound ie D8 is greater than 0 then a comment is automatically inserted quoting the value in D8 in the other tab. If this is possible it would be a great help. TIA Assuming inbound quantities are in Sheet1, Put in D8 in the other tab / sheet (for order no.) : =IF(AND(ISNUMBER(Sheet1!D8),Sheet1!D8>0),Sheet1!D8,"") -- Rgds Max xl 97 --- Please respond in thread xdemechanik <at...

Automatic Dynamic Chart Ranges
Hi: I have an XY plot that refers to a set range of values on my worksheet In this case, it is 8 rows long. I chose 8 rows because that is th maximum number of data points that I will ever need. I have set up th chart values so that they are dynamically calculated based on VLOOKUP from other data. Sometimes there are only 2 data points, sometimes or 6. The problem is that no matter how I set up the formulas, Exce does not like the cells without values in them being included in th chart series (I have hardwired it to include all 8 rows). I thought i might ignore a blank ("" in ...

Automatically forward spam
I have used the "Customize" menu to create/assign hyperlinks so that a new message window will automatically come up with the contact's name in the "To" box. Now I want to forward a message using a hyperlink. Usually my spam is just annoying, but sometimes I get really dangerous spam that wants me to enter my bank account information. I want to forward that to: spam@uce.gov which is an agency that tracks this sort of thing. Is there a way to assign a hyperlink to forward a spam mail to them? ...

Outlook XP: No automatic receive
I have an Outlook XP installation on a networked Windowx XP PC. It is linked to an MS Exchange server. Outlook is set to receive messages automatically once every 5 minutes (Tools / Options / Mail Setup / Send-Receive). The appropriate box is ticked, the time is set to 5 minutes, yet mail only arrives when the user presses the Send/Receive button. Any suggestions how to make Outlook do what it is supposed to do? OL2002: You Cannot Receive New E-mail Notifications in Environments That Use the Network Address Translation http://support.microsoft.com/default.aspx?scid=kb;en-us;305572&Pr...

running external program, waiting for completion
I'm trying to start an external utility from a C++ program, but I'm having some problems I've tried various approaches, but all seem to suffer from the same problem: I can start the utility but if I try to wait for it to exit, then I'll wait forever (& the utility never seems to start running). One example: SHELLEXECUTEINFO ShExecInfo = {0}; ShExecInfo.cbSize = sizeof(SHELLEXECUTEINFO); ShExecInfo.fMask = SEE_MASK_NOCLOSEPROCESS; ShExecInfo.hwnd = NULL; ShExecInfo.lpVerb = _T("open"); ShExecInfo.lpFile = "c:\\xxx\\yyy\\zzz.exe"; ShExecInfo.lpPar...

Public Function not running
Hey, I have a public function that I call from a cell. The function is: Public Function QuitTime(strQuitTime As String) As Date Dim strHour, strMin As String Dim intHour, intMin As Integer Dim dtTime As Date strHour = Left(strQuitTime, 2) strMin = Right(strQuitTime, 2) dtTime = strHour & ":" & strMin dtTime = dtTime + Range("MainSheet!B2") ' this cell contains the value today() QuitTime = dtTime End Function Anyway, I run a vlookup on this array from another sheet and when ever i run that vlookup, it makes all the cells in th...

Accessing desktop shortcuts when programs are running
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: intel Is there any way to toggle between program windows and the desktop so that you can access the shortcut icons there without resizing or minimizing your program windows? It would be great to be able to do this, especially when I have four or five word documents open . . . Thanks! You can use the ApplicationName> Hide ApplicationName or Hide Others command to hide the window(s) to access the Desktop - assuming you don't have any other application windows open. However, you might want to explore other more effective...

Allow active x controls to run in RMS POS
Hi there. When trying to run drilldown.htm in RMS on my POS computer, I get the error message "Your current security settings prohibit running active x controls on this page", and it doesn't work. It works fine on my back office pc, and I can't find any difference between the IE settings on the two pcs. Where would I find the setting to allow active x controls to run? Cheers Problem solved, windows automatically blocked it because I sent it as an email attachment from my office pc Mheill wrote: > Hi there. > > When trying to run drilldown.htm in RMS on my POS co...

Can charts automatically change by entering new range of dates
I am creating a Dashboard report for my Accounting department at work and I have a weekly one I set up with links for the series of a chart to another worksheet. Easy. But what I am trying to do is instead of creating a new one each week by changing the link location for the data is there a way I can just enter in a range of dates and the charts automatically change and filer in the new data without me having to change the link in the series where the data entry is coming from?? You can define a dynamic range that adjusts to dates you enter into cells. I wrote a somewhat involved tut...

Related Fields in sub activities view
CRM 4.0 In the standard My Activities view I am able to display information from the Account (Regarding) i.e. industry, city etc. When switching to Phone Call or Appointment, the information is not displayed even though the facility is there in admin to add these fields - is there a fix for this? ...

How to run Office:Mac on a PC?
For better support of Mac users, I want to run Office:Mac on a PC. Though I know about PearPC http://pearpc.sourceforge.net, I did not yet give it a try. Are there other solutions available? Many thanks for all hints and explanations, Thomas Hi Thomas, as far as I know, there aren't that many solutions to run Mac OS X on PCs. I have heard of PearPC, but I don't think that you can legally run Mac OS X on a non-Apple computer; if I remember correctly, the license agreement states that you are supposed to run OS X on Apple hardware only. Also, since you can only buy Mac OS X for Power...

HELP: Add textbox to a frame at run time
Hi All, How can I add text boxes on the form at run time based on what user inputs in a textbox? For eg: I have a textbox "Total Students" , so Once I input a number in that textbox, I want to have that many textboxes on the form during run time. If I input 4 I want four textboxes to be displayed on the form. Thanks in advance ...

save to sub folder
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: intel As previous PC user my documents are organized in several layers of folder (folder - sub folder - sub sub folder, etc) When I tried to save email atachement I can only get into the main folder (can not get into sub folder or sub sub folder). Same problem occur when I create a new file (excel, word or PW) when I do 'save as' I can not chose the specific folder that I want to. Any advice? Many thanks Paulina Welcome to the Mac! This is actually an Apple thing, not an Office thing, so remember it--you might run ...

Focus on a sub Dialog
Hi all, I've a main CDialog containing a CTabCtrl. In the CTabCtrl I have 2 tabs: each tab containing a CDialog. The main Dialog has two buttons, and when the application starts one of those buttons gets the focus. I'd like to set the focus to a particular button of one of the CDialog in the Tab. Can someone help me. Please? For moving the focus on one single Dialog in the Tab I use the GoToDlgCtrl(), but it works only after I push on one button of the Dialog. Thank you so much Pietro ...

un posted trx warning on GL year end run
We have a situation here as follows: When tried to Year end close 2007 on GL after closing all relavant modules we received a warning message "A single-use batch or quick journal transaction has not been posted yet. Do you want to continue closing ?" We put our maximum effort to find any pending trx that of year 2007 but without success. On a test basis we override the above warning and ran the year end procedure. When finished we find out that 3 multi-currency transactions are effected on the retained earnings account (debited 3 trx and the same amount included as credit t...

RUNNING TOTALS #2
I would be obliged for help in setting up a formula, or similar, relative to retaining a 'running total' in 2 different worksheets please. e.g. in sheet 1 (cell 1a) - inputting a new figure every week. in sheet 2 (cell 1a) - to protect and record the sum of all the previous weekly figures accumulatively over the usage period, month, year and so on. Thanking you See: http://www.mcgimpsey.com/excel/accumulator.html HTH Jason Atlanta, GA >-----Original Message----- >I would be obliged for help in setting up a formula, or >similar, relative to retaining a 'running...

Automatic send / receive at startup Outlook 2003
The subject says it all Thanx in advance <silverarrowmp20@myway.com> wrote in message news:1134494807.974282.63760@g14g2000cwa.googlegroups.com... > The subject says it all > Thanx in advance > Okay. Since your Subject says it all, and since it doesn't pose a question, then expect no replies because you didn't ask for any help. For all you've told us, auto send/receive WORKS on startup of Outlook 2003 and you simply want to declare the obvious. My apologies , my problem is that auto send/receive doesn't start on startup of Outlook 2003 and that i don&...

Running Office 2000 on Win XP
Which, if any of these programs will work on Win XP Professional - 64-bit 1. Microsoft Word 2000 2. Microsoft Excel 2000 3. Microsft Publisher 2003 4. Microsoft Outlook 2003 Hi, Back in 2005 I used Outlook 2003 with no issues on Win XP - 64bit. Carlos "vootsak" wrote: > Which, if any of these programs will work on Win XP Professional - 64-bit > > 1. Microsoft Word 2000 > > 2. Microsoft Excel 2000 > > 3. Microsft Publisher 2003 > > 4. Microsoft Outlook 2003 > > vootsak wrote: > Which, if any of these prog...