Timed Macro?

[MrClean99]

Hi, I'm a relative Excel Novice and could use a little help. All help is very appreciated.
I have a macro that I wish to run every minute from 9:30 AM till :4:00 PM. Currently I have data coming in on x # of stocks into static cells. I want to create tabular charts of this data on a minute basis. My macro copies the data for each chart, then pastes it into a new row for each individual stock. I have the time(=now() in cell A1. I think that I need to add something to the macro in the editor that will trigger the macro every time the time changes. I've formatted the A1 cell to only show minutes, not seconds. (I also am not sure if the macro will trigger in seconds even if the format shows time in minutes, or if i need to find a formula that will give me time without seconds).
Again thanks for any help!!!

mrClean

 

[Trevor G]

Welcome to the Forum,

You will need to look at timer events to trigger something happening every minute.

The following is an example that counts down every second so this can be adjusted to every minute run your macro.

I would look to call your macro in a timer event, so the code remains separate for connecting and refreshing your data then in a separate macro call the macro every minute

Dim CountDown As Date
Sub Timer()
CountDown = Now + TimeValue("00:00:01")
Application.OnTime CountDown, "Reset"
End Sub
Sub Reset()
Dim count As Range
Set count = [A1] ' A1 contains the number of seconds for the countdown.
count.Value = count.Value - 1
If count <= 0 Then
MsgBox "Countdown complete."
Exit Sub
End If
Call Timer
End Sub
Sub DisableTimer()
On Error Resume Next
Application.OnTime EarliestTime:=CountDown, Procedure:="Reset", Schedule:=False
End Sub

 

[AlphaFrog]

If you are using a web query to update your stock data every minute, you could use the worksheet calculate event procedure to trigger your macro.

• Right-click on the sheet tab with the stock updates every minute
• Select View Code in the pop-up menu
• Paste the code below in the VBA edit window.
• Change MyMacro in the code to the name of the macro you want to run.

Private Sub Worksheet_Calculate()
    If Time >= TimeValue("9:30 AM") And Time < TimeValue("4:00 PM") Then
        Call [COLOR="Red"]MyMacro[/COLOR]
    End If
End Sub

This macro will run every time your worksheet recalculates. That would happen every time your webquery updates (every minute).

 

[chuckchuckit]

Am assumming that you are getting new stock data every minute from the auto query feature which queries every 1 minute if set to the minimum. If so then you would want to time your macro code to update all new stock data "after" each new stock data query every minute.

I do this with many programs that uses a "Target" cell location inside the query area that changes of course with each new query. The "Target" is part of the following Worksheet_Change function that then runs all my macro code "instead of" running a separate macro. Because the new stock data query has triggered the code to run when the query changed the "Target" cell location G1 (G1 or any other location inside your query range):

Sub Worksheet_Change(ByVal Target As Range)
   If Intersect(Target, Range("G1")) Then [COLOR=green]'our target cell G1 has made a change from a query[/COLOR]
      [COLOR=green]'So run all your code here to update the new stock data[/COLOR]
 
      Application.EnableEvents = False [COLOR=green]'must turn off so no infinite reoccurence done when changing Target cell on following line[/COLOR]
      [COLOR=green]'Here change your Target cell G1 back to something the new query will modify to trigger this code again next time[/COLOR]
      Application.EnableEvents = True
   End If
End Sub

If you look into using the above Worksheet_change code, it is in a sense a resident macro itself that is just waiting to turn on as soon as its "Target" is changed. Which of course will happen each time your new query imports the new minute of stock data.

You can try some simpler experiments with this function to get used to it perhaps before tackling the stock stuff. But I use it in numerous stock gathering programs and it works maintenance free all day by itself.

If you are using the query method, be sure to set this parameter line to False:

.Refresh BackgroundQuery: = False

This tells the VBA to stop all next lines of code from running "until after" the query has finished. Otherwise your code to update your new stock data might run before the query is finished.

Hope that is of use.

Chuck

 

[MrClean99]

Am assumming that you are getting new stock data every minute from the auto query feature which queries every 1 minute if set to the minimum. If so then you would want to time your macro code to update all new stock data "after" each new stock data query every minute.

I do this with many programs that uses a "Target" cell location inside the query area that changes of course with each new query. The "Target" is part of the following Worksheet_Change function that then runs all my macro code "instead of" running a separate macro. Because the new stock data query has triggered the code to run when the query changed the "Target" cell location G1 (G1 or any other location inside your query range):

