Need to execute VBA code @ 9:30:01 exactly

Hello all. Using Excel 2003 with WinXP. I'm trying to run macro code automatically whenever time = 9:30:01 (or whatever time I pick) for a stock market trading program, which is why the exact time matters so much. I've been able to get the time to update fine, but unless I click on the worksheet while the time condition is TRUE then my code doesn't run. The time actually is sent to me from the stock data provider and it shows up in a cell as a constantly updating value.

I've tried using the Workbook_SheetChange function and OnTime method, but without luck. In both cases, unless I activate the sheet the code doesn't run. By activate, I mean that I have to click on the sheet when the values that trigger the code to run would be true. If I do that it works just fine, but sitting around and clicking on a worksheet defeats the purpose of automation. Since I'm trading stocks this has to be very exact, so I can't trust a macro scheduler to do this.

The code further below is what I'm trying to get to run. The time value is in cell L1. In cell L2 I have the following code:

=IF(L1="9:30:01",TRUE, FALSE)

That turns to True at 9:30:01 and false before and after that time. The code below checks cell L2 and then runs the code below that. It works just fine when I click on L2 and the result is True, but unless I manually "activate" the cell the result will turn to True but not run the code.

Placed in ThisWorkbook:

'Declare order object
Dim Order As SterlingLib.STIOrder
'Declare integer variable to manage loop
Dim intLoop As Integer
'Declare integer variable to hold submit order errors
Dim intSubmit As Integer
Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  
    If Target.Address = "$L$2" Then
        If Target.Value = "True" Then
            'Stop any possible runtime errors and halting code
       
            On Error Resume Next
             
                Application.EnableEvents = False
                
  'Initialize intLoop to the second row to account for the title row
    intLoop = 2
    'Set up Loop to cover all positions until there is a blank line
    Do Until Range("A" & intLoop).Value = vbNullString
        'Initialize Order object
        Set Order = New SterlingLib.STIOrder
        'Determine Side for the order based on column C, Sell if position is Long, Buy if position is Short
        If Range("C" & intLoop).Value > 0 Then
            Order.Side = "S"
        End If
        If Range("C" & intLoop).Value < 0 Then
            Order.Side = "B"
        End If
        'Determine Symbol for the order based on column A
        Order.Symbol = Range("A" & intLoop).Value
        'Set Time in Force for the order to Day
        Order.Tif = "D"
        'Determine Account for the order based on column B
        Order.Account = Range("B" & intLoop).Value
        'Determine Quantity of the order, whether long or short, it will equal the absolute value of the position
        Order.Quantity = Abs(Range("C" & intLoop).Value)
        'Determine Destination for the order based on column F, the default is ARCA
        If Range("F" & intLoop).Value = vbNullString Then
            Order.Destination = "ARCA"
        Else
            Order.Destination = Range("F" & intLoop).Value
        End If
        'Determine Price for the order based on column E, If it is a number it will be a limit price, otherwise it will send a market order
        If IsNumeric(Range("E" & intLoop).Value) Then
            Order.PriceType = ptSTILmt
            Order.LmtPrice = Range("E" & intLoop).Value
        Else
            Order.PriceType = ptSTIMkt
        End If
        'Send the order and capture any errors
        intSubmit = Order.SubmitOrder
        'Display any errors.  Error code of 0 means that the order was submitted successfully.  See the Sterling Trader ActiveX API Guide under the Support Documentation drop down box at http://sterlingfinancialsystems.com/support.php.
        If intSubmit <> 0 Then
            MsgBox ("Submit Order Error " & Str(intSubmit) & ".  See the Sterling Trader ActiveX API Guide for more information.")
        End If
        'Destroy Order object
        Set Order = Nothing
        'Increment intLoop
        intLoop = intLoop + 1
    Loop
                
                
                'Turn events back on
                Application.EnableEvents = True
            'Allow run time errors again
            On Error GoTo 0
        End If
    End If
End Sub







Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
 If Target.Address = "$L$2" Then
        If Target.Value = "True" Then
          MsgBox ("Submit Order Error.  See the Sterling Trader ActiveX API Guide for more information.")
        End If
    End If
End Sub

Any help on this would be greatly appreciated. Thanks.

That looks great. I'll try it ASAP. The code that was suggested I try for OnTime previously looked nothing like what you have below. Thank you very much!

Originally Posted by mudraker

To have a macro un at a specific time you will need to use the Application.OnTime command

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Application.Intersect(Target, Range("l1")) Is Nothing Then
      Application.OnTime TimeValue(Target.Text), "TradingMacro"
   End If
End Sub

Sub TradingMacro()
   'your macro code here
End Sub

SHG, I 'm no good at VBA, but wondering if this function is based on the PC's clock. If this one is not synchronized with stock market trading times, maybe the OP 's macro won't run at the right moment?

Originally Posted by jkupfer

The time actually is sent to me from the stock data provider and it shows up in a cell as a constantly updating value.

How is this done? That would solve the problem that I have:

My company has several different work sites and the computers are not networked. I have created a workbook that works well as a clock in/out device. The only weakness is that it relies on the clock of individual computers which obviously can be changed prior to clocking in and out. If there is a way to direct Excel to use the date and time from a website then it's good to go.

Hi Forum Friends
I would like to start a macro at 9:47 A.M. automatically. Much like the posting in
http://www.excelforum.com/excel-prog...1-exactly.html
I tried to use the code as in #7 (2nd reply by shg).
If I insert the code I get variable not defined error for ThisFile in the code below and for
letters x, y, z etc which I use as counters frequently. Can solve the latter by defining them.
Without the On Time code my macros were working fine.
My Question, is there a simple On Time procedure that would start a macro at 9:47 and does not cause the define error?

My SaveAs_Master macro saves 3 copies of the workbook in 3 diffrent drives.

HTML Code: 

Sub SaveAs_Master()
    Range("K2").Activate
    ActiveCell = ActiveWorkbook.FullName 
    If ActiveCell = ActiveCell.Offset(-1, 0) Then 
        '--------------- Save on E Drive ---------------
        ActiveCell.Offset(1, 0).Select
         If Left(ActiveCell, 4) = "E:\" Then
            ThisFile = ActiveCell.Value
            ActiveWorkbook.SaveAs Filename:=ThisFile, CreateBackup:=False  
        End If  
        '--------------- Save on F Drive ---------------
        ActiveCell.Offset(1, 0).Select
        If Left(ActiveCell, 4) = "F:\" Then
            ThisFile = ActiveCell.Value
            ActiveWorkbook.SaveAs Filename:=ThisFile, CreateBackup:=False   
        End If
        '--------------- Save on D Drive ---------------
        ActiveCell.Offset(-2, 0).Select
        If Left(ActiveCell, 4) = "D:\" Then
            ThisFile = ActiveCell.Value
            ActiveWorkbook.SaveAs Filename:=ThisFile, CreateBackup:=False  
        '-----------------------------------------------
     Else
            MsgBox "Incorrect Workbook or sheet:  " & ActiveCell    
     End If
End Sub