Sub Worksheet_Change(ByVal Target As Range)
   If Intersect(Target, Range("G1")) Then [COLOR=green]'our target cell G1 has made a change from a query[/COLOR]
      [COLOR=green]'So run all your code here to update the new stock data[/COLOR]
 
      Application.EnableEvents = False [COLOR=green]'must turn off so no infinite reoccurence done when changing Target cell on following line[/COLOR]
      [COLOR=green]'Here change your Target cell G1 back to something the new query will modify to trigger this code again next time[/COLOR]
      Application.EnableEvents = True
   End If
End Sub

If you look into using the above Worksheet_change code, it is in a sense a resident macro itself that is just waiting to turn on as soon as its "Target" is changed. Which of course will happen each time your new query imports the new minute of stock data.

You can try some simpler experiments with this function to get used to it perhaps before tackling the stock stuff. But I use it in numerous stock gathering programs and it works maintenance free all day by itself.

If you are using the query method, be sure to set this parameter line to False:

.Refresh BackgroundQuery: = False

This tells the VBA to stop all next lines of code from running "until after" the query has finished. Otherwise your code to update your new stock data might run before the query is finished.

Hope that is of use.

Chuck

Hi Chuck, thanks for the reply. I'm not even sure what a query is. My data is extracted from my providers server via a formula in each cell which is updated every minute. So I think all cells are updated simultaneously. I have a couple of questions. My 1st column has =now() which returns the date and time. My assumption is that a change of time would trigger the macroif I used this cell as the target, but its not, am i doing something wrong. Secondly, if I use another cell, say a stocks last price, and the price is unchanged then the macro will not run even though each of the other stocks have changed values, correct? How do you work around this?

Thx

 

[MrClean99]

Welcome to the Forum,

You will need to look at timer events to trigger something happening every minute.

The following is an example that counts down every second so this can be adjusted to every minute run your macro.

I would look to call your macro in a timer event, so the code remains separate for connecting and refreshing your data then in a separate macro call the macro every minute

Hi Trevor, thanks for the help. I tried your method and after some trial and error, I was close, but no cigar. If I put 60 ( the # of seconds for recurrence) in cell A1 and the run the "timer" macro, the coundown begins and at "0" my macro runs, but then cell A1 stays at "0" and the macro doesn't run again. If I put the =now() in cell A1, and run "timer" the countdown changes the day number in the date portion of the date and time value and never runs. So....what am i doing wrong?

Thx

 

[MrClean99]

If you are using a web query to update your stock data every minute, you could use the worksheet calculate event procedure to trigger your macro.

• Right-click on the sheet tab with the stock updates every minute
• Select View Code in the pop-up menu
• Paste the code below in the VBA edit window.
• Change MyMacro in the code to the name of the macro you want to run.

Private Sub Worksheet_Calculate()
    If Time >= TimeValue("9:30 AM") And Time < TimeValue("4:00 PM") Then
        Call [COLOR="Red"]MyMacro[/COLOR]
    End If
End Sub

This macro will run every time your worksheet recalculates. That would happen every time your webquery updates (every minute).

Hi Alpha, thanks for the help. I did as you instructed but was unable to get my macro to trigger. I 1st was concerned about how this code would know to recur every minute as opposed to every second or hour, but it didn't trigger at the initial time noted in the code either. Am I doing something wrong?

Thx

 

[Trevor G]

The code is right but if you are unsure about macros behind sheets there is additional steps to consider.

As suggested right click the sheet name them select View Code. At the top of the code sheet you see 2 drop downs select first that says object and change that to worksheet, then on the right change that drop down to Calculate. Then paste in the IF statement

If Time >= TimeValue("9:30 AM") And Time < TimeValue("4:00 PM") Then
Call MyMacro
End If

 

[MrClean99]

Well yes I am unsure, LoL! So i tried each of the methods from you Trevor, Alpha & Chuck, each with varying degree of sucess. The closest I got was your code in the 1st response to my post. But in your last reply, you seem to be suggesting Alpha's code:
If Time >= TimeValue("9:30 AM") And Time < TimeValue("4:00 PM") Then
Call MyMacro
End If

So now i am more confused. If I start all over can you walk me through this? I will delete everything I have except for my original macro. Then my next step would be to follow your instructions from your 1st post last week, or yesterdays?

Thanks very much for all your help.